By using this website, you agree to our Terms of Use (click here)
Hi,
For reporting purposes, does anyone how how to best link a specific Invoice to the Ship To State (i.e., which tables to use)?
Thanks,
Rob
I haven’t played around with 2019 R1, but I remember reading that it fixes an issue where the Invoice doesn’t link to shipping info.
In earlier versions, we had to link artran (SOOderNbr and SOOrderType) to soorder (orderNbr and orderType) and then soorder (shipaddressID) to Soaddress (addressID)
Thanks Ryan, I linked the ARTran to SOOrderShipment (OrderNbr, OrderType, and ShipmentNumber), then linked SOOrderShipment to SOShipmentAddress (ShipAddressID to AddressID). It appears to work.
My concern is that ShipmentType is part of the primary key to SOShipmentOrder. The values in this column do not appear to match up with values in the ARTran.
Rob
The Shipment table is funny because you don't need the Type to form a Primary Key. SOShipment.ShipmentNbr is all you need.
Prior to Acumatica 2019 R1, the Ship Address wasn't stored on the Invoice as Ryan pointed out.
For the Shipment on the Invoice Line, you can get from ARTran to SOShipment on ARTran.SOShipmentNbr=SOShipment.ShipmentNbr.
For the Shipment Address, you can get from SOShipment to SOAddress on SOShipment.ShipAddressID=SOAddress.AddressID.
In Acumatica 2019 R1, the Ship Address now gets stored on the Invoice in the ARInvoice.ShipAddressID field. That's especially helpful with Projects that were billed without a Shipment.
I've got a question very close to this one. I need to show Sales Orders sales numbers by State. Similar to what you mentioned, the unique address identifier is stored in SOOrder.ShipAddressID. I've gotten the much.
However, I imagine I need to then join the SOOrder table to the Address table in the inquiry. I'm failing to do so, and I'm not sure why. Any ideas?
Or is there possibly a "Sales by State" out of the box report that I'm missing? Seems like it would be common.
Looks good to me. It's not returning anything? Try SO.SOAddress if CR.Address isn't getting you anything.
Looks good to me. It's not returning anything? Try SO.SOAddress if CR.Address isn't getting you anything.
SOAddress was the answer. Still not certain why Address table wouldn't work with the same relation, but whatever. This works. I've got address data on the inquiry now.
Thank you for the response!
Online Members