Consulting Toolkit Manual

Download PDF version here.

2.Installation #

The installation process for all WilloWare products is identical.  Please review the general installation instructions at www.willoware.com/install.

3.Registration #

All WilloWare software uses the same Registration window.  See the general Registration Window instructions here:

//willoware.com/online/registration-window/

4.Security #

Security

CTK contains tools that can provide users access to confidential data, and which also have the potential to cause severe damage to the database. Access to these tools should be carefully considered. The tools in CTK which present a special security or database integrity risk are described below.

  • SQL Database Manager: With SQL Database Manager a user can select any table in any database and view the data inside the table (or SQL View). Both Database Manager and its Show Data window are view-only tools, so there is no possibility of damaging data with these tools.
  • SQL Query: SQL Query is similar in functionality to SQL Server Version 6 “Query Analyzer” window. Users with access to this tool can view any data in the database. Additionally, they can execute queries against the database, potentially changing or deleting data.
  • Finder: Finder locates table records which contain a search term. Since it searches all tables, it is possible for the tool to return results containing confidential data.
  • Stored Query Maintenance: similar to the Query tool, this window has the ability to create SQL Queries. A user constructed query has the potential to be damage data and the database. The Stored Query Maintenance window should only be given to administrator-level users. The Execute Stored Query window can run a saved query, but does not give the user any ability to view or edit the query in any way.

The installation process will create a Security Task and a Security Role called CONSULTKIT. The Security Task will have access to all windows in this product, and the Task will be automatically assigned to the Role.

To provide access to all windows in this product you can either:

  • Add the CONSULTKIT Role to a User, or
  • Add the CONSULTKIT Task to a User’s existing Role

Do NOT modify the CONSULTKIT Security Task. Each time an update is installed for Consulting Toolkit, it will recreate the Task so it has access to everything in the module, and any changes will be lost.

5.About #

The About Window for all WilloWare products is identical.  Open the About Window by going to Help >> About Microsoft Dynamics GP.  From that window select Additional >> About {WilloWare Product/Customization}.

{WilloWare Product/Customization} = the installed product, such as GP PowerPack or LabelLink.

Please refer to the general About Window guide here: www.willoware.com/online/about-window

6.Overview #

Overview

Consulting Toolkit is a collection of utilities for Dynamics GP. It consists of three core modules and several free tools.

Core Modules:

  • Stored Queries – This tool allows creating and saving pre-defined SQL queries that can be executed by an end-user through the Execute Stored Query window. The Execute Stored Query window allows the user to pick a Stored Query from a list and execute it but does not allow making changes to the query. This tool allows you to create ad hoc queries that can be easily and securely executed by an end without them having to use Query Analyzer.
  • Virtual Triggers – This utility provides the ability to add small code changes to Dynamics GP. This tool is NOT intended for end users. It requires advanced Dexterity programming skills to use this window. It provides WilloWare with an inexpensive way to quickly provide customers with custom programming functionality without the need to install custom code. Please contact WilloWare for programming services when purchasing Virtual Triggers.
  • GP Report Extender – This tool makes it easy to add information to GP Report Writer reports where the data comes from a table that is not linked to the report.

Free Tools:

  • SQL Database Manager – an “Enterprise Manager” or “SQL Management Studio” type tool for exploring the SQL Server. It provides read-only access to the properties of the server, databases, tables, views and stored procedures.
  • Trace – a “SQL Profiler” type tool for tracing the communication between the GP client and the SQL server
  • SQL Query – a “Query Analyzer” type tool to creating, saving, and executing ad-hoc tSQL queries.
  • Finder – a search tool for locating all tables that contain a particular user entered value.
  • Resource Explorer – a search tool for locating Dexterity product information about a field, window, table, or report.
  • View Wizard – a tool for creating SQL views using the Dexterity “descriptive names” for column names.

7.Stored Query Maintenance #

Stored Query Maintenance

HelpID: 460022022

Stored Queries provide the ability to create and store ad hoc tSQL queries that can be executed in a secure manner by an end-user. An administrator can create a query which an end user can run, but the user will not be able to view the query or make any changes to it. A query can also have parameters, such as an Item Number, which the user must provide before running the query.

Navigation: Tools >> Utilities >> System >> Stored Query Maintenance

Query ID: Provide an ID for the Query.

