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.)
Have you every had it happen to you? You build a SmartList and then go to load it and it says “Completed 0 (Zero) Records”. Ug! Here is some advice on how to not have it happen to you:
First, and foremost, check your work as you go. My #1 piece of advice on SmartList Builder it to check your List after each step:
- Start your List and put on your first table. Save and load the changes on SmartList. Check your data. (It is a good idea to know how many records you expect. If is it a large data set, know how many records you should load for a period of time or a certain range of data.)
- Add your second table. Save and load the changes on SmartList. Check your data. (and so on for additional tables)
- Add your first restriction. Save and load the changes on SmartList. Check your data. (and so on for additional restrictions)
- Add your calculated fields. In GP10, press the Validate button for on each calculated field. Save and load the changes on SmartList. Check your data. (I will explain below why you can add all calculated fields together.)
Notice a theme? It is much faster to check your work after each step then to figure out which table join, restriction or calculated field “broke” your List. This goes for too few or too many records (“Duplicate records”) also. That is why I recommend knowing how many records your list should load at each step.
OK, so you didn’t check your work with each step, now what? You might have to take each piece off one-by-one to discover when it “works” again. But first – check these things:
- Calculated Fields – generally, if you do not show the “bad” calculated field as a column, the SmartList will load data. So take off all of your Calculated field Columns and re-add them one-by-one in SmartList.
- SQL data – do you have a SQL table or view?
- Will it load data for sa? If so, check the following:
- In SQL, make sure you have permission to select the data: GRANT SELECT ON SQLVIEWNAME TO DYNGRP
- In security, make sure the user has rights to view SQL data in SmartList Builder Rights
- For multiple companies, verify that the “Use Company database” box is checked
- Will it load data for sa? If so, check the following:
- Restrictions – remember that the data has to pass thru ALL restrictions to show on the List.
These steps should help with many issues. Of course, table joins are a huge part of getting the correct number of records, but that will be the subject of a future post.
I’ve been thinking about doing this Blog for a long time. I started using SmartList Builder when it first was released. At the time I was a client and my partner called to say that GP had just released a module that I might be interested in. Boy, were they right!
For the past 5 years, I have used SmartList Builder extensively as Implementation Consultant for MS Dynamics GP. I love what I am able to do for our clients, whether it is a SmartList of Sales Holds to release for the credit department (with Go To links to the orders, of course) or a Month-end analysis of Obsolete Inventory or Sales by Salesperson with Margin. These SmartLists streamline daily tasks and allow powerful analysis of important data with the click of a mouse.
As I have run training seminars on using SmartList Builder and worked with my coworkers in the program, I find that people really struggle with successfully using this module. Just this week, I had two clients who caused “duplicate records” on existing SmartLists and responded to questions on the Dynamics GP Partner Board regarding calculated fields in SmartList Builder.
I am very excited about the new functionality in GP2010. Beefing up the Excel Report Builder and adding the Drilldown Builder and Navigation List Builder should really increase the power of these modules. (I might even start training users on the Navigation Lists – since these lists may have usable functions that aren’t possible on SmartLists, but that is another story!)
Welcome to the blog and feel free to comment if you want me to cover a specific subject or even if you enjoy it. (Hopefully you will learn something.)