Sign Up for Training |
insightsoftware Company Site
Community
Downloads
Training
Submit a Request
Become a Jet Insider
Give Feedback

Sorting NL("Last") and NL("First") functions


NL(Last) and NL(First) are dependent upon sort order

When using the NL() function with the WHAT parameter of "Last", this is going to pull back the last matching record in the table.  However, the last record in the table is subject to change based on how Dynamics NAV is sorting the records overall.  So the last record in the table is not always based on the entry order, but could be based on another field organizing the records in a different way.

Examples

To resolve this, you will need to add a sort to your function.

 

If you wanted to bring back the amount of the last invoice that posted for a particular customer (i.e. customer no. 10000), you would sort on the field of "Posting Date" to show you the most recent record associated for that customer and invoice (transaction with the most current posting date):

=NL("Last","Cust. Ledger Entry","Amount","Document Type","Invoice","+Posting Date","1/1/23..3/31/23")

By using the WHAT of "Last" we are telling Jet Reports to return the Last record it finds.  However, you can placed a sort "+" in front of the field of "Posting Date" to sort the records by the date, recent date listed first.  Therefore, pulling back the last record, will give you the desired result.

 

You could obtain the same result but using the NL(First) function and sorting the records in the opposite order - using the "-" in front of the field of "Posting Date" to sort the records with the most recent date listed last:

=NL("First","Cust. Ledger Entry","Amount","Document Type","Invoice","-Posting Date","1/1/23..3/31/23") 
Was this article helpful?
2 out of 2 found this helpful

Comments