#GPPT Modifying Reports based on Temporary Tables

David Meego - Click for blog homepageThe recent Build 28.8 hotfix of GP Power Tools incorporated some Developer Tools module changes to enable so very cool customization options for updating reports based on temporary tables. The big feature enhancement is the ability to store a Table Reference as a Memory parameter.

References in Dexterity are like pointers to a particular resource, so the Table Reference allows access to the particular instance table buffer without needing to pass the table as a parameter to a script.

For permanent tables, accessing a specific instance of a table buffer is very important when you want to work with the data of the current record used on a form’s table buffer. This is especially useful for tables used on scrolling windows, or when code stores data for additional windows in the the table buffer.

However, for temporary tables, it is even more important as each table buffer instance is a separate physical table. So you must use the exact same table buffer instance to get access to the data in the temporary table. If you don’t have the exact same table buffer, a new empty temporary table will be created.

Using the Table Reference Helper Functions

If you have access to a table buffer in a script because it is passed as parameter (such as a table trigger), you can use the new MBS_Memory_Set_Reference or MBS_Memory_Set_Table Helper functions to store the Table Reference into a GPPT Memory Parameter slot. The Helper Function used depends on if you have access to the table buffer or a table reference.

Then in a different script, you can use the MBS_Memory_Get_Reference Helper Function to read the stored Table Reference back so it can be used in your script. To re-instantiate a table, you use the table(Reference) function.

Finally, when you have finished using the Table Reference, use the MBS_Memory_Del_Reference Helper Function to release the Memory Parameter slot.

Why are some Reports based on Temporary Tables?

The Dynamics GP Report Writer is a single pass report writer. What that means on a practical level is that it can only support one 1 to many relationship at a given level in the table structure.

For example: if you have a transaction with multiple lines and each line can have multiple lot numbers or serial numbers associated with it, that is two 1 to many relationships at two levels. This is natively supported.

But, if we would also like to display additional 1 to many data associated with the header or with the lines, that is not possible without using a temporary table. So to add distribution or tax detail data to report as well we can use a temporary table with generic fields and populate the table with the data we want printed.

Another reason for using a temporary table, is that code might be needed to create the contents to include based on data in other tables. This could be because data is contained in tables across multiple product dictionaries or is just not stored in the format needed for the report.

Where are the Temporary Tables stored?

There are three possible types of temporary table that could be used and they are stored in three different locations:

SQL Temporary Table (Dexterity Table definition with physical name = TEMP)
This type of temporary table is stored in the TempDB SQL database with an actual name in the form of ##XXXXXX. It will be removed automatically when no longer needed. Using the Dexterity Table_GetOSName() function library command you can get the actual name of the table.

Ctree Temporary Table (Dexterity Table definition with physical name = TEMP and type = ctree)
This type of temporary table is stored in the the TEMP folder on the location workstation in ctree files in the form of TNTXXXX.dat & TNTXXXX.idx. It will be removed automatically when no longer needed.

Permanent Temporary Table (Dexterity Table definition usually with physical name with 5 as first digit)
This type of temporary table is stored in the system or company SQL database and uses additional fields of Company ID and User ID in the primary key to keep each session’s data separated. The data for a session is usually removed before and after it is used by code written by the developer.

Capturing a Table Reference

In GP Power Tools, there are a couple of ways to get access to a table buffer. Once you have access to the table buffer a reference to the table buffer can be created and stored away for future use.

  1. Recommended: Using a Table Trigger on the save record event, with the options set to Start Temporarily Disabled and Temporary Disable After Trigger Executed and the script uses the MBS_Memory_Set_Table Helper Function to store the reference. This trigger is enabled just before the temporary table is to be used and runs once when the first record is saved into the temporary table. This method is 100% reliable and works for temporary tables created inside functions and procedures where they are not associated with a form.
  2. Using a Focus Event with Table Trigger on an event on a form. GP Power Tools will attempt to capture a table reference on form open (but this will not work for tables linked to scrolling windows or set to open on form open). If that fails it will attempt to capture the reference on the first read event on the table. This method might not have captured the table buffer successfully when the trigger fires causing the trigger to abort. It also does not work for table buffer instances created inside functions and procedures.

Using a Table Reference

Once a table reference has been captured and stored, it can be used to access the table buffer it points to using the table() function to re-instantiate it.

Please note that this will only work while the table buffer exists, once the form, function or procedure that created it is closed or completed, the table buffer is closed and the table reference is no longer valid. Do not attempt to use a table reference that points to a table buffer that no longer exists.

A practical example using SOP Invoices

