Archive

Archive for April, 2010

Adding additional tables to your SmartList (Successfully)

Adding your first table to your List is easy (assuming that you can FIND your desired table), but how do you add an additional table?  What type of “Link Method” should you use and what fields should you link on? 

Here is my basic / non-programmer take on Table joins and Link fields.  (For a more comprehensive explanation on the subject search the web – you will find several good websites.)

SmartList builder uses two type of Link Methods or joins (by the way, there are several other types of joins in SQL) - Equals & Left Outer.  These joins determine what records you get in your final List.  Example – First table is RM Customer MSTR table, add your second table – RM Open File (Transactions) table with a link on Customer Number:

  • Equals: you will only get records that have matching link fields in both tables
    Results - Records in RM Customer MSTR table that have records in RM Open File table
  • Left Outer: you will get records from the “Main” table (the first one you put on your SmartList that you will specify it the “Link To” area) whether there is a record in the second table or not   
    Results - All records in RM Customer MSTR table and any records in RM Open File table  (One row will be returned for Customers without any records in the RM Open File table, fields from the RM Open table will be blank (actually NULL) on the SmartList.)

This raises a very important point.  You have to start your SmartList with the right table.  If you started with RM Open File, there would be no way to have customers without transactions appear on your list. (If that was something you wanted.) 

If you add a third table linked to your second table, the concept is the same, it is just comparing to the cumulative records for the first AND the second tables to decide what records to include.   (If your third table is linked to your first table, that would work just like described above. )

OK – so now, what fields do you specify in the Link area? 

Well, this is where knowledge of GP is important.  The programmers at GP realised that users were having a really hard time with this and now (if you are on GP10+) SOME tables will come in with link fields already populated in the link area (you can always modify these – but most are exactly what you need.)

My rule of thumb is to look at the KEY fields of each of your tables.  Generally, you will use ALL of the key fields from one of your tables as the link fields.  (Not always, of course, nothing is that easy, but most of the time.)

OK, so how do find the key fields for a table?  Well, you can do this in SmartList Builder.  Start a new SmartList (you don’t need to save it).  Press the + in the Table area to add a table and select your desired table. Look on the lower part of the window and you will see the names of the key fields on this table in SQL.

Hit cancel and repeat these steps for your other table and then think about what link fields make sense.  Go back to the List you are building and Use the + in the Link Fields area to add each of these fields to your join as the link fields.

And as always… Check your data!  (Read my post about the hazards of not checking your work as you go:   Help! My SmartList loaded zero records (no data)  )

Happy Building!
Devon

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.

If you open the SmartList (from GP) > Item Quantities, you will see each of these records:

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.

Specify: 

  • Table: Item Quantities Master
  • Field: Record Type
  • Is Equal To
  • 2
  • 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”.

Happy building!
Devon

(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.)

Help, My SmartList loaded zero records! (No data)

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
  • 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.

Happy Building!
Devon

Corny, but true, I Love SmartList Builder

April 4, 2010 2 comments

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.)

Regards,
Devon

Follow

Get every new post delivered to your Inbox.