Solved

Concatenate all SO Line Inventory IDs into one cell

  • 29 March 2023
  • 7 replies
  • 188 views
M
Rank
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

M
Rank
Userlevel 5
Badge +1

Thank you, @lauraj46!

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

 

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*.
aaghaei
Rank
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.

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*.
M
Rank
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.

aaghaei
Rank
Userlevel 7
Badge +8

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

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*.
lauraj46
Rank
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

 

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*.
aaghaei
Rank
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

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

Reply


Terms & ConditionsCookie settings
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