Creating SQL Views of Extender Data
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:
Code for MBS_ExtenderDetails SQL View Example
if
exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MBS_ExtenderDetails]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop
view [dbo].[MBS_ExtenderDetails]
GO
create
view MBS_ExtenderDetails
as
select
X0.PT_Window_ID
, X0.PT_UD_Key as [Key Field]
-- , isnull(X1.TOTAL,0) as [Checkbox Value]
, case isnull(X1.TOTAL,0) when 1 then 'true' else 'false' end as [Checkbox]
, isnull(X2.TOTAL,0) as [Currency]
, isnull(X3.DATE1,'1900-01-01 00:00:00') as [Date]
-- , isnull(X4.TOTAL,0) as [List Value]
, isnull(XL4.LONGNAME,'') as [List]
, isnull(X5.TOTAL,0) as [Number]
, isnull(X6.STRGA255,'') as [Short String]
, isnull(X7.TIME1,'1900-01-01 00:00:00') as [Time]
, isnull(X8.STRGA255,'') as [Long String]
from
EXT00100 X0
-- Checkbox stored in Number table EXT00103
left join EXT00103 X1 on X1.PT_Window_ID = X0.PT_Window_ID and X1.PT_UD_Key = X0.PT_UD_Key and X1.PT_UD_Number = 1
-- Currency stored in Number table EXT00103
left join EXT00103 X2 on X2.PT_Window_ID = X0.PT_Window_ID and X2.PT_UD_Key = X0.PT_UD_Key and X2.PT_UD_Number = 2
-- Date stored in Date table EXT00102
left join EXT00102 X3 on X3.PT_Window_ID = X0.PT_Window_ID and X3.PT_UD_Key = X0.PT_UD_Key and X3.PT_UD_Number = 3
-- List Value stored in Number table EXT00103 with Lookup to List Setup table EXT40102
left join EXT00103 X4
inner join EXT40102 XL4 on XL4.PT_Window_ID = X4.PT_Window_ID and XL4.Field_Number = X4.PT_UD_Number and XL4.LNITMSEQ = X4.TOTAL
on X4.PT_Window_ID = X0.PT_Window_ID and X4.PT_UD_Key = X0.PT_UD_Key and X4.PT_UD_Number = 4
-- Number stored in Number table EXT00103
left join EXT00103 X5 on X5.PT_Window_ID = X0.PT_Window_ID and X5.PT_UD_Key = X0.PT_UD_Key and X5.PT_UD_Number = 5
-- Short String stored in String table EXT00101
left join EXT00101 X6 on X6.PT_Window_ID = X0.PT_Window_ID and X6.PT_UD_Key = X0.PT_UD_Key and X6.PT_UD_Number = 6
-- Time stored in Time table EXT00104
left join EXT00104 X7 on X7.PT_Window_ID = X0.PT_Window_ID and X7.PT_UD_Key = X0.PT_UD_Key and X7.PT_UD_Number = 7
-- Long String stored in String table EXT00101
left join EXT00101 X8 on X8.PT_Window_ID = X0.PT_Window_ID and X8.PT_UD_Key = X0.PT_UD_Key and X8.PT_UD_Number = 8
-- Name of the Extender ID for the window
where X0.PT_Window_ID = 'DETAILS'
GO
GRANT
SELECT , INSERT , DELETE , UPDATE ON [dbo].[MBS_ExtenderDetails] TO [DYNGRP]
GO
select
* from MBS_ExtenderDetails
This is the output from the view based on the data shown in the screenshot above:
SQL View Output
PT_Window_ID Key Field Checkbox Currency Date List Number Short String Time Long String
------------- ------------- --------- --------- ------------------------ ------------ ------------ ----------------------- ------------------------ ----------------------
DETAILS AARONFIT0001 true 10.00000 1968-06-06 00:00:00.000 List Item B 12345.00000 This is a short string 1900-01-01 10:10:10.000 This is a long string
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.
David
