I have a report that lists invoices from NAV tables Sales Invoice Header (primary table) and Sales Invoice Line. I also get other details from Sales Shipment Header by linking the Shipment Number fields. I want to sort the report by the Posting Date filed in Sales Shipment Header. How do I properly do it?
Date
Votes
2 comments
-
Bryan Robinson Currently you can only sort on the primary table. You could fake it using a grouping style report to list the days and then use that as a filter for your other queries. Then hide the grouping.
Rows=2 for the main group of dates from the Sales Shipment Header
=NL("rows","sales shipment header","Posting Date") (add any other links and filters you need to only return dates you want)
Next row down, your rows replicator to show all Sales invoice header for that date but make sure you link to the sales shipment header for the date filter.
=NL("rows","sales invoice header","No.","Link=","Sales Invoice Line","Document No.","=No.","Link=","Sales Shipment Header","No.","=Shipment No.","Posting Date",G6)Hide the rows=2 row.
This all assumed the link from Sales Invoice Header to Sales Invoice Line to Sales Shipment Header because for me, Jet didn't naturally build the link from the invoice tables to the shipment tables
If you are familiar with SQL, you could also create a SQL= statement to sort on the secondary/tertiary table but this is a bit more advanced.
-
Pj Pambuena Thanks for the detailed info Bryan. I'll try it some other time.
I got it to work using the concept of Sorting by Sum https://support.jetglobal.com/hc/en-us/articles/218953718-Sorting-Your-Jet-Reports. Shout out to my man JK, from Dynamic BI Asia, for pointing me to it. My client is on NAV connector only. They don't have SQL connector in their JET Pro license so I can't use any of the SQL stuff. Life would have been so much easier. :D
Please sign in to leave a comment.