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.
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:
-- Checkbox stored in Number table EXT00103
-- Name of the Extender ID for the window
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:
If this was for a Detail window you would need to change the view to use the Detail tables:
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.
PingBack from http://victoriayudin.com/2008/12/15/reporting-on-extender-data-in-dynamics-gp/
Something I have been asked more than a few times is how to use a SQL view in SmartList Builder to create
Reporting off of Extender is a significant source of foul language in the GP world. Now Mariano is continuing
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.
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?
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.
Is there documentation that explains the data tables and transaction flow of Extender
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.
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."
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.
Posting from The Dynamics GP Blogster
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.*/
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.
As always Dave you are a life (and time) saver. Thank you for this one, why it would not be the method used to create the default view I can only wonder.
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.