SmartLists in Dynamics GP allow for extremely powerful and flexible reporting. Many ERP systems lack such a comprehensive query tool, so taking full advantage of SmartLists allow you to really reap the benefits of your Dynamics GP purchase. Whether you’ve already figured out how to use SmartLists on your own, or you’re just getting into them for the first time, reading below will allow you to use SmartLists like a pro.

Basic Navigation in SmartList

The left-hand side of the SmartList window is the Navigation Pane. The various folders within the Navigation Pane include the modules you have installed in your environment.

Opening any of these folders will show multiple sub-folders. These sub-folders are the actual SmartLists. When these SmartLists are expanded, the things you see are called favorites. The key point to remember is that any favorite can be recreated from the SmartList. They are simply a different selection of columns and filters applied to the SmartList.

The Asterisk (*) SmartList favorite is the default favorite with no search criteria and only the default columns included. In the example above, the “*” SmartList favorite will give the same results as selecting the “Purchase Orders” SmartList folder. The “Purchase Orders” folder is described as the “SmartList” and the “*” is described as the “Favorite”

The top of the SmartList window is called the Action Pane. It includes the various things that can be done with the Smartlist and/or the Smartlist data, which are described below.

• New: This creates a new SmartList (not a favorite, but an actual SmartList, ie; Folder). This will be opened in SmartList Designer by default but will open SmartList Builder if that has been installed.
• Modify: This modifies the current SmartList (again, not a favorite, but an actual SmartList, ie; Folder). As with New, this will open in SmartList Designer by default, but will open SmartList Builder if that has been installed.

NOTE: Normally New and Modify are only used by technical/admin staff, often in Accounting or IT. The next items are used by all Smartlist users.

• Search: Search allows you to add up to 4 search criteria to filter the results of the SmartList. This is often the first step in creating a new SmartList favorite. See the Search section for more details on how to use the search function.
• Refresh: Refresh the data on the current SmartList favorite. This is useful if data may have changed since the SmartList favorite was loaded.
• Columns: This allows you to Add/Remove/Rename columns on the SmartList favorite. See the Columns section for more details on how to configure columns.
• Favorites: This option allows you to add/modify a SmartList Favorite. See the Favorites section for more details on how to add/modify Favorites and create custom Reminders.
• Print: This option prints the current SmartList favorite, with the current data. The formatting looks similar to the look on the screen.
• Excel: This option is the most common way to extract data from SmartList. This will export the current SmartList favorite with the current data to a new Excel workbook. This allows for all the functionality of Excel to be applied to your SmartList favorite data. These exports will not automatically refresh later—the data is static from the time of export.
• Word: This is like the Excel export, but exports the data to Microsoft Word.
• Stop: This button stops loading data into the SmartList favorite. If your search criteria is not specific enough, but you don’t want to wait for your SmartList data to load, you can select the Stop button.
• GoTo: This button allows you to navigate to various other Dynamics GP Windows and SmartLists using the row selected on the SmartList data set as the source for that window. In the Purchasing example above, you can select a PO line, and use the GoTo to open the Purchase Order Inquiry window for the selected PO.
• Publish: This is available for custom SmartLists designed with a SQL View. This publishes to the Excel Refreshable Reports repository.
• SmartList Options: This button gives a menu-style selection for all the options displayed on the Action Pane (Search, Columns, GoTo, etc.).
• Additional: Additional user-defined windows (such as Extender windows) can be displayed here. Third-party products can also be launched from here if deployed (Extender and SmartConnect, for example, and be run based on a SmartList).
• File: The standard GP file menu, with the option to add a SmartList shortcut to your home page, print and open the Print Setup screen.
• Tools: The standard GP tools menu, with the option to open to play and record macros, and open customization/admin settings.
• Help: The standard GP help menu.

Search Basics

Much of the power of SmartList comes from the Search function. The general idea is that four different criteria can be applied to your SmartList to filter the results. Without filters, everything in that SmartList will be displayed. Sales Transactions, for example, will include all transactions that have ever been entered in the SOP (Sales Order Processing) module since Dynamics GP was originally implemented. In many cases, a date filter of some sort will be used.

For maintenance records (e.g. Cards), a date filter won’t usually be needed. A list of customers, for example, may be filtered based on the Active field, but won’t typically be restricted for a specific date range.

Filters (Search Types)

A search works by selecting the Column Name, selecting a Filter, and entering or selecting a value. Below are the filters (search types) available in SmartList.

