Creating SQL Views of Extender Data

David Musgrave

5 Feb 2009 8:00 PM

Comments 13

David Meego 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:

Extender Details Window

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

Attachment:

SQL Extender View Demo.zip

Extender, SQL, SmartList Builder, SQL Reporting Services, Crystal Reports

Comments

Reporting on Extender data in Dynamics GP « Victoria Yudin

10 Mar 2009 10:29 AM

PingBack from http://victoriayudin.com/2008/12/15/reporting-on-extender-data-in-dynamics-gp/
Victoria Yudin

20 Apr 2009 1:29 PM

Something I have been asked more than a few times is how to use a SQL view in SmartList Builder to create
DynamicAccounting.net

26 Apr 2009 10:34 AM

Reporting off of Extender is a significant source of foul language in the GP world. Now Mariano is continuing
Jason

12 May 2009 2:18 PM

Anyone report off of non-company databases without creating every GP user in that company? We have a custom database that I want to access through smartlist builder but can't because of the user I am logged in as in GP isn't available in the target DB.

Thanks,

Jason
Shelley Williams

28 May 2009 2:33 PM

Is there any way to create a Go To that will open this Details window directly, without having to open the Customer Maintenance window first?

Thank you,

Shelley Williams
Patrick Roth

28 May 2009 11:24 PM

Shelley,

It doesn't look like it. While we don't own Extender (it is OEM'd) and don't have source for it, you can tell a lot from a script.log and about 12 years experience using Dexterity.

But looking at the script.log, the extender window itself is what is pulling the key values from the window. That is unfortunate since if it was passed the key values then this would have worked. But since it looks towards the real window, it isn't open and therefore doesn't know what to get.

Indeed, when I tried this my key values showed as "0" instead of the blank strings I expected. No errors, just didn't work.

patrick
Daniel

28 Jul 2009 5:16 PM

Is there documentation that explains the data tables and transaction flow of Extender

Thanks

Dan
David Musgrave

29 Jul 2009 3:41 AM

Hi Dan

From what I can see Extender Tables not covered in the SDK.

So you would need to look at the Table Resources functionality built into the Dynamics GP application for information on the tables.

David
Sheila

8 Oct 2009 2:09 PM

I don't see that anyone ever answered Jason. Is this possible? I'm trying to do the same thing.

"Anyone report off of non-company databases without creating every GP user in that company? We have a custom database that I want to access through smartlist builder but can't because of the user I am logged in as in GP isn't available in the target DB."
Patrick Roth

8 Oct 2009 2:51 PM

Sheila/Jason,

Obviously you cannot query a table in a database if the user doesn't have SQL access to that database.

From a SQL view perspective, I believe that the user would need access to that other db even if the view originated in a company they did have access to.

I don't think that is the case for a stored procedure however but it doesn't matter because the question was in regards to SLB which cannot execute procs.
David Musgrave

14 Jan 2010 5:19 PM

Posting from The Dynamics GP Blogster

http://dynamicsgpblogster.blogspot.com/2010/01/parsing-long-string-fields-in-extender.html
Brenda Neville

14 Apr 2011 8:56 PM

I get this error in my Dexsql when opening smart lists.

It is opening very slow

/* Date: 12/27/2010 Time: 11:52:15

SWSTATE:(SW041) Native Err:(41):*/

Number of results columns doesnt match table definition.*/

any thoughts??
Patrick Roth

15 Apr 2011 8:03 PM

Brenda,

You don't show which table - but this error typically means that you have at at least one table that is updated to the version of your dictionary. Or you have an upgraded table and an old dictionary.

patrick

Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Dates of Interest

Social Networking & Syndication

Shortened URLs

Disclaimer

Creating SQL Views of Extender Data

Creating SQL Views of Extender Data