Posted on by WinthropDC Development, Dexterity, GP Power Tools, Products, Visual Studio Integration Toolkit

#GPPT #VSIT Using a ListView to display data with a Custom Form

David Meego - Click for blog homepageAs mentioned in my recent article, it is possible to add a ListView to a window using Modifier along with a hack. So today we are going to show how to use a ListView to display multiple records of data.

This is a practical example which works with Sales Transaction Entry (SOP_Entry) and shows the stock levels at each inventory site that has stock for the selected item on the window.

This project requires Visual Studio Integration Toolkit Build 18 (or later) Custom Forms module and GP Power Tools Build 30 (or later) Developer Tools module with Custom Forms support.

Creating the Project

The Project Setup window for the project is shown below:

Here are the steps used to build this project from concept to reality:

  • Create the Project in the Setup window and add the SOP_Entry form as the testing start point for the project.
  • Add a Runtime Execute script with the Script Purpose of 5986: EventRegisterForm to create our Developer ID and Form ID to reserve a Blank custom form. Delete the sample code not being used as we only need Blank Form code.
  • Add a Runtime Execute script with the Script Purpose of 5986: EventHandlerForm to handle form events. Delete the sample code not being used as we only need Blank Form code.
  • Create focus triggers after FORM_PRE and FORM_POST events of SOP_Entry form and use Helper Functions >> Working with VSIT Custom Forms to call FormOpen and FormClose API calls.
  • Open SOP_Entry (with starting triggers) so the custom blank form opens. Use Tools >> Customize >> Modify Current Window to go to the Modifier.
  • Create the Local Fields for the fields desired on the window (Item Number and Item Description) and use the ListView hack to get the ListView onto window.
  • Return to Microsoft Dynamics GP and the Project Setup window and add the modified window to the project using Add >> Customization Maintenance.
  • Adjust the Alternate Modified Forms and Reports ID to point to the modified version of the window or temporarily use GPPT Dynamics Product Selection to open the modified version.
  • Create a Runtime Execute Script to get the field data and window position and size from the SOP_Entry window and store them as Memory Parameters using Helper Functions. This code runs in the context of the original window in the Dynamics dictionary.
  • Update the Custom Form Event Handler script to clean up the Memory Parameters when the custom window closes.
  • Create a Runtime Execute Script to Redisplay Custom Form. This code runs in the context of the modified window in the Visual Studio Integration Toolkit dictionary. The code will read the Memory Parameters previously stored to populate the fields and reposition the custom form to the right of the SOP_Entry window based on its position and size.  This script uses the default form and default window commands so we can skip the “of window of form” parts of the naming the fields and easily change custom form number.
  • Add triggers after following events with each trigger loading and executing the Redisplay Custom Form Runtime Execute script: SCROLL_PRE on Line_Scroll window, Item Number of Line_Scroll CHG, Item Description of Line_Scroll CHG, WIN_PRE and WIN_ACTIVATE. This will keep the custom window current.
  • Add a Form Level Menu Trigger to allow the custom form to be re-opened if it is closed. Use Helper Functions to open the custom form and then call the Redisplay Custom Form script.
  • Update the Custom Form Event Handler script to call the Redisplay Custom Form script when the custom window is opened.

At this stage the custom form will open automatically in a docked position and refresh with the item information.

The final steps are to setup and populate the ListView from a SQL Query:

  • Create a Parameter List with the fields needed for a SQL Query to get the data desired. In this case it is just the Item Number.
  • Create a SQL Execute Query that uses the Parameter List and returns the desired data. You can execute it to test the data returned. I suggest returning the first column with the column in the parameter list even though we won’t include it in the ListView. This makes it easy to process the dataset returned by the SQL Query later.
  • Create a Runtime Execute Script for Setup Custom Form and add the code to setup the ListView when the custom form is opened. Below is example code for setting up a ListView and creating columns. This script uses the default form and default window commands so we can skip the “of window of form” parts of the naming the fields and easily change custom form number.
    Note: The modifier added ListView will not show in the Form Explorer used by the Names Button as it is not officially supported by Microsoft Dynamics GP.

[code]local boolean result;
local integer view_mode;
local integer column_index, column_width, sort_column, previous_alignment;

default form to VSTM_Forms_Blank_10;
default window to VSTM_Forms_Blank;

result = ListView_ItemRemove('(L) ListView', LV_ALL);
result = ListView_ColumnRemove('(L) ListView', LV_ALL);
result = ListView_SetEmptyMessage('(L) ListView', "No data to display");
view_mode = ListView_SetView('(L) ListView', LV_REPORT_VIEW);
column_index = ListView_ColumnAdd('(L) ListView', "Site ID", 0);
column_width = ListView_ColumnSetWidth('(L) ListView', column_index, 100);
sort_column = ListView_SetSortColumn('(L) ListView', column_index, LV_SORT_ASCENDING);

column_index = ListView_ColumnAdd('(L) ListView', "Qty On Hand", 1);
column_width = ListView_ColumnSetWidth('(L) ListView', column_index, 100);
previous_alignment = ListView_ColumnSetAlignment('(L) ListView', column_index, LV_ALIGN_RIGHT);
column_index = ListView_ColumnAdd('(L) ListView', "Qty Available", 2);
column_width = ListView_ColumnSetWidth('(L) ListView', column_index, 100);
previous_alignment = ListView_ColumnSetAlignment('(L) ListView', column_index, LV_ALIGN_RIGHT);