Name: When a user runs a Stored Query, they will use the Execute Stored Query window. The Execute Stored Query window has a drop-down list of Stored Query Names that the user has access to.

Description: Description is also displayed in the Execute Store Query window to help the user identify what the query does.

Execute Only: The user will be able to select and run the query, but it will provide no feedback to the user other than completion status. A query must be marked as Execute Only for it to appear in the Execute Stored Query window.

Show Results: The Execute window will display the results of the query. The Show Results option also enables the Export To Excel button on Execute Stored Query. Otherwise the Excel button is hidden.

Email Results: The Execute window will automatically export the results to a CSV file, create an email to the specified Email address, and attach the file to the email.

Email: The email results are sent to this address.

Execute In Database: Select the database in which the query will be executed. When the Execute Stored Query window is opened, it will only show users queries to which they have been granted security, and which are assigned to the current company database or DYNAMICS database. The “All Companies” option means that the query will be available in all company databases (not that it will execute across all databases at the same time).

7.1.Parameters #

Parameters

HelpID: 460022039

The first step to creating a query is to write the query in Query Analyzer (SQL Management Studio) to ensure the syntax is correct. The Query window in Consulting Toolkit can also be used.

The next step is to create the parameters. When a stored query is executed, the user will be prompted to enter Parameters (if there are any). For example, perhaps the user wants to find all item numbers that begin with a specific user entered string (i.e. all items beginning with ABC). The SQL query would look like this:

Select * from IV00101 where ITEMNMBR like ‘ABC%’

The Stored Query using a parameter would look like this:

Select * from IV00101 where ITEMNMBR like ‘{1}%’

Click the PARAMETERS button to open the Parameters window.

The first parameter you create will have ID {1}, and the second will be {2} and so on. The ID is auto-generated. After creating parameters, add the numbered parameters into the Query in the place where you want to substitute user entered data.

Select * from IV00101 where ITEMNMBR like ‘{1}%’

Delete a parameter by selecting the row, then clicking Edit >> Delete Row.

Query Builder Window:

Enter the tSql query. To ensure correct syntax is used, the query should be created in Query Analyzer and pasted into this window. A simple query is:

Select * from IV00101

Parameters are added by inserting the Parameter ID. For example:

Select * from IV00101 where ITEMNMBR = ‘{1}’

Note that the apostrophes before and after the parameter are included in the query because it is searching for a string. The parameter {1} will be replaced by the user entered data.

When the Execute Stored Query window runs a query with parameters, it replaces only the parameter, such as {1}, with the data entered by the user. Here is another example showing a numeric parameter:

Select * from IV00101 where ITMTRKOP = {1}

This query would retrieve all item numbers where the Item Tracking Option = (the value entered by the user). In this case there are no apostrophes because the user entered data (the Parameter) is a number.

When designing a Stored Query, keep in mind that the parameter will be replaced with the user entered data as-is.

7.2.Security #

Security

HelpID: 460022040

Click the Security button on the Stored Query Maintenance window.

Security is set per user per Stored Query. Add users who should have access to the Stored Query. The Execute Stored Query window will only show users the Stored Queries to which they have been given security.

By default “SA” and “DYNSA” have access to all queries.

Remove a user by selecting the row, then clicking Edit >> Delete Row.

7.3.Import/Export #

Import/Export

HelpID: 460022041

The GoTo button on Stored Query Maintenance provides options to Import and Export a stored query.

Export: select a Stored Query, then click the GoTo button and select Export. On the Export window, click the Folder icon and select a path to save the Stored Query. Click Process to export the query.

Import: Clear the Stored Query window, then click GoTo and select Import. On the Import window, click the folder icon and select the Stored Query to import. Click Process to import the query.

8.Execute Stored Query #

Execute Stored Query

HelpID: 460022023

Execute Stored Query allows a user to select from one of the pre-defined Stored Queries and run the query. No changes can be made to the query from this window.

Navigation: Tools >> Utilities >> System >> Execute Stored Query

Stored Query Name: The drop-down list shows the Query Names. A query appears in this list when:

  • The user has security access to the query
  • The query is assigned to the current company database, or All Companies, or Dynamics
  • The query has the Execute Only box marked

Description: displays the Stored Query description.

Execute Button: Click the EXECUTE button to run the query. If the query requires parameters, the Parameters window will open. If the query does not require parameters, the query is executed after clicking the button.

The Query will run when the DONE button is clicked on the Parameters window.

