Skip to main content

Free Tools

Troy Gullacher

The following tools fall into two categories: SQL Management Utilities and GP Resource Utilities.

SQL Management Utilities:

  • Database Manager
  • Trace
  • Query

GP Resource Utilities:

  • Finder
  • Resource Explorer
  • View Wizard

In general, the utilities are all useful to consultants and power users who need to create reports or SmartLists, write SQL queries and updates, and find data in Dynamics GP.

The SQL Management Utilities provide much of functionality that is in SQL Server’s SQL Management Studio but do so from within Dynamics GP. These tools are particularly helpful in deployments where it is not easy to access the server but you require the ability to look at SQL tables and views, and to write SQL Queries.

The GP Resource Utilities help locating windows and tables that store specific information and assist in getting that data out in an easy to read format.

 

Database Manager

Database Manager is a “view only” utility for exploring your SQL Server. It provides full access to all databases on the server to view the SQL stored procedures, tables and views.

Navigation: Tools >> Utilities >> System >> SQL Database Manager

Server Properties: select the Server in the tree-view, then click the Properties button. The Server Properties window shows information about the configuration of the SQL Server, such as the number of processors, amount of memory, auto-start parameters, and memory usage. Consult SQL Server documentation for more information about each setting/property.

Stored Procedures: expand the tree-view to display the database objects, then click on Stored Procedures. The list-view on the right fills with the stored procedures in the selected database. Either double-click on a stored procedure, or select it and click properties, to view the Stored Procedure Properties.

Tables: expand the tree-view to display the database objects, then click on Tables. The list-view on the right fills with the tables in the selected database. Either double-click on a table, or select it and click Properties, to view the Table Properties. On the Database Manager window, Click Show >> All to view the data in the table.

 

Trace

Trace tracks all communication between the Dynamics GP client and the SQL database. This can be used for trouble-shooting, or to help track down which tables to use for reporting.

Navigation: Tools >> Utilities >> System >> Trace

The best way to use Trace is to have GP “ready” for the trace by setting up windows, or a transaction, that you want to monitor. For example, if you want to see which tables are used by the Item Maintenance window:

  1. Open an existing record in Item Maintenance
  2. Make a small change (such as changing the Shipping Weight)
  3. Start the trace (click START on the Trace window).
  4. Return to the Item Maintenance window and click the Save button.
  5. Stop the trace (click STOP on the Trace window).
  6. Click VIEW on the Trace window.

The View window displays the Trace Results in the following columns:

Row: this is a line number that increments for each line

Database: shows the database name used by the query

Table/Proc: shows the table name or stored procedure referenced in the query. Dynamics GP often uses stored procedures to communicate with the database. The table name is contained in the name of the stored procedure. For example, if the stored procedure name is zDP_IV40100F_1, this is a stored procedure that is communicating with the physical SQL table IV40100. Likewise, zDP_SY02000SS_1 is a stored procedure communicating with the table SY02000. This naming convention can be helpful in tracking down which tables are being used by a particular GP window or process.

Text: shows the full text of the communication being sent from GP to the database.

 

Query

Query is a tool for executing ad-hoc tSQL queries on the SQL Server.

Navigation: Tools >> Utilities >> System >> Query

Enter a query in the top text window then click the green PLAY Button to execute the query.

Query results can be exported to Excel by clicking the EXCEL button.

 

Finder

Finder is a tool for locating all tables in the GP company database that contain a specified user-entered value.

Navigation: Tools >> Utilities >> System >> Finder

Datatype: set the Datatype of the data you are searching for. Finder can search for strings, numbers and dates. Datatype also contains an option for Table Information, which is discussed further below.

Find: enter the search target. For strings Finder does a “contains” search. So if the search target is “red” is will find “Red phone” and “Blocks – Red”.

