By using this website, you agree to our Terms of Use (click here)
Hello Tim,
What is the best way to accomplish a report like this in Acumatica Report Designer?
I understand that one invoice can have multiple Sales Orders and one Sales Order can have multiple shipments but I need to show only one record per Invoice. Maybe showing the dates for the most recent sales orders and most recent shipment.
Suggestions?
Mauricio Camara
Hey Mauricio,
I'm including a screenshot here of your Excel file with my comments below.
Invoice #: You should be able to get this from ARInvoice.RefNbr
Doc Total: You should be able to get this from ARInvoice.OrigDocAmt
Sale Date: I suggest using a Subreport to grab Order data, then resize the Subreport to just be the size of a field. You can group the Order data by Invoice # since there could be more than one as you pointed out, then decide whether you want to use a Max or Min formula to grab the latest or earliest order date.
Inv Date: You should be able to get this from ARInvoice.DocDate
Paid Date: Same technique as for Sale Date, but on payment data
Order to Ship, Ship to Inv, Inv to Pay: I assume that these are the number of days between the document dates. Same thing here, you could use the Subreport technique and use the grouped Max or Min in your calculation.
TCT X $ and WACT: I'm not sure what these are