• Is equal to: This is the default filter and looks at the Column Name selected for an exact match to the Value listed.
• Contains: This looks within the Column Name selected for any row that contains the Value listed. This value can be anywhere in the data. Searching for a value of “R”, for example, will include rows that contain “Received”, “Change Order”, but not “New”. This is frequently used when you don’t know the exact string to find or (for example) want to return multiple Customers with a similar name.
• Begins With: This looks in the Column Name selected for values that start with the Value supplied. All values are treated as strings, so a Value of 1 will return rows with 100, 10, 1A-A01, etc.
• Is Between: This looks in the Column Name selected for values that are between two Values listed. Selecting this option changes the Value field from a single field to two separate fields. The top value is the first, and the bottom value is the last. The Is Between filter includes the beginning and ending values, so between A and ZZZ will include all alphabetical values including “A”, “B” and “ZZZ”, but will exclude “ZZZZ”, which comes after “ZZZ”.
• Is Not Equal To: This looks in the Column Name selected for values that are not equal to the Value listed. Everything except the listed value is displayed and works well to display all documents not in a specified type, or (for example) all countries outside of the US (Country Code is not equal to US).
• Is Greater Than: This looks in the Column Name selected for all values that are greater than the Value listed. This is not inclusive. A Value of 0 will display all positive values, it will not include values with 0.
• Is Less Than: This looks in the Column Name selected for all values that are less than the Value listed. This is not inclusive. A Value of 0 will display all negative values, it will not include values with 0.

Advanced Search Options

You also have some advanced search options available. The Field Comparison checkbox will change the Value field from a user-input value to another Column Name lookup. This allows you to compare two values from your SmartList and only return rows based on that comparison.

For example, you could compare the “City” and the “City from Customer Master” on a Sales Transaction to show you orders for a customer that shipped to a city other than the Customer’s city.

The Match Case checkbox will search for a case-sensitive search. By default, the search functionality is not case-sensitive. When this is unchecked, A = a. When this is checked, A is not equal to a.

At the bottom of the screen is the Search Type selection. Match All is the default and is a restrictive search. In other words, Match All takes the first Search Definition, AND the second Search Definition, AND the third, etc. Match 1 or More is additive. It takes the first Search Definition, OR the second Search Definition, OR the third, etc.

If we had two search criteria for City is equal to Winnipeg, and City is not equal to Winnipeg, Match All would return no results. A city cannot be both Winnipeg and not Winnipeg. Match 1 or More would return every record. Every city is either Winnipeg or not Winnipeg.

One final note on this window is the Maximum Records field. This allows you to change the maximum number of records that can be displayed. Your SmartList will stop returning data when this is reached. Make sure it is set high enough to cover your expected dataset.

Clicking the Order By button opens a window that allows you to add multiple sort methods to your SmartList results. The first field sorts first (either Ascending or Descending), where there are multiple rows with the same value, the second sort will take effect, then the third, etc.

Sorting by GL Posting Date first, then Customer Number, for example, will sort by date, and rows, then sort all the day’s records by Customer Number.

Columns

The Columns button on the Action Pane (or from the Search window) will open the Change Column Display window. This lists all the columns currently displayed on the SmartList and allows you to change them.

The column on the Left (Display Name) allows you to rename a column to something that makes more sense for your report. The column on the right displays the original name, allowing you to find the appropriate column in GP windows or other reports that may use the original name from GP. I highly suggest using the Original name whenever possible, as it encourages use of the names GP uses throughout the interface.

The Add button brings up the Add Columns window, where you can select from the available columns in the SmartList. There is a button at the top of the screen, which allows you to change from “Available Columns” to “All Columns”. The “All Columns” selection allows you to select from all the available columns, while the “Available Columns” shows you the default columns that are listed as part of the SmartList.

It’s worth getting into the habit of changing this to “All Columns” whenever you go to add new columns. Adding columns not available under “All Columns” will require development. Typically, a new SmartList would need to be created in SmartList Designer (included in GP2013 SP2+) or SmartList Builder (from eOne Solutions)

NOTE: Multiple columns can be added at once using windows selection commands (Ctrl-Click to select multiples, and Shift-Click to select a range)

The Remove button removes the column currently selected on the Change Column Display window, and the Default button will remove all non-default columns and add the missing default columns for this SmartList Favorite.

Finding the Right Smartlist

In general, master records (Customers, Employees, Vendors, Items, etc.) have two different SmartLists that may be used. The master record itself (Customers, for example), and an address record (Customer Addresses). These are separated to ensure that a customer with multiple addresses does not show up multiple times on the Customers SmartList.

When looking for a list of Customers, for example, you’ll want to choose the Customers SmartList, rather than the Customer Address SmartList. In this case (and many others), some address information is still available. The customer’s Primary address is displayed in the Customers SmartList. The same is true for Employees and Vendors.

When looking for address information, the Customer Address SmartList will show all the details regarding all the different addresses a customer has assigned. If you want to look at an outstanding balance, or YTD Sales Revenue, you’ll see these listed multiple times in the Customer Address SmartList. Exporting this to Excel and summarizing by Customer Class would be problematic, as many customers could have duplicate records, and it could artificially increase your total outstanding balance.

This idea of granularity is important. If you know what level of detail, or granularity, needed before you choose your SmartList, you’ll be better suited to find the information you need. This same concept applies to transaction SmartLists. The Sales Transactions and Sales Line Items SmartLists differ in that the Sales Transactions SmartList gives you totals, but the Sales Line Items SmartList shows the line item detail from the transactions.

As always, if you need assistance finding the right SmartList, using SmartLists, or developing new SmartList Designer SmartLists, feel free to contact us.