Dex - Filtering a Third Party Lookup

Article
10/22/2008

David Meego When I started this blog, one of my goals was to help developers push the boundaries of what they believe is possible. This was the foundation of the materials presented at conferences by myself and Mark Rockwell. For more information on the conference sessions and materials have a look at the Cross Dictionary Dexterity Development post.

Over the last couple of days I have been chatting with Mariano Gomez about some code he was working on. He needed to filter a lookup from a 3^rd party dictionary. There is some information on techniques that can be used in the Pushing the Limits materials, but I thought I would expand on this with a working example.

Business Case

Using the Field Service Equipment Maintenance window (Cards >> Service Call Management >> Equipment). Assuming the Customer's State has been entered, and Office ID is blank, when the Office Lookup is opened, filter the lookup to only include offices in the same state as the customer.

This is based on the the SVC_Office_Lookup form in the Field Service dictionary and uses the SVC_Office_MSTR table. The lookup is being called from the SVC_Serial_Maintenance form in the Field Service dictionary.

Boring Theory Stuff

We want to use a range table where command on the SVC_Office_MSTR table. To do that we need to execute() a script using pass through sanScript against the SVC_Office_MSTR table, but not just any instance of the SVC_Office_MSTR table buffer, it must be the same table buffer used on the SVC_Office_Lookup form.

The execute() function runs scripts as though they are global procedures. This means that unless a table buffer is passed as a parameter, the global procedure will create a new separate instance of the table buffer. So, how can we pass the SVC_Office_MSTR table buffer from the form when we can't compile code referring to the SVC_Office_MSTR table as it does not exist in our dictionary.

We need to pass the table as a reference (pointer) which can then be instantiated using the table() function. So now, how can we capture the reference to form's table buffer. A cross dictionary trigger on the FORM PRE will have access to the table buffer but we will not be able to reference the SVC_Office_MSTR table. So what we are going to do is register a database read trigger against the SVC_Office_MSTR table limited to the SVC_Office_Lookup form and when that trigger fires we can capture the reference to the table.

So we don't slow down the system, once we have captured our reference we can disable the database trigger. We also need to make sure that when the form closes that we clear the reference as it is no longer valid. In our code we can make sure that we only try to use the reference when it is assigned correctly.

Once we have the table reference we can use pass through Dexterity sanScript to apply the a where clause to the table and then use run script delayed on the Sort By or Redisplay field to get the lookup to re-fill the scrolling window with the now filtered records.

Note: If the lookup window you are filtering uses a range clear command in its code, you will need to make sure you apply the range table where clause after the original code has completed and will probably need to use the fill window command to get the window to redisplay the filtered records.

Method

I will be prefixing my resources with MBS, you can use whatever prefix is appropriate for your company. The completed code for Microsoft Dynamics GP v10.0 is also added as an attachment to this post.

Create a Workset to keep all the resources for this customisation in one location.
Create a field 'MBS Table Reference' to store the table reference as Data Type Reference.

'MBS Table Reference'

Create a field 'MBS Table Trigger Tag' to store the tag of the trigger we will be using as Data Type INT.

'MBS Table Trigger Tag'

Create a field 'MBS Lookup Filter' to store the value to filter the lookup by as Data Type STR30_State. This needs to be the same data type as the field we are filtering on.

'MBS Lookup Filter'

Add these 3 fields as Globals so they can be referenced as system variables.
Add a constant MBS_PROD_ID_FIELD_SERVICE with a value of 949 so we can refer to the Field Service Dictionary easily.

MBS_PROD_ID_FIELD_SERVICE

Add the following messages which will be used in the script:

22001: Set Lookup Filter ...
22002: F
22003: Set Lookup Filter (blank for none):

Now all that is required is to write the scripts. There are 8 scripts for this example and they are listed below in the order they would be executed. This should help show the flow of the code and help you understand how it all works:

The Startup script is normally used to register triggers. Because I am using cross dictionary triggers I have moved the triggers common to this same destination dictionary into a secondary procedure. This makes it easier to abort registration when a product is not installed.

Global Procedure: Startup

{ Global Procedure: Startup }

{Register Cross Dictionary Triggers for Field Service }
call Startup_Field_Service;

This procedure registers all the cross dictionary triggers for Field Service after checking that we running in the runtime environment and that Field Service is actually installed. Note the use of the pre-compiler pragma command to prevent literal string warnings.

Global Procedure: Startup_Field_Service

{ Global Procedure: Startup_Field_Service }

pragma(disable warning LiteralStringUsed);

{ Running in Test Mode }
if empty(Launch_GetFileName()) then
abort script;
end if;

{ Field Service Not Installed }
if Launch_GetProdPosition(MBS_PROD_ID_FIELD_SERVICE) < 1 then
abort script;
end if;