Find Button: after entering the search target click the FIND button. The first step in a search will be to locate tables that contain columns of the specified datatype. You will see the status message display “Adding….” as it locates columns & tables. When the status message displays “Searching…” the system is in the process of examining each table for the search target.

Get Table Info (  ): Click on a row in the results, then click this button to get information about the table. The button launches Resource Explorer and executes a search for the table information the table in the selected row. See the Resource Explorer chapter for more information.

Query Table For Selected Record (  ): Click on a row in the results, then click this button to query the selected table for the selected record. It opens the SQL Query window and automatically builds a query to return the complete record. See the Querychapter for more information.

Table Information

The Table Information query searches all installed dexterity-based modules for information about the table. In the “Find” box enter the physical name of the table (the name of the table in the SQL Server database, such as IV00101). This is the Table Name displayed in the first column of a Finder Search as described above.

After entering the SQL table name (this is case sensitive), click FIND. The results window will show the product (or products) which contain the specified table name. As shown above, Dynamics GP contains IV00101, but there are copies of it in Manufacturing and Report Scheduler.

 

Resource Explorer

Resource is a tool for locating Dexterity information about a window, report or table.

Navigation: Tools >> Utilities >> System >> Resource Explorer

Search: select Windows, Reports or Tables. The FIND option changes depending on the search type.

Find:

  • Windows – Double-click on a window in the results list to open it.
    • By Name: enter part of the Display Name of a window. The search will locate all windows that contain the search target in the window Display Name. For example, “Item” will return all windows with the word Item in the window name.
    • By Field on the Window: enter a field name, such as “Item Number”, or just “Item”, to locate all windows that contain the specified field (or portion of the field name). This search can take many minutes. GP often has a large number of fields that are on the window, but not visible, so the search may return windows that do not appear to have the specified field, but it may not be visible to the user. Opening the window in Modifier will reveal all of the other “hidden” fields.
    • An Open Window: sometimes the Display Name of a window is changed through code at the time the window opens, so what appears as the window name is not the actual “display name” of the window in Dexterity. This can make windows hard to find. Open the window you want to find, then click the Find button in Resource Explorer. It will display information about all open windows.
  • Reports – Double-Click on a report in the results list to open it.
    • By Name: enter part of the Display Name of the report (the name that appears in the title bar surrounding the report when it is printed to screen).
  • Tables
    • By Physical / Technical / Display Name: each GP table has three names: physical, technical and display. Enter the name, or portion of the name, to search for. For example, a search on “MSTR” would return all “Master” tables.
      • Physical is the table name in SQL server. For example, IV00101.
      • Technical is what is used in development to refer to the table, and does appear sometimes in Report Writer. The technical name often looks like this: IV_Item_MSTR.
      • Display Name is a “plain text” descriptive name of the table, and is often seen in Report Writer and SmartList Builder. For example: Item Master.

Results can be exported to Excel by clicking the EXCEL button.

 

View Wizard

The View Wizard is a tool for generating SQL Views of Dynamics GP tables where the column names are “human readable”. For example, a view of the Item Master table would contain a field called “ItemTrackingOption” rather than ITMTRKOP.

Use View Wizard to create views for end-user reporting.

It can also be used to create views which are then used in queries to build a more complex view for SmartList Builder. Since View Wizard views have human readable column names, your SmartLists will then also have readable column names.

Navigation: Tools >> Utilities >> System >> View Wizard

Product: Select a Product (such as Manufacturing)

Series: Select a Series (such as 3rd Party or Inventory).

The Tables list will populate. Columns can be sorted by clicking on the Column Header.

Select a table by double-clicking on it.

This will automatically generate a suggested View Name. The system verifies that the view name does not already exist in the database. If it does, you will be prompted to overwrite it, or keep the existing view.

Select fields from the Available Fields list to include in your view.

Click the CREATE VIEW button. The view will be created, and security granted to DYNGRP.

 

Comments

0 comments

Article is closed for comments.

Powered by Zendesk