Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Feedback

NL("Last") function requires sorting


NL(Last) is 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 Navision 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.

To resolve this, you will need to add a sort to your function. For example, 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). For example, your formula might look something like this:

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

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/10..3/31/10")

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

Comments