SmartList Builder – For Advanced Users – Part II

Certainly by now you’ve started to see the power of the SmartList reports that come standard with Dynamics GP. They provide such easy access to your business data.

SmartList Builder allows you to extend the reach of the standard SmartList reports by allowing you to create your own SmartLists that have all of the functionality of the standard reports. The reason this is important is because there is some data and combinations of data that are not included with SmartLists.

If you are using the Manufacturing modules or the Project Accounting modules in Dynamics GP, you are definitely going to want to become familiar with SmartList Builder. Or maybe you have some other combinations of data that you want to retrieve. For example, perhaps you want to associate a specific customer with a specific fixed asset, if you have equipment out in the field, with your customers. This is data from two different areas in Dynamics GP for which there is no standard reporting. But with SmartList Builder, you will be able to quickly and easily create a set of reports to capture this information.

There are basically two steps in creating your own SmartLists with SmartList Builder:

  • Determine which data you want and in which Dynamics GP tables the data is stored
  • Create the SmartList, join multiple tables if necessary, and determine the default fields

The best way to determine which Dynamics GP tables store the data you want to retrieve is to use the Table Descriptions documentation that is accessible directly from your system. Navigate to this area is as follows: Tables >> Resource Descriptions >> Tables

Does this seem like a lot of work? Well, you’re right. It does take some study to figure out the table design in Dynamics GP. But it’s worth a little time. You can do it.

A good way to get started is to explore the tables a bit and get a general outline of how they are typically arranged. To do this, click on the ellipse next to the Table field in the Table Descriptions screen, select the product and series you want to review, and then sort the tables by Table Physical Name.

You’ll discover that there are generally three types of tables:

  • Master File
  • Transaction Header
  • Transaction Detail

Other good sources of information about Dynamics GP table layouts are CustomerSource and the Internet. Search for blogs and technical support dialogs. You’ll find a lot.

Let’s take a look at a simple example.

Let’s create a new SmartList that uses two tables, link the tables and then select the default fields. We’ll create a general ledger account list that combines the Account Master table with the Account Index Master table. The reason that I want to do this is to display the full account number string along with the account master information.

Navigate to SmartList Builder: Tools >> SmartList Builder >> SmartList Builder

Create an ID and Name for the new SmartList.

Select the Product and Series under which the new SmartList will be organized.

Click on the + button to add a table.

Select the table you want. Generally you can just accept the default Key Fields that are displayed. Select Save.

Add additional tables if you want. When you add additional tables, you will have to select the fields that will join the fields together. Generally the system will do that for you. If you need to review or change the join attributes, click on the edit button.

This screen shows the second table being added:

This screen shows the Add Link screen that allows you to review or change the table join attributes.

In this example, the default values can be accepted. And you’re done with the hard part.

This screen shows the two selected tables, and related fields:

The next step is to select the Default fields you want to show on the SmartList. You don’t need to worry that you select all the ones you desire at this time, because when you actually run the SmartList that you create, you can add additional fields to the view results, just as you can with the standard SmartLists.

When you’re finished, save the SmartList and exit SmartList Builder. You will receive a message to refresh the cache and this:

Click Yes.

To review your new SmartList, just go into SmartList and

This is the new SmartList. It has all the functionality of a standard SmartList, but it includes the tables and fields you selected:

This is a very simple example, and there is more functionality in SmartList Builder. But this will get you started.