[/code]

  • Update the Custom Form Event Handler Script to load and execute the Setup Custom Form script (before the Redisplay Custom Form script is called).
  • Update the Redisplay Custom Form script to load and execute the SQL Execute script including loading and setting the parameter list and returning the data (without column headings).
  • In the case statement when data is returned, create a loop that uses the SQL Parsing Helper Functions to retrieve the fields from the returned dataset.
    Note: For best performance get the rows and columns in sequence. Don’t jump around.

[code]local integer l_row_number;
local string MBS_SQL_String_Value;
local string MBS_SQL_String_Value2;
local currency MBS_SQL_Currency_Value;
local currency MBS_SQL_Currency_Value2;

clear l_row_number;
repeat
increment l_row_number;
call with name "MBS_SQL_Parse_Data_String" in dictionary 5261, MBS_Text_Field, l_row_number {Row}, 1 {Col}, MBS_SQL_String_Value, MBS_Status;
if not empty(MBS_SQL_String_Value) then
call with name "MBS_SQL_Parse_Data_String" in dictionary 5261, MBS_Text_Field, l_row_number {Row}, 2 {Col}, MBS_SQL_String_Value2, MBS_Status;
call with name "MBS_SQL_Parse_Data_Currency" in dictionary 5261, MBS_Text_Field, l_row_number {Row}, 3 {Col}, MBS_SQL_Currency_Value, MBS_Status;
call with name "MBS_SQL_Parse_Data_Currency" in dictionary 5261, MBS_Text_Field, l_row_number {Row}, 4 {Col}, MBS_SQL_Currency_Value2, MBS_Status;

end if;
until empty(MBS_SQL_String_Value);

[/code]

  • The final step is to display the data read by adding an item to ListView and populating the columns. Below is example code for populating a ListView item.

[code]local boolean result;
local long item_index;

default form to VSTM_Forms_Blank_10;
default window to VSTM_Forms_Blank;

item_index = ListView_ItemAdd('(L) ListView', MBS_SQL_String_Value2, 0);
result = ListView_ItemSetSubitem('(L) ListView', item_index, 1, format(MBS_SQL_Currency_Value, false, true, l_dp_qtys-1, SYSTEMNEG));
result = ListView_ItemSetCurrencySubitem('(L) ListView', item_index, 1, MBS_SQL_Currency_Value);
result = ListView_ItemSetSubitem('(L) ListView', item_index, 2, format(MBS_SQL_Currency_Value2, false, true, l_dp_qtys-1, SYSTEMNEG));
result = ListView_ItemSetCurrencySubitem('(L) ListView', item_index, 2, MBS_SQL_Currency_Value2);

[/code]

We are done. I hope you found the process of how this project was created interesting and helpful. It should show how you work with building each part until the final functionality is completed.

The End Result

We can now open the SOP_Entry window (with the triggers enabled) and the custom window will open and be updated as we move around the transaction. Notice how it is “docked” to the right of the Sales Transaction Entry window.

Here is the form by itself so you can see it full sized:

Downloading and Installing

BEFORE IMPORTING THE PACKAGE: Check if you will be overwriting an existing custom form’s modified windows. If necessary, do not import the package initially, run the Runtime Execute Script to register the custom form(s) and check the form number(s) using the System >> Cards >> Custom Form Setup window. Once you confirm the form(s) being used, you can edit the package with Notepad to change the form numbers. The number for each form is listed in two locations at the start of the definition for that form. If you do change form numbers, you can use the Project Setup window’s Options menu >> Find in Script window to locate references that need to be changed in the code. Code using the default form command (when possible) makes these change simpler.

Download the example code and 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):

Note: The second archive is for VSIT build 18.2 as it uses the new FormCheckSecurity Helper Function to avoid errors when the modified version of the window is not selected. The third archive is for VSIT build 19.1 as it uses the new RegisterFormReserve Helper Function to request from 10 so that it avoids clashing with other projects.

Note: After loading the project, to complete the forms registration you will need to either manually execute the SOP_QTY_CUSTOM_FORM_01 Runtime Execute Script or login/switch companies again. 

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.

Note: Also make sure to change security to point to the modified window using Alternate Modified Forms and Reports IDs or temporarily with GPPT Dynamic Product Selection.

More Information

For more information see:

If you haven’t done so already, update to GP Power Tools Build 30 and later and Visual Studio Integration Toolkit Build 18 or later to be able to use the Custom Forms module.

Please install this sample project and have a look around and in conjunction with this article get an understanding of how you can use this practical example of the latest powerful functionality in your projects.

David

06-Sep-2023: Added information on checking and changing custom form details if necessary.
07-Sep-2023: Added screenshot showing “docked” window.
08-Sep-2023: Added updated code which needs VSIT build 18.2 as it uses the new FormCheckSecurity Helper Function.
24-Jul-2024: Added Trigger to refresh custom window when changing transactions.
08-Oct-2025: Added updated code which needs VSIT build 19.1 as it uses the new RegisterFormReserve Helper Function.

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

One thought on “#GPPT #VSIT Using a ListView to display data with a Custom Form”

  1. Pingback: #GPPT Adding a ListView (Grid) to any window | Winthrop Development Consultants Blog

Leave a Reply