{ Register Triggers on opening and closing of the Lookup form }
if Trigger_RegisterFocusByName(MBS_PROD_ID_FIELD_SERVICE, "form SVC_Office_Lookup", TRIGGER_FOCUS_PRE, TRIGGER_BEFORE_ORIGINAL, script MBS_SVC_Office_Lookup_FORM_PRE) <> SY_NOERR then
warning "SVC_Office_Lookup form pre focus trigger registration failed.";
end if;
if Trigger_RegisterFocusByName(MBS_PROD_ID_FIELD_SERVICE, "form SVC_Office_Lookup", TRIGGER_FOCUS_POST, TRIGGER_AFTER_ORIGINAL, script MBS_SVC_Office_Lookup_FORM_POST) <> SY_NOERR then
warning "SVC_Office_Lookup form post focus trigger registration failed.";
end if;

{ Register Trigger to add Addtional Menu }
if Trigger_RegisterFormByName(MBS_PROD_ID_FIELD_SERVICE, "SVC_Office_Lookup", getmsg(22001){Set Lookup Filter ...}, getmsg(22002){F}, script MBS_SVC_Office_Lookup_FORM) <> SY_NOERR then
warning "SVC_Office_Lookup form trigger registration failed.";
end if;

{ Register Database Trigger used for capturing the table reference }
if Trigger_RegisterDatabaseByName(MBS_PROD_ID_FIELD_SERVICE, "SVC_Office_MSTR", "SVC_Office_Lookup", TRIGGER_ON_DB_READ, script MBS_SVC_Office_Lookup_Table_READ, 'MBS Table Trigger Tag' of globals) <> SY_NOERR then
warning "SVC_Office_MSTR table read trigger registration failed.";
end if;

{ Register Trigger to set filter when lookup is used }
if Trigger_RegisterFocusByName(MBS_PROD_ID_FIELD_SERVICE, "'Lookup Button 10' of window SVC_Serial_Maintenance of form SVC_Serial_Maintenance", TRIGGER_FOCUS_CHANGE, TRIGGER_BEFORE_ORIGINAL, script MBS_SVC_Serial_Maintenance_Lookup_CHG) <> SY_NOERR then
warning "Lookup Button 10 of SVC_Serial_Maintenance form change focus trigger registration failed.";
end if;

pragma(enable warning LiteralStringUsed);

This trigger handling procedure runs when the lookup button is pressed, it checks if the Office ID has been entered and if it is empty it will set the global lookup filter variable to the State field on the Equipment Maintenance window.

Global Procedure: MBS_SVC_Serial_Maintenance_Lookup_CHG

{ Global Procedure: MBS_SVC_Serial_Maintenance_Lookup_CHG }

local text code;
local string compiler_error;

clear 'MBS Lookup Filter' of globals;

{ Running in Test Mode }
if empty(Launch_GetFileName()) then
abort script;
end if;

{ Field Service Not Installed }
if Launch_GetProdPosition(MBS_PROD_ID_FIELD_SERVICE) < 1 then
abort script;
end if;

pragma(disable warning LiteralStringUsed);

{Build the pass-through sanScript code.}
clear code;
code = code + "inout string INOUT_filter; " + char(13);
code = code + "if isopen(form SVC_Serial_Maintenance) then " + char(13);
code = code + " if empty('Office ID' of window SVC_Serial_Maintenance of form SVC_Serial_Maintenance) then " + char(13);
code = code + " INOUT_filter = 'State' of window SVC_Serial_Maintenance of form SVC_Serial_Maintenance; " + char(13);
code = code + " end if; " + char(13);
code = code + "end if; " + char(13);

pragma(enable warning LiteralStringUsed);

{Execute the code.}
if execute(MBS_PROD_ID_FIELD_SERVICE , code, compiler_error, 'MBS Lookup Filter' of globals) <> 0 then
{A compiler error occurred. Display the error.}
warning compiler_error;
end if;

This script ensures that the Table Reference is cleared when the lookup form is first opened and that the trigger for capturing the table reference is enabled.

Global Procedure: MBS_SVC_Office_Lookup_FORM_PRE

{ Global Procedure: MBS_SVC_Office_Lookup_FORM_PRE }

clear 'MBS Table Reference' of globals;

{ Trigger should be enabled, but just in case }
if not empty('MBS Table Trigger Tag' of globals) then
Trigger_EnableSingle('MBS Table Trigger Tag' of globals);
end if;

This is the table trigger handling script that captures the table reference and stores it into a global variable and then disables itself. It also calls the function which applies the filter to the lookup.

Global Procedure: MBS_SVC_Office_Lookup_Table_READ

{ Global Procedure: MBS_SVC_Office_Lookup_Table_READ }

inout anonymous table SVC_Office_MSTR;

