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
Virtual Fields
GP Report Extender
Free Tools
-
- SQL Database Manager
- SQL Query
- SQL Trace
- GP Finder
- GP Resource Explorer
- GP View Wizard
Stored Queries
Stored 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
- Delete all empty Batches from the Inventory Series Post window
- Automatically set the Backflush Flag in the Manufacturing BOM window based on an Item UDF setting
- Auto-generate Batch IDs
- Require Vendor ID in Purchasing Requisition Entry
- Edit User Defined Fields from Sales Inquiry
- Open Item windows using the Default Site for the Item
- Price List and Default U of M by Store
Virtual Fields
Price $1200 Manual
Virtual Fields is a utility for adding fields (vFields) to GP windows without needing to using Modifier. The vFields are added in a way that does not affect existing security on GP windows.
Virtual Fields works together with the Virtual Triggers module to add fields to a window and be able to add “change scripts” to fields, and to get, save and delete the data entered in to the vFields.
Virtual Fields can be added to any window in a dexterity-based module (core GP windows or 3rd Party Products). In most cases, on all windows (core and 3rd party) the vFields can display information from the primary table linked to the window (this does not require Virtual Triggers as GP will normally populate all fields on the window that come from the primary table). However, the get/save/delete functionality only works on core GP windows.
For example, a vField could add a Go To Button to Customer Maintenance and then Virtual Triggers could add code to each go-to option.
Since Virtual Fields are in dexterity, they are compatible with the GP Web Client.
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
The 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.
From Database Manager you can access:
- Server Properties
- Stored Procedure Properties
- Table Properties
- View Properties
- Data in a View
- Data in a Table
Query
Query allows you to create and run ad-hoc T-SQL queries.
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.
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.
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.
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.