The best way to show how this new functionality in GP Power Tools can be used is by a practical example. This example leverages the fact that the SOP Invoice with Options reports use the Sales Document Temp (SOP_Document_TEMP) temporary table as the source of the data for the report using generic fields on the report layout.

The high level summary is that we can modify the report by manipulating the contents of the temporary table just before it prints.

The lower level details will be explained as we go through the components of the GP Power Tools project. Below is the Project Setup window showing the components:

The configuration settings file for this project is available from the bottom of this article. It is recommended to install the project and then review each of the components with their scripts to see exactly how the code is written.

Trigger: TABLE REF SOP 1

To ensure the SOP Invoice with Options report is printed rather than the normal version, this trigger turns on the Include Tax Details checkbox on the Sales Document Print Options window is selected and cannot be unselected. It also selects the Summary Taxes Only option by default.

Trigger: TABLE REF SOP 2

By capturing logs with GP Power Tools of the printing process, we identified that the global procedure: SOP_DP_Create_Forms_With_Options is executed before the report data is collected and so used this trigger point to temporarily enable the TABLE REF SOP 3 trigger using the MBS_Trigger_EnableSingle Helper Function.

Trigger: TABLE REF SOP 3

This is where part 1 of the magic happens. This trigger registers itself against the save record event of the SOP_Document_TEMP table and then temporarily disables itself.

It is enabled by the TABLE REF SOP 2 trigger just before the table contents are populated and executes once on the first save record event. The trigger script saves the table reference using the MBS_Memory_Set_Table Helper Function. Then the trigger disables itself again as its job is done.

Note: This trigger uses Help Functions in its script and Options which are only available in Build 28.8 onwards.

Trigger: TABLE REF SOP 4

This is where part 2 of the magic happens. From the logs we captured earlier we could see that the global procedure: SOP_DP_Opt_Single_Invoice is executed just before the report prints and so we can use this event to run our script to manipulate the contents of the temporary table just before it prints the report.

The script uses the MBS_Memory_Get_Reference Helper Function to read back the reference stored away by the TABLE REF SOP 3 trigger.

Then script performs the following steps using the table reference:

  • Because the actual temporary table is a local ctree table, it cannot be manipulated using SQL commands and joins to other tables. So the script defines a local anonymous table and then opens a duplicate temporary table using SQL as the database.
  • Now we can use the range copy command to transfer the records from the ctree local temporary table to the SQL temporary table.
  • Next we load the TABLE REFERENCE Parameter List and set the value of the Table Name field to the full physical path of the SQL Temporary table obtained using the Dexterity Table_GetOSName() function library command.
  • Now we can load and execute the TABLE REF SOP SQL Script to perform whatever manipulation of the SQL temporary table that we desire.
  • The last step is to remove the contents of the ctree temporary table using the remove range command and copy the now altered records from the SQL temporary table into the ctree temporary table using the range copy command.
  • An optional extra section of code uses standard Dexterity table commands to add a Header Item line just before the first line of the data in the temporary table. Just to show how you can use Dexterity or SQL to manipulate the table data.

Finally, the script calls the MBS_Memory_Del_Reference Helper Function as the reference is no longer needed (and will not be valid after the report is printed).

Trigger: TABLE REF SOP 5

This trigger is similar to the TABLE REF SOP 1 trigger but works for the Print Sales Documents window to turn on Include Tax Details and select Summary Taxes Only.

Parameter List: TABLE REFERENCE

This is Parameter List used to substitute the full table name for the SQL temporary table into the SQL script used to manipulate the table contents.

SQL Script: TABLE REF SOP

This SQL script builds and executes a dynamic script to update the SQL temporary table created by the TABLE REF SOP 4 trigger. The name of the physical table is substituted in automatically where the Parameter List placeholder is.

The script can perform any manipulation desired, in this example it just prefixes the Item Number and Item Description with TEST and Test respectively and multiplies the Sequence Number column by 10 to make space where additional lines can be added.

Dexterity Script: TABLE REF TEST

This script is not directly part of the example, but was used to test the MBS_Memory_Set_Reference, MBS_Memory_Set_Table, MBS_Memory_Get_Reference and MBS_Memory_Del_Reference Helper Functions using the Customer Master (RM_Customer_MSTR) table.

Downloading and Installing

Download the example code, import using the Project Setup window (now possible for first time imports with Build 28.8 onwards):

The code will be active on next login or after switching companies, or you can use start them manually from the Project Setup window.

More Information

For more information see:

David

This article was originally posted on http://www.winthropdc.com/blog.

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.