Locating Dynamics GP data using the Resource Descriptions windows
One of the most useful tools for locating data when being tasked with writing reports against Dynamics GP data is the Resource Descriptions tool within the application itself. Resource Descriptions are broken into three distinct windows: Tables, Windows, and Fields. Typically, we find ourselves using a combination of these three windows to locate the specific data we are looking for. There is not really a right or a wrong way to use these windows. Each report developer or consultant could argue that the way he or she uses the windows is the correct way; but ultimately, as long as we are able to identify and find the data we need, we will be that much closer to creating an accurate report that fills our users' needs.
The various Resource Descriptions windows are located by navigating to Microsoft Dynamics GP | Tools | Resource Descriptions.
Tables
Table Descriptions let us select a Product (Microsoft Dynamics GP, Fixed Assets, and so on) as well as the Series (Financial, Sales, and so on). We are then provided with a list of all the tables for the selected product and series. By default, the list of tables is by Display Name. We do have the option to change how the tables are sorted by changing the View By field. The options are Table Display Name, Table Group Technical Name, Table Physical Name, or Table Technical Name. The method we choose will depend on how familiar we are with the table structure. For example, we might select Table Physical Name once we are familiar with the naming convention guidelines provided earlier in this chapter. There is also a Find button that allows us to search for the table based on any one of its three names.
To access the Table Descriptions, follow these steps:
- Open Microsoft Dynamics GP.
- Click on the Microsoft Dynamics GP Menu from the toolbar.
- Select Tools.
- Select Resource Descriptions.
- Select Tables.
- Click on the Ellipses button and find your table. In the following screenshot, we are searching for the PM Vendor Master File table, which is found under the Microsoft Dynamics GP Product and Purchasing Series.
Once we have found the table we are looking for, we can drill into that table and get additional information for that table. This view includes all of the fields in the selected table, their physical names (as would be seen in the SQL tables), the storage type of the field, and the position.
We can access this additional information by double clicking on the table record in the Table Names window. This opens a new window called Table Descriptions; as shown in the following screenshot:
From the Table Descriptions window, we can further drill into each field and get information such as the Format Type, the Keyable Length of the field, and if applicable, any Static Values if it is a dropdown field.
We can access this detail by double-clicking on a field, or selecting the field and clicking on the Field Info button to open the Additional Field Information window, as shown in the following screenshot:
Returning to the Table Descriptions window, the last pieces of information we can access from this window are additional info such as any secondary tables, the secondary tables' keys, what the related fields are for the tables, and what the relationship type is. We can also see the usage, which gives us all the forms and reports that use this table.
Most of this information can be found by returning to the Table Descriptions window and clicking on the Additional Info button. This opens the Additional Table Information window as seen in the following screenshot:
Also, by clicking on the Usage button from the Table Descriptions window seen in an earlier screenshot, we can access the Table Usage window as seen the following screenshot by clicking on the Usage button:
Typically, we use Table Descriptions when we know the name of the table needed and we need to find additional information about that table.
Fields
The Field Descriptions window gives us the ability to again select both the Product and the Core (Series), which contains the field we are looking for. Once both of these fields are selected, we are provided with the field list. Similar to how we could look at field information in Table Descriptions, we can look at field information here as well.
To access Field Descriptions, follow these steps:
For example, in the following screenshot, Microsoft Dynamics GP has been selected as the Product, and System has been selected as the Core value. This shows us a list of fields that meet this criteria and a list of the tables that contain the selected field:
The one added benefit of using this window over others accessible via Resource Descriptions is that once we select the field we need, a list will be provided showing us all the tables that contain that field.
This window is typically used when we are not sure exactly which table(s) a requested field resides in. We can use this window to tell us all the table display names that contain the selected field, then we can go to Table Descriptions and find the physical (SQL) table name based on the results returned in the Field Descriptions window.
Windows
Windows Descriptions provides us with the ability to view the windows where the data is being recorded or viewed. As with the other Resource Description windows, we select the Product and Series. Products relate to the application code dictionaries, that is, Microsoft Dynamics GP, Fixed Assets, and so on. Series relate to the series the modules reside in, for instance, General Ledger and Bank Reconciliation reside in the Financial Series. Once we locate the window we are looking for, Purchase Order Entry for example, we will be provided with a list of both the fields in that window and the tables used by the window. We can also use the Form name in this window to find the related fields and tables.
To access Window Descriptions, follow these steps:
- Open Microsoft Dynamics GP.
- Click on the Microsoft Dynamics GP Menu from the toolbar.
- Select Tools.
- Select Resource Descriptions.
- Select Windows.
- Select a Product, Series, and View By from their dropdown lists.
As the following screenshot shows, selecting Microsoft Dynamics GP as the Product, Purchasing as the Series, and by Window Display Name as the View By option presents us with a list of all windows available in this unique combination:
We typically use Window Descriptions when we know where the requested information is being entered or displayed in Dynamics GP but we are unsure of the field name(s) or the table name(s) used to store the data. As with Field Descriptions, once we have a list of possibilities, we can cross-reference to Table Descriptions.
The Table Descriptions window is a bit constrained in size, and when dealing with a Product and Series with numerous tables, it can often be time-consuming to scroll through the list to find the right window. Don't neglect the Find button in the upper-right hand corner of this window. This works well, assuming we have already made our Product and Series selection in the appropriate drop-down boxes. After selecting this button to open the Find window, we can type in the Display Name we are looking for and the window will auto-focus on this row in the scrolling window. Remember, the Display Name corresponds to the name of the window as it appears in the menu bar attached at the top of the window in GP.