This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
Extender can create SQL views of the data in an Extender window which allows the data to be viewed as a single row in the view even though the actual data is stored as a multiple records across multiple tables (depending on data type).
This view can then be used by SmartList Builder to show the Extender data in SmartList.
However, the views created by Extender have a number of issues with them that makes them less than perfect.
- If the data for the first field is missing from the table, no data will be shown even if it exists for other fields.
- If the data for any of the other fields is missing from the table, a NULL will be returned. Dexterity does not understand the concept of NULL and will not be happy with a NULL being returned.
- If the labels for a List field are edited, the view will show the old values until the view is generated again. This is because it hard codes the list labels into a case/if statement.
An alternative is to code your own view which overcomes these issue by working of the key value in the PT_Extender_Window_Key_Values (EXT00100) table as the primary table for the join. It also has isnull() handling for the missing data and populates list labels dynamically from the setup table.
I have used Extender to add a Details window to the Customer Maintenance window with one field of each of the major data types. The screenshot is displayed below:
I then created a SQL view using the following code:
This is the output from the view based on the data shown in the screenshot above:
SQL View Output
The SQL Script for the MBS_ExtenderDetails view as well as the exported Extender Details window and a test view are attached at the bottom of this article.
The example here shows one field of each data type coming from one of the four tables:
EXT00101 – PT_User_Window_Field_Strings
EXT00102 – PT_User_Window_Field_Dates
EXT00103 – PT_User_Window_Field_Numbers
EXT00104 – PT_User_Window_Field_Times
If this was for a Detail window you would need to change the view to use the Detail tables:
EXT00181 – PT_Detail_Window_Field_Strings
EXT00182 – PT_Detail_Window_Field_Dates
EXT00183 – PT_Detail_Window_Field_Numbers
EXT00184 – PT_Detail_Window_Field_Times
You would also need to change the view to use the PT_Extender_Detail_Window_Key_Values (EXT00185) and PT_UD_Details_Window_Lines (EXT00180) tables instead of the PT_Extender_Window_Key_Values (EXT00100) table for the main join.
You should be able to take this example code and adjust it to work with any Extender data you have. Then you can join this view with any other tables you need to to create complex views that can be used with SmartList Builder, SQL Reporting Services or Crystal Reports.
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.