The Importance of Selecting the Correct Extender Key Fields

I have had a couple of recent cases which highlighted the importance of selecting the correct Key fields when creating Extender objects. Both of the cases involved adding additional user defined fields to an existing Microsoft Dynamics GP window using an Extender Window Object. In both cases, the incorrect fields were selected in the Key Fields section of the Extender Windows setup window which caused undesired behaviour.

The first example was using the Sales Order Processing window where the 'Document Number' field had been selected as the Key Field. This caused issues for new SOP transactions as the 'Document Number' is not populated until the transaction has been saved at least once. Also 'Document Number' is not contained in the primary key for the SOP_HDR_WORK (SOP10100) table. For this example to work properly, the Key Fields need to be 'SOP Type' and 'SOP Number'.

The second example was using the Employee Maintenance window where the 'Employee ID', 'First Name' and 'Last Name' had been selected as Key Fields. This seemed to work until data started disappearing for specific employees. The problem here is that too many fields have been included in the Key Fields, and as the 'First Name' and 'Last Name' fields are still editable, they can be changed from the original value which "breaks" the link to the previously saved Extender data. For this example to work properly, the Key Field needs to be just the 'Employee ID'.

Below is the screenshot of the Extender Window setup with the incorrect Key Fields defined:

Here is the Employee Maintenance window with the Employee Additional Extender window showing:

With this current setup, if you were to edit the 'First Name' or 'Last Name' fields and save the record, when you reloaded that same 'Employee ID' and looked at the Extender window, the Additional Data would be blank.

Bottom Line: For Extender to be able to successfully associate additional data with a window, it needs to have the Key Fields specified so that they match the fields contained in the primary key of the table which holds the data for that window.

So now we know that we should be matching the primary key fields of the main table for a window, the question is how can we identify what the main table and primary key fields are?

The following section covers some techniques that should help you:

Using Table Import and/or Resource Descriptions and/or SQL Commands

While you have the window you wish to link to open, select Tools >> Integrate >> Table Import.  This will display a list of associated tables (using Display Names) for the current window, from which you should be able find the main table. Press Cancel to close the window.

If you are not sure which is main table, you can use Tools >> Resource Descriptions >> Windows to lookup your window. This will provide the same list of tables (using Technical Names), but also can provide the Auto-Linked Table which is often the main table.

We now know that the Payroll Master (UPR_MSTR) is the main table for the Employee Maintenance window. Using Tools >> Resource Descriptions >> Tables and then clicking on the lookup button we can select the table.

Once selected, the Table Descriptions window lists the Keys and the Key Segments for each Key. While not always the case, the first key on a table is usually the primary key.

You can also use SQL to check the keys, using the Physical Name you can run the following command from the Query Analyzer:

exec sp_helpindex 'UPR00100'

This command will return a list of the indexes and identify the primary key in the descriptions. Once you have the Physical Names for the fields for the primary key, you can go back to the Table Descriptions window to find the correct key and get the list of Technical Names for the fields.

So, the bottom line of this example is that the 'Employee ID' (EMPLOYID) field is the primary key of the Payroll Master (UPR_MSTR, UPR00100) table.

Using the Support Debugging Tool's Resource Information Window

If you have the Support Debugging Tool installed, you can use Tools >> Support Debugging Tool >> Options >> Resource Information to identify the table and primary key.

Once the Resource Information window is open, make sure the Resource Type is set to Form, Window & Fields and then locate your window. You can do this by typing in the Technical or Display Name, or by using the Menu Explorer (by navigation) or Resource Explorer (by dictionary) lookups. Below shows the Menu Explorer:

If desired you can drill down and select a field that you believe is contained in the primary key, for example the 'Employee ID', or you can enter it manually afterwards.

Click on the Associated Tables button to display the tables linked to the form (same as the information from Table Import or Windows Descriptions) with the option to filter to only display tables containing the selected field. 

Select the desired table from the list and it will swap the Resource Information window into Table & Field mode with that table already selected.

Finally, click the Display Keys button to show the list of keys. You can then move through the keys until you find the one with Primary = Yes.

IMPORTANT NOTE: If you change the Key Fields for an Extender Object after it has already been used, you will need to use SQL update statements to change the underlying data to match the new Key Fields. For the EMPLOYEE Extender Window Object in this example, the following commands would fix the data.

update E set E.PT_UD_Key = I.Key_Strings_1 from EXT00101 E
join EXT00100 I on I.PT_Window_ID = E.PT_Window_ID
and I.PT_UD_Key = E.PT_UD_Key
where E.PT_Window_ID = 'EMPLOYEE'

update E set E.PT_UD_Key = I.Key_Strings_1 from EXT00102 E
join EXT00100 I on I.PT_Window_ID = E.PT_Window_ID
and I.PT_UD_Key = E.PT_UD_Key
where E.PT_Window_ID = 'EMPLOYEE'

update E set E.PT_UD_Key = I.Key_Strings_1 from EXT00103 E
join EXT00100 I on I.PT_Window_ID = E.PT_Window_ID
and I.PT_UD_Key = E.PT_UD_Key
where E.PT_Window_ID = 'EMPLOYEE'

update E set E.PT_UD_Key = I.Key_Strings_1 from EXT00104 E
join EXT00100 I on I.PT_Window_ID = E.PT_Window_ID
and I.PT_UD_Key = E.PT_UD_Key
where E.PT_Window_ID = 'EMPLOYEE'

update EXT00100 set PT_UD_Key = Key_Strings_1,
Key_Strings_2 = '', Key_Strings_3 = ''
where PT_Window_ID = 'EMPLOYEE'

This code uses the Key_Strings_1 field stored in the EXT00100 table to update the data back to a single key field. If you need further assistance with fixing Extender data, please contact your support professional.

I hope you find this information on how best to set up Extender object keys and the methods of identifying the primary key fields useful. 

David

26-Oct-2011:  Replaced SQL Update Statements with statements not dependent on having a fixed field length.