I have a database that imports product line-items from another application. Each line-item has an Order ID (but no Line-item ID). An order will consist of multiple lines each sharing the same Order ID. The database also imports from another application, the delivery cost for each order as a single record. These records have the cost and the Order ID to which the cost refers.
I need to export the results as an excel file so it shows the line items sorted by Order ID with an extra column showing the total cost of each order. Basically, I may have multiple order lines and on the final line of the order, that's where the total cost for the order should appear.
I have tried to accomplish these by sorting and then grouping the fields as per the normal excel export from Filemaker. I just can't get it to return the results I need.
I have got a workaround via a loop script but this does seem to be a bit of a clunk.
The actual database concerned is pretty hefty so I have attached a simple gash database plus a couple of exports that I hope illustrates the issue.
I'm not even sure what I need is possible without scripting but all help gratefully received as always!