Rose Business Solutions

Yesterday I had a call from one of our customers, they recently upgraded all computers to Windows 7 64 bit, office 2010, since they upgraded every time they import a budget to GP, they can’t see it in FRx, the book name is populated but no data is printed. They can export the data using excel [...]

Business growth, cost optimization, customer relationships - these are among CEOs' top priorities this year, according to a recent survey of 704 CEOs conducted by The Conference Board. Addressing all three is no easy feat. As executives strive to maximize business growth......

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.

Did Oracle buy RightNow to show Salesforce.com who really is the Silicon Valley big dog?

Business growth, cost optimization, customer relationships - these are among CEOs' top priorities this year, according to a recent survey of 704 CEOs conducted by The Conference Board. Addressing all three is no easy feat. As executives strive to maximize business growth......

Business growth, cost optimization, customer relationships - these are among CEOs' top priorities this year, according to a recent survey of 704 CEOs conducted by The Conference Board. Addressing all three is no easy feat. As executives strive to maximize business growth......

Ok, the title it’s a bit demanding but when someone asks you this question regarding inventory here is a hint that will help you on your quest of finding all the “stuff”. Dynamics GP has several reports built in as well inquiries concerning inventory items, the most important in my career so far are the following: Back-Ordered [...]

Yesterday I had a call from one of our customers, they recently upgraded all computers to Windows 7 64 bit, office 2010, since they upgraded every time they import a budget to GP, they can’t see it in FRx, the book name is populated but no data is printed. They can export the data using excel [...]

"I don't like that guy," she said. "Why not," I wondered... It turns out that she had done some business with him years ago and it hadn't gone well. When pressed, though, she couldn't actually recall what the problem had...

Workday wows the Rising crowd with new functionality and a message or two to Oracle.

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.

Business growth, cost optimization, customer relationships - these are among CEOs' top priorities this year, according to a recent survey of 704 CEOs conducted by The Conference Board. Addressing all three is no easy feat. As executives strive to maximize business growth......

Did Oracle buy RightNow to show Salesforce.com who really is the Silicon Valley big dog?

The term has been around since 1988, but it's not truly understood by many. You can't buy earned media. It doesn't arrive on schedule. Earned media isn't free media, because the amount of time and energy and risk you have...

Business growth, cost optimization, customer relationships - these are among CEOs' top priorities this year, according to a recent survey of 704 CEOs conducted by The Conference Board. Addressing all three is no easy feat. As executives strive to maximize business growth......

Procrastination Hack : change and to or

When the form changes, so does the underlying business model, which of course changes the function as well. Mail ---> email Books ---> ebooks DVD ---> YouTube/Netflix 1040 ---> Online taxes Visa ---> Paypal Open outcry ---> Electronic trading Voice...

A friend was waiting to hear about the results of a job interview. He hadn't heard in a while and he asked me, "how long before I should start worrying?" Of course, the answer is, "you should never start worrying."...