I’ve been asked a few variations of the questions below few times lately:
Can I have a Field 2 on my Extender Window/Form be required conditionally depending on what Field 1 is set as?
Or
Can I set a field on my Extender Window to be required conditionally based on what a GP window field is set to?
The stock answer is “No, that isn’t possible out of the box as the Required property is set at Design time when you create the window”. The field is either Required or it is not.
But recently the customer had Extender Enterprise and was wondering the same thing.
Knowing that Extender Enterprise is Dexterity-based and that we can do Dexterity scripting, would it be possible to make this happen? Well, I’d like to think so.
In the end, it was possible but you would have to know a fair amount about of Dexterity coding, a lot about the inner workings of Extender to make it work, and the ability to read Dexterity script & Dexsql logs to write this and troubleshoot any issues you find since this is all undocumented and not officially a feature.
We’ll start with a simple Extender Enterprise window based on the GP Purchase Order Entry window.
Figure 1: Basic Extender Window
In this case, our expected logic is:
- If the Vendor ID of the Purchase Order Entry window starts with “A” then Field 1 should be required. Otherwise, it should not be required.
- If Field 3 is greater than 0, then Field 4 should be Required. Otherwise, Field 4 should not be required.
And then I’ll a few more that you might not have thought of:
- If you change Field 3 from 0 to 3, Field 4 is required as noted. But then if you change it from 3 to 0, then Field 4 needs to be changed to no longer required.
- If you close the Extender window and re-open it, we should to also re-evaluate Field 3 to set the Required status of Field 4. This one isn’t quite as critical because if you were able to save the window previously then coming back into it again then our required data should be already entered. But it should show the correct “Required” status.
If we look at our questions of “when”, we would have to look at when we need to perform our action.
In case #1 and #4 above, to me this suggests that “When the Extender window opens, the field should either be required or not”.
We need to set the Required status for Field 1 and Field 4 but could do it in either one script or two – it wouldn’t really matter. I personally prefer two events because clear we are doing two different things to two different fields.
Figure 2: Open Window event to set our fields conditionally
Because we need to execute custom Dexterity scripting our Action Type for all our Actions will be of type “Run Dynamics GP script”.
Looking at the first one in Figure 2 – “set field 1 conditionally”, this is the event that will evaluate our condition (“Vendor ID starts with A”) and set Field 1 to be Required or not.
|
local string vendor; local integer field_id, field_number;
{this is the Field Number that we wish to work with, 1 to 15} field_number = 1;
{pull the Vendor ID from the PO Entry window} GetWindowValue( 0, {this is product id} “POP_PO_Entry”, {this is Form Name} “POP_PO_Entry”, {This is the Window Name} “Vendor ID”, {This is the target field} vendor); {variable declared as a local to hold return value}
{if vendor starts with A then we want to make Field 1 required} if substring(vendor,1,1) = “A” then {get the field id of the first window field as that is the one we want to make required}
‘Extender Window ID’ of table EXT_Extender_Windows = ‘Extender Window ID’ of window ‘User Defined Window’ of form <#FORM_NAME#>; get table EXT_Extender_Windows; field_id = ‘Extender Field IDs'[field_number] of table EXT_Extender_Windows;
{set range on our Field ID and switch it to required} ‘Field ID’ of table EXT_Data_TEMP = field_id; clear ‘Line Item Sequence’ of table EXT_Data_TEMP; range start table EXT_Data_TEMP; fill ‘Line Item Sequence’ of table EXT_Data_TEMP; range end table EXT_Data_TEMP;
change first table EXT_Data_TEMP; if err() = OKAY then ‘Required Field’ of table EXT_Data_TEMP = true; end if;
save table EXT_Data_TEMP;
range clear table EXT_Data_TEMP;
{format the prompt for this field to be required/not} call FormatPrompt of form ExtenderObject, ‘User Defined Prompts'[field_number] of window ‘User Defined Window’ of form <#FORM_NAME#>, (‘Required Field’ of table EXT_Data_TEMP = true), ‘Field Type’ of table EXT_Data_TEMP;
end if;
range clear table EXT_Data_TEMP;
|
Figure 3: Window Open event set Field 1
A few things to point out about the code above:
- The GetWindowValue() function above is the function that Extender uses to pull the “Key Values” as defined by Extender from GP windows. You pass it the Product/Dictionary ID and the Form, Window, and Field Name. The last parameter (vendor in this example) is the “out” value returned from the function. Uncomment the {warning vendor;} line to Extender would show you the returned value for testing purposes.
- The field_number variable is used for convenience since we must set the value in a couple of the scripts. This value is the Field 1 to Field 15 position of the Extender Field we are trying to make required or not. In this example, our field to be set is Field 1 – Conditional By Vendor ID, so it is set to 1.
- The reason we are working with the EXT_Data_TEMP table is because Extender populates this to hold temporary data that will be written to the permanent tables on the Save. Data is validated in the Extender Window by the underlying EXT_Data_TEMP table based on the ‘Required Field’ field in that table. Knowing this, the example code looks for the specific record and then sets value to true and saves the record.
- To be able to see the data, we have to know what the ##temp table name that is being used. The dexsql.log would be able to tell you that. Or you can add the line:
warning Table_GetOSName(table EXT_Data_TEMP);
to your script above and then query the table in SSMS directly. The name will be like ##12345 - Lastly, the example calls a built-in Extender function to set the correct prompt to show as “Required” in GP.
The next script is the code for “set field 4 conditionally” which sets the Field 4 required status and is very similar to the previous script.
|
local integer field_id, field_number;
{this is the field number we wish to work with, 1 to 15} field_number = 4;
‘Extender Window ID’ of table EXT_Extender_Windows = ‘Extender Window ID’ of window ‘User Defined Window’ of form <#FORM_NAME#>; get table EXT_Extender_Windows; field_id = ‘Extender Field IDs'[field_number] of table EXT_Extender_Windows;
{set range on our Field ID and switch it to required or not} ‘Field ID’ of table EXT_Data_TEMP = field_id; clear ‘Line Item Sequence’ of table EXT_Data_TEMP; range start table EXT_Data_TEMP; fill ‘Line Item Sequence’ of table EXT_Data_TEMP; range end table EXT_Data_TEMP; change first table EXT_Data_TEMP; if err() = OKAY then ‘Required Field’ of table EXT_Data_TEMP = (<Any Number> > 0); end if; save table EXT_Data_TEMP;
{format the prompt for this field to be required/not} call FormatPrompt of form ExtenderObject, ‘User Defined Prompts'[field_number] of window ‘User Defined Window’ of form <#FORM_NAME#>, (‘Required Field’ of table EXT_Data_TEMP = true), ‘Field Type’ of table EXT_Data_TEMP;
range clear table EXT_Data_TEMP; |
Figure 4: Open Window event set Field 4
The only real difference between the Field 1 example in Figure 3 and the Field 4 code from Figure 4 is that we don’t need the Vendor – instead we look at the <Any Number> since our condition for Field 4 is “<Any Number> > 0”. So, we used that as our condition above instead of hard coding to “true” as done previously.
For our last action, we need to set the Field 4 Required field status depending on the value of Field 3.
For this the best Event Type to use is the “Change field value” which only happens if the user changes a value. We could also use the “Exit Field” event which happens if the user just tabs through the field – but no reason to fire the event if nothing changed – but that event would work as well.
Figure 5: <Any Number> change field value event
When the event runs, we add the same “Run Dynamics GP script” Action Type as before. I don’t need to show this code because it is the same as the code in Figure 4. This one is easy – you can just copy it and paste it into this action.
Close and save your Actions and Save the Extender window and now we can give it a test.
Figure 6: Looks like it is working
Looking at the screenshot above, I’ve opened my test PO Required Example from the Purchase Order Entry window.
The Vendor ID on the PO is “ADVANCED0001” therefore Field 1 should be required. Check.
I entered 3 for “Any Number” and we can see Field 4 also shows as required. Check.
And while it is nice that we see the prompts showing as Red & Required, the better test is: What happens if I don’t enter a value for a newly Required field? Pressing Save gives us the error:
Not all the required fields have been entered.
That we get the error shows the required fields check appears to be working as well.
In this article, the interesting things (to me anyway) we’ve found/created are:
- Using GetWindowValue() function to read data directly from GP windows.
- Use of the EXT_Data_TEMP table for data manipulation. Normally we would just work with the UI but in this case, it was necessary to dig a little deeper.
- Using the FormatPrompt of form Extender object to change a prompt color.
I’ve included the custom window written using GP 2018 and Extender 18.0.0001 as well as text files for both scripts in a zip file that you can download HERE.
I should note that while this example worked for me on this specific version, since this is essentially a “hack” of Extender, there isn’t a guarantee that this would work for you on your version of Extender Enterprise. Since none of the methods used isn’t documented as an API or added as a feature, this type of scripting against Extender Enterprise wouldn’t be officially supported.
Regards,
Patrick Roth
eOne
