I've created a report using table builder. I've linked 4 separate tables, one of which returns the posting date of the purchase receipt line table. When I run the report, it returns the "first" (or oldest) record which goes back to 2014 for one particular item. Does anyone know if there's a way to get it to return the "last" (or newest) record from 2023? I know I could easily use an NL function normally, but I can't seem to get that to work correctly with the builder.
Date
Votes
4 comments
-
CascadiaDataworks Since I don't know exactly what you are trying to get, I built this which would go into a New Formula in the Table Builder. This would return the most recent Posting Date from the Sales Invoice Header based on the Customer No on the row of the table. Depending on what you need, it could be much more complicated
=NL("First","Sales Invoice Header","Posting Date","-Posting Date","*","Sell-to Customer No.",[@[No.]]) -
CascadiaDataworks And as a side note, always use "First" and reverse the order instead of using the "Last" function. The Last function is super inefficient and can have a negative impact on your reports.
-
Rachel Schaefer I appreciate your input, Bryan. I did try that. Unfortunately, that didn't work for what I was trying to do. I ended up just doing it all manually instead of using the builder. It took more time, but it got the job done. Data dump, NF, Links and all... it is a beautiful report though.
Side note, I've never had issues using "last". I find reversing the order just adds extra steps when none are needed, but again, I do appreciate the input.
Thank you.
-
CascadiaDataworks Great to hear Rachel. I initially was going to suggest the manual process. In regards to the Last vs First, the bigger the report, the more the inefficiency is. That is something I learned in talking with the original Jet developers, years ago.
If you only use it on smaller, fast running reports, you may not see a difference. Just something to keep in mind for future reports.
Please sign in to leave a comment.