Cancel Button: A query that returns a large amount of data can take a long time to display. The Cancel Button will stop filling the window with results.

Excel Button: If the query is marked to Show Results, the Excel button will be enabled.

9.Virtual Triggers #

Virtual Triggers

Virtual Triggers is a utility for adding “scriptlets” to GP. A Scriptlet, or small script, is a small bit of dexterity which modifies the normal functionality of GP or adds new functionality. Virtual Triggers and Scriptlets can replace the need to create a separate dexterity customization when all that is needed are small tweaks to GP functionality.

For example, a Scriptlet could automatically mark checkboxes when a window opens or change labels on fields or hide fields without needing to modify a window. Since Virtual Trigger’s Scriptlets are dexterity, they are compatible with the GP Web Client.

Virtual Triggers is a dexterity development tool and is not intended for end-users. Creating Scriptlets requires advanced level experience with Dexterity (sanScript).

Contact WilloWare Support for the programming services to create Scriptlets.

9.1.Setup #

Setup

HelpID: 460022027

Navigation: Tools >> Utilities >> System >> Virtual Triggers Setup

WilloWare will send Scriptlets in text files which can be imported into the Virtual Triggers Setup window.

IMPORT: Click the Import button, then select a Scriptlet file.

Click OPEN.

The Scriptlet will be read from the file, saved to the database, then displayed in the VT Setup window. If the Scriptlet already exists, you will be asked to confirm that you want to overwrite the existing Scriptlet.

DATABASE: the Scriptlet file will NOT contain Database IDs. The Scriptlet will only run in the database(s) selected here.

Click the DATABASES tab.

Mark the database(s) in which you want the Scriptlet to run. Click the Mark All or Unmark All buttons to select/de-select all databases.

Click the DATABASES tab to hide the window.

ACTIVE: by default the Scriptlet will be marked as ACTIVE. Unmark this box to disable it.

SAVE: click SAVE to save the Scriptlet and clear the window.

REFRESH TRIGGERS: after making any changes to Scriptlets, or importing new Scriptlets, clear the window then click the Refresh Triggers button. This will ensure your GP Client is running the current Scriptlets. Other users will need to log-out of GP, then log-in again to have the new Scriptlets activated.

IF YOU RECEIVE ANY WARNINGS DUING WHEN IMPORTING A SCRIPTLET, YOU MAY BE ATTEMPTING TO IMPORT AN OLDER SCRIPTLET FILE THAT THE UTILITY CANNOT READ. FOLLOW THE MANUAL ENTRY STEPS DESCRIBED BELOW.

To manually add a Scriptlet to your system, you will need to copy-paste information from the text file into the Virtual Trigger Setup window.

//willoware.com/wp-content/uploads/2017/01/VirtualTriggers2.png

The top section of the text file contains the information needed for the top section of the window.

Copy the TRIGGER ID from the file and paste it into the Trigger ID field on the window.

Repeat for the Description and Product ID.

Mark the ACTIVE checkbox.

Enter one or more Databases in the Database list.  The Scriptlet will only be active in the Companies you enter here.

//willoware.com/wp-content/uploads/2017/01/VirtualTriggers3.png

 

The next section of the Scriptlet file contains the TRIGGER.  Make sure that TRIGGER is selected in the View.  Then copy the “trigger” section of the text file and paste it into the window, as shown above.

//willoware.com/wp-content/uploads/2017/01/VirtualTriggers4.png

The final section of the Scriptlet file contains the SCRIPT.  Make sure that SCRIPT is selected in the View.  Then copy the “script” section of the text file and past it into the window, as shown above.

Click SAVE.

Click REFRESH TRIGGERS.

The Scriptlet is now active for the current user. Other users will need to close GP, then log-in again to activate the Scriptlets in their GP client.

9.2.Commands #

Commands

Virtual Triggers Scriptlets supports all Dexterity (sanScript) commands and Function Library commands. In addition, the following Commands are provided by language extensions in the Virtual Trigger module.

Exec

Purpose

Execute a pass-through tSql statement.

Syntax

call with name “Exec of form wwSQL” in dictionary 4600, Database, SQL Statement, Suppress Errors, Error Message;

Parameters

  • Database – if left blank (“”) it will default to the current database (‘Intercompany ID’ of globals).
  • SQL Statement – a text parameter containing the tSql statement to execute.
  • Suppress Errors – a Boolean (true/false) that controls whether an error message dialog box will display.
  • Error Message – a string identifying the Scriptlet, such as the Trigger ID or Trigger Description. If the SQL statement generates an error the Exec command will display the SQL Error, the Error Message provides more information about what code generated the error.

Exists

Purpose

Execute a pass-through tSql statement that has a true/false return. This is commonly used to see if a certain record or condition exists in the database.

Syntax

call with name “Exists of form wwSQL” in dictionary 4600, Database, SQL Statement, Error Message, Exists;

Parameters

  • Database – if left blank (“”) it will default to the current database (‘Intercompany ID’ of globals).
  • SQL Statement – a text parameter containing the tSql statement to execute.
  • Error Message – a string identifying the Scriptlet, such as the Trigger ID or Trigger Description. If the SQL statement generates an error the Exec command will display the SQL Error, the Error Message provides more information about what code generated the error.
  • Exists – a Boolean return parameter.

GetAcct

Purpose

Retrieve a GL Account stored using SaveAcct.

Syntax

call with name “GetAcct” in dictionary 4600, Array Number, Account Number;

Parameters

  • Array Number – in integer from 1 to 20.
  • Account Number – an account number field.

GetDate

Purpose

Retrieve a date stored using SaveDate.

Syntax

call with name “GetDate” in dictionary 4600, Array Number, Date;

Parameters

  • Array Number – in integer from 1 to 20.
  • Date – a Date field.

GetDlr

Purpose

Retrieve a currency/dollar amount stored using SaveDlr.

Syntax

call with name “GetDlr” in dictionary 4600, Array Number, Dollar;

Parameters

  • Array Number – in integer from 1 to 20.
  • Dollar – a currency/variable currency/dollar/quantity field.

GetInt

Purpose

Retrieve a longer integer amount stored using SaveInt.

Syntax

call with name “GetInt” in dictionary 4600, Array Number, LongInt;

Parameters

  • Array Number – in integer from 1 to 20.
  • LongInt – a long integer or integer field.

GetStr

Purpose

Retrieve a string value stored using SaveStr.

Syntax

call with name “GetStr” in dictionary 4600, Array Number, String;

Parameters

  • Array Number – in integer from 1 to 20.
  • String – a string field up to 255 characters.

SaveAcct

Purpose

Save a GL Account Number for use by another Scriptlet. Use GetAcct to retrieve it.

Syntax

call with name “SaveAcct” in dictionary 4600, Array Number, Account Number;

Parameters

  • Array Number – in integer from 1 to 20. Keep track on the array number used as that same array number must be used in GetAcct to retrieve the value.
  • Account Number – an account number field.

SaveDate

Purpose

Save a Date for use by another Scriptlet. Use GetDate to retrieve it.

Syntax

call with name “SaveDate” in dictionary 4600, Array Number, Date;

Parameters

  • Array Number – in integer from 1 to 20. Keep track on the array number used as that same array number must be used in GetDate to retrieve the value.
  • Date – a date field.

SaveDlr

Purpose

Save a dollar/currency/variable currency for use by another Scriptlet. Use GetDlr to retrieve it.

Syntax

call with name “SaveDlr” in dictionary 4600, Array Number, Dollar;

Parameters

  • Array Number – in integer from 1 to 20. Keep track on the array number used as that same array number must be used in GetDlr to retrieve the value.
  • Dollar – a currency/dollar/variable currency field.

SaveInt

Purpose

Save a long integer for use by another Scriptlet. Use GetInt to retrieve it.

Syntax

call with name “SaveInt” in dictionary 4600, Array Number, LongInt;

Parameters

  • Array Number – in integer from 1 to 20. Keep track on the array number used as that same array number must be used in GetInt to retrieve the value.
  • LongInt – a long integer or integer

SaveStr

Purpose

Save a string value for use by another Scriptlet. Use GetStr to retrieve it.

Syntax

call with name “SaveStr” in dictionary 4600, Array Number, String;

Parameters

  • Array Number – in integer from 1 to 20. Keep track on the array number used as that same array number must be used in GetStr to retrieve the value.
  • String – a string field up to 255 characters.

10.Report Extender #

GP Report Extender

Dynamics GP Report Extender (GP-REX) provides the ability to add any field from any table to any GP Report Writer Report.  If you purchased a license that includes GP-REX, it is automatically enabled (there are no settings for it inside Dynamics GP). To use GP-REX you must be proficient with writing queries in tSQL.

Overview

GP-REX works through an integration to two existing Report Writer functions:

rw_TableLineCurrency

rw_TableLineString

Since these functions are part of core Dynamics GP, they are available when modifying any Report Writer report (i.e. they are present when working with a core GP report, or with a report from any 3rd Party Product or customization).

GP-REX connects these two functions to corresponding SQL Stored Procedures:

wrwTableGetLineCurrency

wrwTableGetLineString

The CTK installation routine creates these stored procedures.  They have some sample code in them which should be removed before adding your own tSql.

Adding a field using GP-REX requires two steps:

Using Report Writer, create a Calculated Field using one of the two functions above, and add that field to the report.

Using SQL Management Studio, modify the corresponding stored procedure to retrieve the desired value.

10.1.Get Started #

Get Started

It may help to think about the GP Report Writer functions as simply “GetString” and “GetCurrency”, rather than rw_TableLineString and rw_TableLineCurrency.  There is not anything inherently “line level” about either one of these.  They simply provide several input parameters that can be used to pass information into the stored procedures, which can then be used to retrieve the desired information.

Begin by thinking about what information you will need to write a tSql query to retrieve the desired data.  For example, to retrieve anything from the Item Master you need the Item Number.  To retrieve something from the Item Quantities Master you will need Item Number and Location Code.

For both GetString and GetCurrency you have the following input parameters to work with:

String-1

String-2

Integer

Decimal-1

Decimal-2

Integer Control Parameter (more on this later)

These are the input values you will have available to locate information from SQL. You can use up to two strings, two decimals, and one integer.

Another tip is to think creatively about these parameters.  A string can store a string, but it can also store a date, number or boolean.  A decimal field can store a currency amount, an integer or a boolean.

Pro Tip: for advanced users, you can concatenate multiple string values into one string parameter.  For example, the Report Writer calculated field could append multiple values into String-1:

‘Customer Number’+”~”+’Address Code’

Your tSql would then need to parse each value out of String-1.

Each string can hold up to 255 characters.

For this example, we are going to retrieve a field from the Item Master, so we need just one string: Item Number.

IMPORTANT: If the value you want to retrieve requires knowing an Item Number, the GP Report MUST have Item Number as an available field.  If the tables/fields available on the report do not provide the key fields needed to retrieve the desired value from another table, you will not be able to retrieve that data.

10.2.SQL Server #

SQL Server

GP-REX connects two GP Report Writer functions to SQL Stored Procedures. Most of the work of adding a new field to a report with GP-REX will be done on the SQL Server by adding code to a stored procedure. The stored procedures are created by the Consulting Toolkit installation.

Before editing the stored procedure, start by creating a tSql query that returns the desired data. For example:

Report Extender

Since the value we want to return, USCATVLS_1 is a string, we will use the wrwTableGetLineString stored procedure.  Locate wrwTableGetLineString in SQL Management Studio, right-click on it, then select: Script Stored Procedure as >> Alter To >> New Query.

Report Extender

wrwTableGetLineString is shown below:

Report Extender

wrwTableGetLineString, when created by the CTK installation, looks like the script above (there are a couple of small changes which are explained below).  The stored procedure has two primary sections:

Program Control

The tSQL Code “if @iCtrl = 1 goto ItemUDF1” is a program control.  For each Calculated Field you create in report writer, you will add a new section to wrwTableGetLineString.  As shown above, the stored procedure has been modified to handle two different Calculated Fields which display Item UDF1 and Item UDF2.  Note that if you need to use Item UDF1 on multiple reports, you do not need to “repeat” that section in the stored procedure–you would always use @iCtrl=1.

Return Script

The Return script is a “named” block of code.  As shown above, “@iCtrl = 1” tells the script to go to a section called ItemUDF1 (this is just a name you make up to label that block of code).  Copy the tSql you wrote earlier into the new Return Script section.  Replace parameters as needed.  Above the hard-coded ‘100xlg’ is replaced with the input parameter @Str1.  The implication is that the Calculated Field will need to provide Item Number in the first string parameter, while all of the other parameters will be empty.

Pro Tip: the use of “goto” is controversial and is generally considered poor coding practice.  It works particularly well in this case and is easy to use.  This method of controlling program flow is NOT required. You may use any method you choose to direct the stored procedure to return different outputs depending on the value of @iCtrl.

