By using this website, you agree to our Terms of Use (click here)
Hello,
In report designer we have been trying to sum up some fields within a group, but it does not seem that the sum follows the groups.
We have changed the "tran group" from grouping by ARtran.InventoryID and ARtran.Linenbr, to ARtran.Shipmentnbr. If it's only one shipment on the invoice, this works as expected, but when the invoice containes multiple shipment, the Sum per shipment gets multiplied by the number of shipments.
Is it possible to get the price sum of each shipmentnbr in the report?
Here is an example of how we want it to work:
Shipment | qty | Total price
0001 | 2 | 100
0001 | 1 | 150
0002 | 3 | 100
0002 | 1 | 100
0002 | 3 | 120
to:
0001 | 3 | 250
0002 | 7 | 320
Your logic sounds like it should work. Can you attach the .rpx file? Maybe that will shed some light.
Hello Tim,
.rpx file is attached.
We found a temporary solution by getting the SoOrderShipment.linetotal. This could work, but it requires a shipment on every invoice. would be better if the sum function worked properly.
Regards,
Stian
Hi @stian-simonsen,
It looks like the joins under Relationships are the cause.
There is a line ARInvoice Left join SOOrderShipment on InvoiceNbr, InvoiceType. This should be removed.
There is a line ARTran Left join SOLine on OrderType, OrderNbr, and LineNbr. This line is fine and will join to the SOOrder properly. ARTran also holds a line to the Shipment and it should be used instead of SOOrderShipment.
The line SOOrderShipment Left join SOShipment should be removed and replaced with ARTran Left join SOShipLine on SOShipmentType, SOShipmentNbr, and SOShipmentLineNbr.
Next, add SOShipLine Left join SOShipment on ShipmentType and ShipmentNbr.
The problem isn't with SOOrderShipment itself, but because ARTran is being joined to the SOOrders, and then with the SOOrderShipment, which also has a link to the Orders. That second link is not being joined by the order as well, causing the duplication.
Hi @wyatt-erp
Thank you 🙂
Do you have an .rpx file with those changes? We cannot get the SOShipLine table to connect correctly.
No sorry, I was just spitballing the joins.
You want it to look like this though:
ARTran Left SOShipLine
Relation:
ARTran.SOShipmentType Equals SOShipLine.ShipmentType AND
ARTran.SOShipmentNbr Equals SOShipLine.ShipmentNbr AND
ARTran.SOShipmentLineNbr Equals SOShipLine.LineNbr
Ok, thank you @wyatt-erp.
What I meant was, the SOShipLine table is not even coded in to the .rpx file, so we can't join it to anything before we script. We have been trying to write some of it, but we can't seem to get it to work. I took a screenshot of one of our attempts at scripting and atteched it here. Can anyone see what we are doing wrong?
This link walks you through Loading the Schema under Schema builder to add tables to your report. It should show up on the left for you to add.
https://help-2020r1.acumatica.com/Wiki/ShowWiki.aspx?pageid=8bb29ea8-b4c4-46b7-8821-85990ed82988