Solved

Sequence contains no element error when select result is null

  • 8 April 2022
  • 8 replies
  • 432 views

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

8 replies

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);

 

Userlevel 7
Badge +8

Thanks @Naveen Boga ,

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

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);

 

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();

 

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);

 

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.

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. 

Userlevel 7
Badge +8

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

 

much appreciated

Reply


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