Dynamics GP Extender is one of those modules that has so much in it, most people don’t use even half of it. One of the most underused features is Extender Views. Anyone that has worked on reports using data entered into Extender windows will tell you that getting data directly out of Extender tables is a challenge. All dates will be stored in one table, all strings in another, all integers in another… This is where Extender Views can really help.
Extender Views will automatically create a SQL view from your Extender windows and even let you link in other GP tables. These views make reporting on Extender data significantly easier. Below are the detailed steps to create an Extender View. For this example I am using Dynamics GP 10.0 SP 3 and a pre-existing Extender window called Customer Additional Information which links to the Customer Maintenance window on key field Customer Number. To illustrate, I will create a view with all the fields on the Extender Window and a few fields from the Customer Master table. You can click on the screen shots to see them larger/clearer.
Log into Dynamics GP as ‘sa’ and open Extender (Microsoft Dynamics GP > Tools > Extender > Extender). Click on Views in the list of Extender Objects on the left, click New. This will open the Extender Views window:
Choose an ID, Description and SQL Name for the View. I typically like to use the same ID and Description as the Extender window. The SQL Name is what you’ll see in SQL and use for reporting, so make it something easily identifiable. Maybe even put EXT in there, so you know this came from Extender:
The next step is adding a primary table. Since we’re only planning on two ‘tables’ it really doesn’t matter which one we add as the primary, but I typically make the GP table the primary one. Click Add, choose Microsoft Dynamics GP for Type, Microsoft Dynamics GP for Product, Sales for Series, RM Customer MSTR for Table:
Click Add, this will bring you back to the Extender Views window where you can decide what fields from the Customer Master table you want to add to your view. While it may be tempting to click Mark All, this is not a great idea as there are over 100 fields in this particular table, most of which you do not need. In case you’re looking for them, Extender automatically strips out the DEX_ROW_ID and DEX_ROW_TS fields.
To add the fields from the Customer Additional Information window click Add, choose Extender Window under Link to Type and choose you Extender window under Window ID. (Note: even though it’s called Window ID, the list is actually of Extender window descriptions, which is more user friendly.)
To define the link between the Customer Master table and the Extender window, click on the + next to Link Fields and choose Customer Number for both the From Field and To Field, then click Add:
Click Add one more time to get back to the Extender Views window which will now have your Extender window linked in under the Customer Master table. Click on the Extender window description on the left to see the list of fields on the right, then Mark All to add all the fields to your view. In this case, since I already added the Customer Number from the Customer Master table, I will uncheck it so that I don’t have it being brought in twice:
There are 2 Options that you may want to look at before finalizing your view. Click on the Options button at the top to see these:
By default both of these options are checked, here is what they mean:
Save Lists and Checkboxes as Strings: If you have a checkbox, having this option checked will show ‘Yes’ and ‘No’ as values in the view. Having this option unchecked will show these as 1 and 0. For lists, having this option checked will return the actual list value, having the option unchecked will return the numerical position of the value in the list. Which is typically not useful whatsoever. My recommendation: if you only have checkboxes – do whatever you prefer. If you have lists, leave this checked.
Use Display Names in View: This sounded really good to me when I first saw it, I thought I would get all the fields with nice names. However, the big caveat to this is that it only applies to the Extender fields, not any other tables. If checked, this will return the Extender column names as they actually appear on your Extender window, so in my example above, Billing Preference will create a column called ‘Billing Preference’. With this option unchecked, Billing Preference will create a column called ‘CUST_INFO_BillingPreference’ with the spaces stripped out and the Extender window ID added. My recommendation: both of these have their uses and it depends what you will be using your view for. If you are going to be creating a SmartList in SmartList Builder, check this option, as it will mean you don’t have to rename these fields once you bring them into a SmartList. If you are going to be using this for Crystal Reports, uncheck this option, as Crystal is very finicky about spaces in field names.
Once you’ve brought in all your fields and decided on your options, click Save to create the SQL view. As soon as you’ve clicked Save, you will see the view appear in SQL under the lists of views for your database. Remember, Extender is GP company/database specific, so if you have the same Extender windows in multiple companies you will have to copy these views. (You can do this in SQL by creating a script for the view and just running it against your other databases.)
There is a Preview button on the Extender Views window – you can see what your columns and data will look like before you exit Extender. And finally, if you ever need to add to or change these views, go back to Extender Views, make your changes and click Save. The changes will be immediate, so be careful with making changes if you already have reports based on these views.
Update (03.10.2009): David Musgrave has a great post on the limitations of creating Extender Views using this built in functionality and some alternatives for creating your own views for Extender data in his Creating SQL Views of Extender Data blog post.