wrwTableGetLineString will be used to retrieve every custom field you need, so the Program Control section may eventually have a large number of options.

IMPORTANT: every Return Script section must end with “goto EndCase”.  Do not modify EndCase.  This is required to ensure that the program jumps from the Return Script to the end of the script without executing any of the other Return Scripts.  In the example above, if the ItemUDF1 section did not contain “goto EndCase”, the program would continue to execute ItemUDF2 and therefore would always return the value from ItemUDF2 because it would always be the “last” code to run.

Save changes to the stored procedure.  The next step is to create a Calculated Field in Report Writer.

10.3.Report Writer #

Report Writer

Open the report in Report Writer, then follow the steps below to add a Calculated Field to your report.

The two Report Writer functions you can use are:

rw_TableLineCurrency

rw_TableLineString

They have the same input parameters.  One returns a string value, the other returns a currency value.  The input parameters are:

Product ID: this will always be 4600 (the Product ID for CTK)

String-1: (Up to 255 characters)

String-2: (Up to 255 characters)

Integer: (-32768 to +32767)

Currency-1: (-99,999,999,999,999.99999 to +99,999,999,999,999.99999)

Currency-2: (-99,999,999,999,999.99999 to +99,999,999,999,999.99999)

Integer Control: this must correspond to the @iCtrl value created earlier in the stored procedure.  For example, 1 = Item UDF1, 2 = Item UDF2.

Pro Tip: the input parameter datatypes are strictly enforced.  A common issue occurs when working with Line Numbers.  Some GP line numbers are long integers and some are currency.  Report Writer will give an error if a long integer is passed in to one of the currency fields.  A long integer must first be converted to a currency before being passed in to the currency parameter.  This is done by creating a calculated field (i.e. cLineNum) where the Result Type = Currency, and the Expression contains only the long integer field.

Create a new Calculated Field.  Name it and select Result Type = String.

//willoware.com/wp-content/uploads/2017/02/REX4.png

Select Core = System, Function = rw_TableLineString.  Click ADD.

//willoware.com/wp-content/uploads/2017/02/REX5.png

Product ID: Select Constants >> Type = Integer, Constant = 4600.  Click ADD

//willoware.com/wp-content/uploads/2017/02/REX6.png

String 1: The remaining fields are specific to this example.  You will need to provide either fields from the report tables, or default values.  In this case, we are adding ‘Item Number’ from the Item Master.

//willoware.com/wp-content/uploads/2017/02/REX7.png

String 2: In this example, String-2 is not used, so an “empty” string must be provided.  Select Constants >> Type = String, leave the Constant field empty, click ADD.

//willoware.com/wp-content/uploads/2017/02/REX8.png

Integer: In this example, Integer is not used.  This field is often used for Document Type.  Select Constants >> Type = Integer, Constant = 0, click ADD.

//willoware.com/wp-content/uploads/2017/02/REX9.png

Currency-1: In this example, Currency-1 is not used.  This field is often used for a Line Number.  Select Constants >> Type = Currency, Constant = 0.0000, click ADD.

//willoware.com/wp-content/uploads/2017/02/REX10.png

Currency-2: In this example, Currency-2 is not used.  This field is often used for a second Line Number, such as the SOP Line Component Sequence field.  Select Constants >> Type = Currency, Constant = 0.0000, click ADD.

//willoware.com/wp-content/uploads/2017/02/REX11.png

Integer Control: This field is critical.  The integer control is fed into @iCtrl in the stored procedure, so this field is ultimately controls what data is returned to the calculated field.  Select Constants >> Type = Integer, Constant = 1 (or your control integer), click ADD.

Click OK to save the Calculated Field, then add it to the report.

11.Free Tools #

Free Tools

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.

11.1.Database Manager #

Database Manager

HelpID: 460022004, 460022011, 460022016, 460022019, 460022007

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.

11.2.Trace #

Trace

HelpID: 460022018

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.

11.3.Query #

Query

HelpID: 460022008

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.

11.4.Finder #

Finder

HelpID: 460022005

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 Query chapter 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.

11.5.Resource Explorer #

Resource Explorer

HelpID: 460022010

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.

11.6.View Wizard #

View Wizard

HelpID: 460022020

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.

Help Guide Powered by Documentor
Suggest Edit