Skip to main content
Solved

Concatenate all SO Line Inventory IDs into one cell


Forum|alt.badge.img+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?

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

View original
Did this topic help you find an answer to your question?

7 replies

aaghaei
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1176 replies
  • Answer
  • March 29, 2023

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


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 502 replies
  • March 30, 2023

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

 


aaghaei
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1176 replies
  • March 30, 2023

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


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 228 replies
  • March 30, 2023

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.


aaghaei
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1176 replies
  • March 30, 2023

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.


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 502 replies
  • March 30, 2023

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

 


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 228 replies
  • March 31, 2023

Thank you, @lauraj46!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings