Skip to main content
Question

Getting multiple results in a GI for items with multiple storage location ID

  • 5 September 2024
  • 4 replies
  • 61 views

Forum|alt.badge.img

Hi all,

I am trying to build a GI to monitor quantities being sold on our finished good products against the bulk being used in said finished good products. I am using the INSiteStatus table two times to see both QTY Avail. in one row. The issue I am running into is some items (doesn’t matter if it is considered a finished good or BULK item) are appearing multiple times due to being in multiple location IDs. I want to use the total summed QTY Avail. on each item. Is there a way I can do this using the current tables or am I using the incorrect tables? See below for my set up and results. 

 

4 replies

meganfriesen37
Captain II
Forum|alt.badge.img+11

Have you looked at using the “Grouping” tab and adding a grouping by Inventory ID?  You may need to add the “Sum” feature on the Aggregate Function column on the Results tab to count up the quantities in all the locations.


BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4
  • Acumatica Support Team
  • 630 replies
  • September 9, 2024

@mralyea Does it matter that they are showing additional lines for each inventory item if they are in a different location? If you are trying to only show one line per item and the quantities are correct, then you’ll need to do as Megan mentioned and add a grouping so that they get auto-summed.

If you are wanting to keep the location information distinct, then you could add a parameter for Location and then the results would only pertain to the location specified.

Only other thing I could think of is to specify in the joining tables which warehouse data you pull from the table in the relations of the join. Haven’t tried this myself but it seems like it could work.


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 35 replies
  • September 10, 2024

@BenjaminCrisman and @meganfriesen37 The reason I can’t use the grouping plus sum function here is because I am not pulling the individual quantities from each unique storage ID location. The value being displayed in the GI is already the summed total available across all locations. 

 

I do not know what table those storage/location ID are stored in, so even though that is why the lines are duplicating I don’t know how to get to that field where i can use your suggestion of summing all locations.

Based off the tables I have in the images do you know what additional table I can bring in to bring that storage location field into my results grid?

 


Chris Hackett
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • 2640 replies
  • October 14, 2024

Hi @mralyea were you able to find a solution? Thank you!


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