Inventory – Adding Item Quantities & Sites
A popular table to add to Builders in GP is the MS Dynamics > Inventory >Item Quantities table (AKA Site table) (SQL – IV00102). Great for adding On-Hand, On Order, Allocated and other quantities and assigned Sites. But this table will cause trouble with your List unless you understand and adapt to its structure. Just this week, I had both a client and a co-worker say “Help! My records are doubling” after they added this table to their SmartLists in Builder.
How would that happen? For a simple company with only one Site (which is Location Code, by the way, on this table) there will be TWO records in this table for each Item. Why? Well, one of the records is a Summary Record, the other is a Site Record. And this fits with how you see this data in GP. Let me explain further. If you go to the window in GP where this data is maintained: Inventory >> Cards >> Quantities/Sites, you will see the following:
Notice how the radio button (circled in red) changes the screen from viewing the summary “All Sites” record to the record for a specific site.
So how do we only have one record for each item (or at least one for each Site assigned) on our List if we use this table? Well, if you look at this same data in SQL (IV00102) you will notice the field Record type (SQL – RCRDTYPE):
See how the record without a Site (field LOCNCODE is blank) has the value 1 in this field – This is the summary/overall record. While the record with a Site has a value of 2 in the Record Type field? We can use this to add a Restriction to our List.
Press the Restrictions button on the top bar.
Press the + to add a new Restriction.
- Table: Item Quantities Master
- Field: Record Type
- Is Equal To
- Press Save to add this Restriction
- Load your changes and TEST!
Adding this Restriction will only pull records from this table for the Sites, not the summary/overall record, making so that this List will no longer pull “Duplicate records”.
Now, your next question may be “Where is the Quantity Available on this table?” Well, it isn’t there. (Sorry! GP calculates this field on the “fly” on windows & reports.) But do not fear, that will be the subject for a future post: “Adding a Simple Calculated Field to your SmartList”.
(Note – since we can now build Excel Reports (and soon Navigation Lists) in addition to SmartLists – I am using the generic term List for what we are building.)