We have a bunch of stock items that are physically stocked in multiple locations between our Warehouse and Factory.
Is there a way to create a GI that can show the Inventory ID, a totalized On-Hand qty, and its breakout quantities per the location its stocked in?
I tried to use the InventorySummaryEnquiryResult table to pull in the Location IDs but no dice. Anything would help!
Thank you!
Best answer by jdunmire
Hey guys, thank you for reminding me of this one! The GIs helped but it wasn't exactly what I was looking for, but by using them I was able to figure out how to represent the data as columns rather than multiple rows for each location.
I found out that if you go into the DAC Schema Browser → Source Data for the INLocation table, you can find that the LocationCD also has its own LocationID, which stored as a numerical value. (For instance, for us our LocationCD = “RECEIVING” has the LocationID = “1612”.)
Then by writing the following formula, I was able to find the specific qty for the location and put it as its own column:
=IIF([INLocationStatus.LocationID]=”1612”,[INLocationStatus.QtyActual],0)
See below for an example pic of how the GI came out. I was able to make it such that you can search for a specific part of view all of the items at once. The only drawback I can see is that if you have lots of locations it would take lots of columns and setting up to see them each individually. (These are fake numbers to just represent the data).
I also attached the GI to this post.
Thank you all for the help and inspiration for figuring this one out!
+13Here is a GI that lists all of your inventory by location with quantity and cost. The totals are at the bottom of the screen. If you filter the inventory ID in the column header of the grid, you can get a total for a single item.
The INLocationStatus table has QtyAvail also so you could easily add that column.
Here is a GI that lists all of your inventory by location with quantity and cost. The totals are at the bottom of the screen. If you filter the inventory ID in the column header of the grid, you can get a total for a single item.
The INLocationStatus table has QtyAvail also so you could easily add that column.
This definitely helped and I had worked it into the Stock Check GI I was previously working on! Thank you!
Do you know how to turn these warehouse locations into columns themselves? That way there can be one line per product with multiple column values.
Thank you!
Depending on if you are using/reporting by Warehouse or down to the Warehouse Locations level, you can use the Data Access Classes below.
Warehouse level:
Warehouse Location level:
These DAC’s have a wide range of columns for the various ways that quantity is defined and shown.
Attached is an example GI with all the tables joined.
Hey guys, thank you for reminding me of this one! The GIs helped but it wasn't exactly what I was looking for, but by using them I was able to figure out how to represent the data as columns rather than multiple rows for each location.
I found out that if you go into the DAC Schema Browser → Source Data for the INLocation table, you can find that the LocationCD also has its own LocationID, which stored as a numerical value. (For instance, for us our LocationCD = “RECEIVING” has the LocationID = “1612”.)
Then by writing the following formula, I was able to find the specific qty for the location and put it as its own column:
=IIF([INLocationStatus.LocationID]=”1612”,[INLocationStatus.QtyActual],0)
See below for an example pic of how the GI came out. I was able to make it such that you can search for a specific part of view all of the items at once. The only drawback I can see is that if you have lots of locations it would take lots of columns and setting up to see them each individually. (These are fake numbers to just represent the data).
I also attached the GI to this post.
Thank you all for the help and inspiration for figuring this one out!
Your example could have still used the CD in the IF statement and achieved the same result without the need to lookup the ID’s. But you arrived at the same result which is the main thing.
Another alternative would be to have the GI list rows per item per location, then create a pivot from that GI with the Locations as columns.
Always great when there are multiple ways to solve a problem :)
Your example could have still used the CD in the IF statement and achieved the same result without the need to lookup the ID’s. But you arrived at the same result which is the main thing.
Another alternative would be to have the GI list rows per item per location, then create a pivot from that GI with the Locations as columns.
Always great when there are multiple ways to solve a problem :)
For sure great when there are multiple solutions!
Just curious, I just tried using the CD in the code but I receive this warning and it causes the column to not show up on the GI. Do you know how to get around that?
Thank you!
Your example could have still used the CD in the IF statement and achieved the same result without the need to lookup the ID’s. But you arrived at the same result which is the main thing.
Another alternative would be to have the GI list rows per item per location, then create a pivot from that GI with the Locations as columns.
Always great when there are multiple ways to solve a problem :)
For sure great when there are multiple solutions!
Just curious, I just tried using the CD in the code but I receive this warning and it causes the column to not show up on the GI. Do you know how to get around that?
Thank you!
INLocationStatus only has LocationID (the key int field for joins).
If you join INLocation to INLocationStatus on LocationID, you’ll be able to use INLocation.LocationCD in your IF statement and use the more meaningful ‘RECEIVING’ string value.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
