Consulting ToolKit

Consulting Tool Kit is a suite of powerful tools designed to help consultants and power users.  It contains Stored Queries, which allows end-users to securely execute ad-hoc tSql queries created by an admin, and Virtual Triggers, which provides the ability to quickly enhancement GP functionality with small bits of dexterity code.  Additionally, tt contains a number of Free Tools, including a complete set of SQL Server management tools written in Dexterity and a set of GP Tools to help locate data and find resources inside GP.

Consulting Toolkit Modules:

Stored Queries

Virtual Triggers

GP Report Extender

Free Tools

    • SQL Database Manager
    • SQL Query
    • SQL Trace
    • GP Finder
    • GP Resource Explorer
    • GP View Wizard

Stored Queries

YouTube_48x48Stored Queries provide the ability to create and save tSql queries which can be securely assigned to, and executed by, an end user.  If you have ever thought, “I wish I could give the user a button that would run this script,” then Stored Queries is for you!

A Stored Query can require the user to enter parameters, and return results for them to view (such as the results of a query).  To make it easy to move Stored Queries between company databases, servers, or different clients, Stored Queries can be imported and exported for easy transfer between systems.

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.  Contact WilloWare Support for the programming services to create Scriptlets.

Virtual Triggers has been used to:

  • Add an Additional Menu option on the GL Batch window to run a stored procedure
  • Add a GL Batch Posting control to prevent users from posting their own batches
  • Automatically set the MO Status checkboxes on the Edit MO Status window
  • Add a zoom to the Receivables Batch Note from the RM Cash Inquiry window
  • Automatically calculate a Trade Discount in Sales Transaction Entry
  • Prevent editing a saved (but not yet posted) sales transaction in Sales Transaction Entry
  • Only allow price increases in Sales Transaction Entry

GP Report Extender

GP Report Extender (GP-REX) adds Report Writer functions that can be used to add any field from any table to any GP Report Writer report.  GP-REX solves the age-old problem of “How do I add a field to this report if the table is not already linked in Report Writer.”

Use GP-REX to:

Add fields to a report when there tables you need are not linked in Report Writer

Perform complex text manipulations, such as masking a SSN, Bank Account, or other confidential information

Translate terminology on a report so it prints in a Customer’s language

Create new records when printing occurs–such as creating an audit of printing activity

Update records when printing occurs

GP-REX adds the power of tSql scripting to report writer functions available to any GP Report Writer report.

 

Free Tools

YouTube_48x48The three SQL Tools provide utilities for working with SQL Databases. The tools are completely written in dexterity and run inside the GP Client.  SQL Tools are an excellent way to provide powerful data access tools to users, without providing access to the SQL Server.

 

SQL Database Manager

Database Manager provides inquiry-only access to server, table, view, and stored procedure properties, and the ability to view data in SQL tables and views.

Click to Enlarge

From Database Manager you can access:

Query

Query allows you to create and run ad-hoc T-SQL queries.

Click to Enlarge
Click to Enlarge

You can save and run stored SQL scripts, and export query results to Excel.

Trace

Trace allows you to track the communication between the Dynamics GP and the database. This tool is invaluable for troubleshooting, and identifying tables needed for reporting.

Click to Enlarge
Click to Enlarge
Finder

Finder searches all tables in the Company Database for any user-entered value.  It locates the table, field, and dex_row_id of the record containing the search term.

finder

Resource Explorer

Resource Explorer searches the dexterity definitions of the tables, windows, and reports in Dynamics GP.  Quickly locate all tables containing a particular column name, find the product that contains a table using the SQL table name, or find windows that use a field.

ResExp

View Wizard

View Wizard is utility for creating SQL Views of Dynamics GP tables that using “human readable” column names.  For example, instead of ITMTRKOP the column name would be ItemTrackingOption.  This is a great tool for building user-friendly tables for reporting, or for use in complex queries creating views for SmartList Builder.