assign 'MBS Table Reference' of globals as reference to table SVC_Office_MSTR;

{ Disable Trigger now that we have the Reference }
Trigger_DisableSingle('MBS Table Trigger Tag' of globals);

{ Apply Filter }
MBS_SVC_Office_Lookup_Filter();

This function is the engine of the code, it makes sure that the product is installed and that the table reference has been captured and then uses passthrough Dexterity sanScript to apply the filter by using a range table where command against the instantiated instance of the global table reference.

Global Function: MBS_SVC_Office_Lookup_Filter

{ Global Function: MBS_SVC_Office_Lookup_Filter}

function returns integer OUT_Status;

local text l_whereclause;
local text code;
local string compiler_error;

{ Running in Test Mode }
if empty(Launch_GetFileName()) then
OUT_Status = 1;
abort script;
end if;

{ Field Service Not Installed }
if Launch_GetProdPosition(MBS_PROD_ID_FIELD_SERVICE) < 1 then
OUT_Status = 2;
abort script;
end if;

{ Check Reference assigned }
if empty( 'MBS Table Reference' of globals) then
OUT_Status = 3;
abort script;
end if;

{ Build Where Clause }
clear l_whereclause;
if not empty('MBS Lookup Filter' of globals) then
l_whereclause = physicalname(field 'State') + CH_SPACE + CH_EQUAL + CH_SPACE + SQL_FormatStrings('MBS Lookup Filter' of globals);
end if;

pragma(disable warning LiteralStringUsed);

{Build the pass-through sanScript code.}
clear code;
code = code + "in reference IN_reference; " + char(13);
code = code + "in text IN_whereclause; " + char(13);
code = code + "if isopen(form SVC_Office_Lookup) then " + char(13);
code = code + " range table(IN_reference) where IN_whereclause; " + char(13);
code = code + " run script delayed '(L) Sort_By' of window SVC_Office_Lookup of form SVC_Office_Lookup; " + char(13);
code = code + "end if; " + char(13);

pragma(enable warning LiteralStringUsed);

{Execute the code.}
if execute(MBS_PROD_ID_FIELD_SERVICE , code, compiler_error, 'MBS Table Reference' of globals, l_whereclause) <> 0 then
{A compiler error occurred. Display the error.}
warning compiler_error;
end if;

OUT_Status = OKAY;

This form trigger handler script is used to allow the filter to be edited or removed while the lookup form is open.

Global Procedure: MBS_SVC_Office_Lookup_FORM

{ Global Procedure: MBS_SVC_Office_Lookup_FORM }

local string l_filter;

l_filter = 'MBS Lookup Filter' of globals;

if getstring(getmsg(22003){Set Lookup Filter (blank for none):}, false, l_filter) then
'MBS Lookup Filter' of globals = upper(substring(l_filter, 1, 30));

{ Apply Filter }
MBS_SVC_Office_Lookup_Filter();
end if;

This trigger handling procedure is used to clean up when the lookup is closed and re-enable the table trigger for the next time the lookup is used.

Global Procedure: MBS_SVC_Office_Lookup_FORM_POST

{ Global Procedure: MBS_SVC_Office_Lookup_FORM_POST }

clear 'MBS Table Reference' of globals;
clear 'MBS Lookup Filter' of globals;

{ Turn Trigger back on for next time }
if not empty('MBS Table Trigger Tag' of globals) then
Trigger_EnableSingle('MBS Table Trigger Tag' of globals);
end if;

Conclusion

Once all the scripts and resources have been entered your Resource Explorer in Dexterity should look like the screenshot below:

All you need to do now is compile the dictionary and create a chunk. Remember because this is all cross dictionary code, it cannot be tested in Dexterity Test Mode, it must be installed into the runtime (multi-dictionary) environment as a chunk file.

Example code for v10.0 is attached at the bottom of the article.

The chunking macro XDicDemo.mac is expecting the files to be installed in a C:\Dex1000\XDicDemo folder. If you install elsewhere, edit the macro with Notepad.exe. The chunking macro is currently set to leave the source code in the chunk and so in the resulting extracted dictionary.

You will need to run Developer Update from Dexterity Utilities to combine the code from the XDicDemo.dic into a XDic_Dyn.dic (renamed v10.0 Dynamics.dic) dictionary.

Please let me know what you think of this posting. Would you like to see more of this Advanced Topic stype of posting?

David

18-Nov-2008: A quick note to say that this method of capturing a form's table buffer is only needed for a 3rd party or cross dictionary form. For a form in the Dynamics.dic you can just use a focus trigger on the FORM_PRE event to capture the form's table buffer. This does not work for cross dictionary as you need to ue the execute() function which works like a global procedure and so loses access to the form's table buffers.

XDicDemo.zip

Dex - Filtering a Third Party Lookup

Additional resources