Solved

Concatenate all SO Line Inventory IDs into one cell

  • 29 March 2023
  • 7 replies
  • 176 views

Userlevel 5
Badge +1

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?

icon

Best answer by aaghaei 29 March 2023, 20:00

View original

7 replies

Userlevel 7
Badge +8

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

Userlevel 7
Badge +7

Hi @mikeho ,

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

 

Userlevel 7
Badge +8

@lauraj46 Great thoughts . I missed that it might only be for reporting purpose not display on UI

Userlevel 5
Badge +1

Thank you @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.

Userlevel 7
Badge +8

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.

Userlevel 7
Badge +7

Hi @mikeho ,

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

 

Userlevel 5
Badge +1

Thank you, @lauraj46!

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved