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