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?
Answer
Concatenate all SO Line Inventory IDs into one cell
Best answer by aaghaei
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
+10Add 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
Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
+8Hi
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!
Laura
Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
+10Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Thank you
+10In 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.
+8Hi
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.
Laura
Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.