I need to take the SOLine table, group the results by the SO Order Nbr, and concatenate all of the Inventory IDs from each line into a single cell. Anyone know if this is possible, and if so, how to accomplish this?
Best answer by aaghaeiView original
Add an Unbound String Field let's Say “UsrInventoryIDs” to SOOrder. Then in this Custom Field FieldSelecting Event Loop through SOLines where OrderType & OrderNbr is Equal to the Current SOOrder and aggregate the InventoryID results and print/display it the same as other SOOrder Fields. If You want InventoryCD (that I think is what you mean by ID) you can Join the SOLine.InventoryID to InventoryItem.InventoryID and get the InventoryCD from InventoryItem DAC
If you only need to display the concatenated value on a report then you could accomplish this without a customization by using variables in the Report Designer to build the concatenated string. The idea would be to group on OrderNbr, concatenate each InventoryCD in a variable of the detail section of the report, and print the resulting variable in the footer section of the OrderNbr group.
Hope this helps!
@aaghaei and @lauraj46. I was trying to accomplish this in a GI so that we could create an endpoint to the data for access by an outside system. But it sounds like the only way to do so would be via a customization.
In this case yes. What I suggested first is your only option. And you will need a Bound field instead of an Unbound to hold the data.
Another way that would allow you to use the field in a GI would be to create a SQL View and then expose that view using it’s own DAC. It still requires a customization but it is pretty easy once you have the SQL View created.
@chris49 discusses this idea on this thread:
Hope this helps.