Acumatica provides a way to sum values of transaction lines into a field in the header. This works well when the data is contained within the parent and child records, but what if the information needed is contained in another table? After years of finding workarounds, a better solution has surfaced.
My sysadmin loves generic inquiries and all the flexibility and power they provide. When I need to populate a field in a screen, I’ve relied on the FieldSelecting or RowSelecting event in the screen’s graph to populate an unbound field. The issue with that approach is that the field exists for my sysadmin, but a generic inquiry does not contain the logic from the graph to populate the field. Therefore, the field always returns as null. In some cases, I’ve used PXDBScalar to lookup the value, but this was limited to “get that one value from that one record” until now.
Scenario
We have a customization to facilitate returning an SO Shipment. This return results in SOLine storing a reference to the SOLine of the shipment returned. Multiple returns would result in multiple SOLine records, meaning we need to sum up the orderQty on the return orders to know how much was returned in totality. This isn’t airtight since you can ship on another SO that isn’t linked to the original order, but it is enough for us under the 80/20 rule.
Tools for the Job
To query the database and return the total quantity ordered on linked sales orders for the returns, we can use the following tools:
- Add an unbound field (UsrQtyReturned in this example)
- PXDBScalar() to inject a subquery when retrieving the record from the database
- Search4<> to locate the record(s) needed and aggregate to return a single record
- Aggregate<Sum<>> to sum the quantity of all records found by the subquery and return the sum instead of a single value of one of the records
- Custom getter and setter to return 0 instead of null if no records are found in the subquery
The resulting field definition for the SOShipLine DAC extension is shown below, putting together all of the pieces.
Special thanks to Tony Lanzer for the custom getter and setter to ensure the value returns as non-null.
Happy coding!