#GPPT Reversing the sort order of the Item History window

A recent post on the GPUG (Dynamic Communities) forums asked How to change the default sort order for Item History. The Item History (IV_Item_History) window in Microsoft Dynamics GP shows period summary data from the IV_SUM_HIST_Period (IV30102) table based on the Item Number, Site ID and date range on ascending period basis.

This means that once you have a data for a number of periods, the latest data is always at the bottom of the window and will require the user to scroll down to the bottom to view it.

The forum thread asks if there is a way to reverse the default sort order of the displayed data which is date ascending order.

The answer is that there is no option available on the user interface to change the index used in the scrolling window. In fact there is no index available in Dexterity on the table that would provide the required filtering and sorting we need.

Impossible Challenge

To make this customization work seemed like an impossible challenge, but doing the impossible is something GP Power Tools is really good at.

Using the functionality in the latest builds of GP Power Tools (Build 28.8 or later required) we can capture a reference to the IV_SUM_HIST_Period table buffer on the IV_Item_History form.

We can then use the little known assign as key Dexterity command to create a virtual key based on the same index as used when filling the scrolling window, but changing the year and period fields to descending order instead of the default ascending order.

Note: A virtual key is a memory based key that allows Dexterity to work with a custom index and is implemented in SQL as a Order By statement.

Combining the table reference and virtual key with updated range selection and fill window commands allows for the records to be displayed in descending order. Triggers are used to re-display the scrolling window contents after the original scripts run using the parameters passed to the original scripts.

Note:Access to Dynamics GP source code helped make sure the scripts that fill the window are identified and that the ranges set in the triggers matches the original code (except for the descending columns).

As usual the Project Setup window is used to create a customization project to group together all the components.

The customization contains the following triggers:

IV HIST SORT 1

Table read trigger set to start disabled and disable itself after execution. Its job is to capture the table reference and save it as a memory variable. It also creates a virtual key based on the existing index, but with the appropriate date based fields set to descending order and saves the key index number as a memory variable.

IV HIST SORT 2

Focus trigger on form open, enables the IV HIST SORT 1 trigger so it will run when any data is read from the IV_SUM_HIST_Period table and then disable itself.

IV HIST SORT 3

Focus trigger on form close to delete the two memory variables used for the table reference and the virtual key.

IV HIST SORT 4

Form Level Procedure trigger with parameters on the FillHistoryAnnual script to set the range based on the parameters passed into the script and fill the window using the table reference with the virtual key.

Note: Setting a range for descending columns must be set in reverse compared to an ascending column. For example: Instead of going from low or cleared value to high or filled value, you must start with the high or filled value and end with the low or cleared value.

IV HIST SORT 5

Form Level Procedure trigger with parameters on the Fill_History script to set the range based on the parameters passed into the script and fill the window using the table reference with the virtual key.

Note: Setting a range for descending columns must be set in reverse compared to an ascending column. For example: Instead of going from low or cleared value to high or filled value, you must start with the high or filled value and end with the low or cleared value.

Once all the triggers were enabled, the window can be used as normal, but the data in the scrolling window is shown in descending order.

Downloading and Installing

Download the example code, import using the Project Setup window (now possible for first time imports with Build 28.8 onwards), or use Configuration Export/Import window (for older builds):

• GP Power Tools Settings Iv Hist Sort.dbg.zip

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

More Information

For more information see:

• GP Power Tools Portal: http://winthropdc.com/GPPT
• GP Power Tools Samples: http://winthropdc.com/GPPT/Samples
• GP Power Tools Videos: http://winthropdc.com/GPPT/Videos

David

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