Solved

Concatenate all SO Line Inventory IDs into one cell

  • 29 March 2023
  • 7 replies
  • 161 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 5
Badge +1

Thank you, @lauraj46!

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 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 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

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

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

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

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