Solved

Sequence contains no element error when select result is null

  • 8 April 2022
  • 8 replies
  • 460 views
aaghaei
Rank
Userlevel 7
Badge +8

Hello gang,

 

I have the below select in one of my methods. When there is data that meets the conditions, it works fine but when there is no data with this conditions I get an error “Sequence contains no element”. How can I prevent system from raising error. I’m checking this select result later in my code whether is null or not.

 

                EPApproval ownerApproval = PXSelect<EPApproval,
                            Where<EPApproval.refNoteID, Equal<Required<APInvoice.noteID>>,
                            And<EPApproval.ownerID, Equal<Required<AccessInfo.contactID>>,
                            And<EPApproval.status, Equal<EPStatuses.pending>>>>,
                            OrderBy<Asc<EPApproval.approvalID>>>
                           .Select(this.Base, this.Base.Document.Current.NoteID, PXAccess.GetContactID().Value, EPStatuses.Pending).First();

 


 

icon

Best answer by Shawn Burt 8 April 2022, 15:54

View original
Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

8 replies

Shawn Burt
Rank
Userlevel 5
Badge +1

 

aaghaei,

I have had this issue if the result is empty, you might change to FirstOrDefault() instead. 

FirstOrDefault will return a null if the list is empty. First will return the error you are getting if results are empty.

Shawn Burt
Dmitrii Naumov
Rank
Userlevel 7
Badge +5

You can also just skip the First() completely. 

The engine is smart enough to recognize that if you save the result to variable of type EPApproval and not IEnumerable<EPApproval> that you expect only one result. The engine will automatically add the TOP1 to the SQL query if you save the query result to such variable. 

Naveen Boga
Rank
Userlevel 7
Badge +17

Hi @aaghaei  If you wanted to fetch the TOP 1 record, please use the SELECTWINDOWED syntax below and NO need to FIRST

 

Here is the example:


            EPApproval ownerApproval = PXSelect<EPApproval,
                         Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                         And<EPApproval.status, Equal<Required<EPApproval.status>>,
                         And<EPApproval.ownerID, Equal<Required<EPApproval.ownerID>>>>>,
                         OrderBy<Asc<EPApproval.approvalID>>>
                         .SelectWindowed(Base, 0, 1, Base.Document.Current?.NoteID, EPStatuses.Pending, Base.Accessinfo.ContactID);

 

Naveen B
aaghaei
Rank
Userlevel 7
Badge +8

Thank you @Dmitrii Naumov, @Shawn Burt and @Naveen Boga 

 

much appreciated

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Naveen Boga
Rank
Userlevel 7
Badge +17

Hi @aaghaei  Please use below query and let me know if you have any issues?

 

   EPApproval ownerApproval = PXSelect<EPApproval,
                            Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                            And<EPApproval.ownerID, Equal<Current<AccessInfo.contactID>>,
                            And<EPApproval.status, Equal<Required<EPApproval.status>>>>>,
                            OrderBy<Asc<EPApproval.approvalID>>>
                           .Select(Base, this.Base.Document.Current?.NoteID, EPStatuses.Pending);

 

Naveen B
aaghaei
Rank
Userlevel 7
Badge +8

Thanks @Naveen Boga ,

Now I get this error “The multi-part identifier "AccessInfo.ContactID" could not be bound.”

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Naveen Boga
Rank
Userlevel 7
Badge +17

Hi @aaghaei Sorry, Please try below query!!

  EPApproval ownerApproval = PXSelect<EPApproval,
                                        Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                                        And<EPApproval.ownerID, Equal<Required<EPApproval.ownerID>>,
                                        And<EPApproval.status, Equal<Required<EPApproval.status>>>>>,
                                        OrderBy<Asc<EPApproval.approvalID>>>
                                       .Select(Base, this.Base.Document.Current?.NoteID, Base.Accessinfo.ContactID, EPStatuses.Pending);

 

Naveen B
aaghaei
Rank
Userlevel 7
Badge +8

Thanks @Naveen Boga 

 

Now it is back to square 1. “Sequence contains no elemen” please note I only need the top 1 so I have .First() at the end. here is the code I run

 

                EPApproval ownerApproval = PXSelect<EPApproval,
                            Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                            And<EPApproval.status, Equal<Required<EPApproval.status>>,
                            And<EPApproval.ownerID, Equal<Required<EPApproval.ownerID>>>>>,
                            OrderBy<Asc<EPApproval.approvalID>>>
                            .Select(Base, Base.Document.Current?.NoteID, EPStatuses.Pending, Base.Accessinfo.ContactID).First();

//also I tried this

                EPApproval ownerApproval = PXSelect<EPApproval,
                            Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                            And<EPApproval.status, Equal<Required<EPApproval.status>>,
                            And<EPApproval.ownerID, Equal<Required<EPApproval.ownerID>>>>>,
                            OrderBy<Asc<EPApproval.approvalID>>>
                            .Select(Base, Base.Document.Current?.NoteID, EPStatuses.Pending, Base.Accessinfo?.ContactID).First();

//and tried this
        int CurrentOwnerID = PXAccess.GetContactID().Value;

                EPApproval ownerApproval = PXSelect<EPApproval,
                            Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                            And<EPApproval.status, Equal<Required<EPApproval.status>>,
                            And<EPApproval.ownerID, Equal<Required<EPApproval.ownerID>>>>>,
                            OrderBy<Asc<EPApproval.approvalID>>>
                            .Select(Base, Base.Document.Current?.NoteID, EPStatuses.Pending, CurrentOwnerID).First();

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved