SmartList Builder 2018
Tables
All SmartList Builder Lists are composed of at least one table. The main table is the first table selected for the SmartList Builder List. All additional tables are linked to this table, either directly or indirectly through another table. There are 6 types of tables that can be added to a SmartList Builder List; Microsoft Dynamics GP tables, SQL Server tables, SQL Script tables, SmartLists, Data Connections, and Extender Resources.
Microsoft Dynamics GP tables are defined in a Microsoft Dynamics GP dictionary. This includes third party dictionaries as well as the main Microsoft Dynamics GP application dictionary. When you select a Microsoft Dynamics GP table, the metadata contained in the dictionary can be accessed. Information such as the display names of the table and fields and list items for list fields are automatically defaulted.
A SQL Server table is any table or view that is contained in your Microsoft Dynamics GP SQL Server databases or any SQL Server database that resides on the same SQL Server as your Microsoft Dynamics GP databases. This feature should be used to access tables that are not contained in a Microsoft Dynamics GP table. You can also use SQL Server views to create complex joins and queries that you cannot create with SmartList Builder. Since there is no metadata for SQL Server tables, display names and list items are not defaulted.
A SQL Script table is a SQL select script that can be run against the current Dynamics GP company databases. Like SQL Server tables, this feature can be used to access tables that are not contained in a Microsoft Dynamics GP table and create complex joins and queries that you cannot create with SmartList Builder. The SQL Script option can be used to query tables outside of the current Dynamics GP database as well if the database is specifically included with the table call. Since there is no metadata for SQL Script tables, display names and list items are not defaulted.
A SmartList is an existing Dynamics GP SmartList. When a SmartList table is selected, all fields from that SmartList will added into the SmartList. SmartLists from Dynamics GP, Field Service, Project Accounting, Fixed Assets, Manufacturing, Human Resources, Canadian Payroll and Analytical Accounting can be selected.
Data Connections are pre-defined SQL views for the most common fields and tables used in Dynamics GP. Since there is no metadata for SQL Server tables, display names and list items are not defaulted. However, most of this information is not required because it is already included in the view.
Extender Resources are Windows and Forms that have been defined in Extender. When you select an Extender Resource, information such as the display names of the fields, decimal places for numeric fields and list items for list fields are automatically defaulted.
To add a Microsoft Dynamics GP table as the main table:
| 1. | Select Microsoft Dynamics GP Table from the Add button above the Tables list to open the Add Table window. |
| 2. | Select the Product, Series and Table. Use the Table Finder to quickly find a table based on a window, field name or field value. |
| 3. | Select Key fields for the table. There will only be one record displayed in the SmartList for each key field. |
| 4. | Click Save. It will take a few seconds while the fields for the table are initialized with default settings. |
To add a SQL Server table as the main table:
| 1. | Select SQL Server Table from the Add button above the Tables list to open the Add SQL Table window. |
 |
| 2. | Select the Database and Table. Select the Views option to display views contained in the selected Database. |
| 3. | If the table or view that you have selected is contained in more than one Microsoft Dynamics GP company database, you can mark the Use Company Database checkbox. When this checkbox is marked, SmartList Builder will use the table from the Microsoft Dynamics GP database that the user is currently logged in to. If this checkbox is unmarked, SmartList Builder will always use the selected database. |
| 4. | Select Key fields for the table. There will only be one record displayed in the SmartList for each key field. |
| 5. | Click Save. It will take a few seconds while the fields for the table are initialized with default settings. |
|
Security must be granted to tables and/or views in the database before this option will be available. See SQL Table Security for more details. |
To add a SQL Script table as the main table:
| 1. | Select SQL Script from the Add button above the Tables list to open the Add SQL Script window. |
| 2. | Enter the SQL Script. |
| 3. | Select Key fields for the table. There will only be one record displayed in the SmartList for each key field. |
| 4. | Click Save. It will take a few seconds while the fields for the table are initialized with default settings. |
|
Security must be granted database before this option will be available. See SQL Table Security for more details. |
To add a SmartList as the main table:
| 1. | Select SmartList from the Add button above the Tables list to open the Add SmartList window. |
| 2. | Select the SmartList that you want to add. |
| 3. | Click Save. It will take a few seconds while the fields for the table are initialized with default settings. |
To add a data connection as the main table:
| 1. | Select Data Connection from the Add button above the Tables list to open the Add Data Connection window. |
| 2. | Select the Product, Series and Data Connection. |
| 3. | Click Save. It will take a few seconds while the fields for the table are initialized with default settings. |
|
Security must be granted to data connections before this option will be available. See Data Connection Security for more details. |
To add an Extender Resource as the main table:
| 1. | Select Extender Resource from the Add button above the Tables list to open the Add SQL Table window. |
| 2. | Select the Resource Type and Resource Name. |
| 3. | Click Save. It will take a few seconds while the fields for the table are initialized with default settings. |