Skip to main content
Answer

Display multiple record results as 1 record by listing the one field that has multiple results as a list separated by commas.

  • April 17, 2025
  • 3 replies
  • 139 views
J
Freshman II
Forum|alt.badge.img

I’m trying to create a GI that lists each Inventory Item one time and I want to show the Sales Category or Categories for each stock item without duplicating the Inventory Item. I would like the field to list all the Sales Categories that the Stock Item includes.

 

For example, we have a Category called Machines. We have machine A and Machine B. We have several parts that can be used on both machines. When I make my inquiry I don’t want to see multiple records for the same stock item that has sales categories of each machine, I want the Stock Item Record as 1 line, but the field “Sales Categories” to list all of the Sales Categories the Stock Item is found in.

Best answer by Robert Sternberg

The way to accomplish this is to create a group based on the Sales Category. Set the Details section’s Visible property to False. Then, create a variable within the Details section and set it to Concat(Variable Current Value, Sales Category).

In the footer of the immediately encapsulating group, you can then output the list of Sales Categories and related details without any duplication.

    3 replies

    lauraj46
    Captain II
    Forum|alt.badge.img+8
    • lauraj46
    • Captain II
    • April 17, 2025

    Hi ​@jhutchinson ,

    I’m not sure this is possible without creating a custom DAC that exposes a custom SQL view.  

    One idea that we use frequently in this scenario is to create a separate generic inquiry with the detail records and configure that as a side panel on the main GI.

    Another idea, if you have a defined list of categories, you could create a separate result column in your GI for each category, group on the stock item inventory code, and use a formula to print Yes or No in the column.  For example for Category A the formula would be something like this:

    =max(iif([INItemCategory.CategoryID]=’A’, ‘Yes’, ‘No’))

    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*.
    Chris Hackett
    J
    Robert Sternberg
    Captain II
    Forum|alt.badge.img+7

    The way to accomplish this is to create a group based on the Sales Category. Set the Details section’s Visible property to False. Then, create a variable within the Details section and set it to Concat(Variable Current Value, Sales Category).

    In the footer of the immediately encapsulating group, you can then output the list of Sales Categories and related details without any duplication.

    Robert Sternberg, Crestwood Associates -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
    lauraj46
    J
    D
    J
    Freshman II
    Forum|alt.badge.img
    • jhutchinson
    • Author
    • Freshman II
    • April 21, 2025

    The way to accomplish this is to create a group based on the Sales Category. Set the Details section’s Visible property to False. Then, create a variable within the Details section and set it to Concat(Variable Current Value, Sales Category).

    In the footer of the immediately encapsulating group, you can then output the list of Sales Categories and related details without any duplication.

    I was trying to use a GI, but I don’t think the end results would be ideal, so I might have to go with a Report and this would be the best way to go. Thank you!

    Terms & ConditionsCookie settingsAccessibility statement
    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