December 15, 2008 
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.
Victoria Yudin 
Hi Victoria,
I make use of calculated fields in my extender form, one fetches the SYSTEM_USER and the other DATETIME.
My Extender view does not seem to add these field to the available list of fields for the specific form. Is there anyway to add these?
Should I update the view in SQL?
You advice greatly appreciated.
Kind regards
Nico
LikeLike
Hi Nico,
If the Extender created view is not capturing the calculated fields you will have to add them to the view manually.
My 2 cents on the views created by Extender – they are nice for people that do not know who to code in SQL. However, they are ‘generic’ by their design and so if you do know how to code this in SQL, you may be better off doing that yourself. You will often end up with less complicated views.
-Victoria
LikeLike
Victoria, is this only available in GP10 SP3 and higher? We’re in SP2 and I don’t see “views” as one of the options in the extender objects window.
LikeLike
Joe,
This will only be available is you are logged into GP as ‘sa’. It has been available since at least GP 9.0. Before that the memory starts getting a little fuzzy. 🙂
-Victoria
LikeLike
Geeze – I really seem to be having a hard time following directions today! Thanks for the fast response and great article. Incredibly useful as always!
LikeLike
I appreciate the information on your blog.
Do you have a list of eOne’s eXtender tables?
LikeLike
Sorry Howard, I do not. Hopefully that is something eOne can provide for you.
-Victoria
LikeLike
Hi Vic
I need to extend the field size of categories in the inventory control setup.I cannot seem to find the table description in the item master table .Will extender or modifier work better with what i am trying to achieve .I need to report on item categories as well as sub categories example item 1 = Drilling machine .Category = power tool machinery and the sub category will be impact drills .
Thanks
Logan
LikeLike
Hi Logan,
I would not advise changing any fields in the out of the box tables in GP. This can lead to many issues down the road, including not being able to perform updates/upgrades and losing the data stored in changed fields. If you need to track additional information about your inventory items, Extender is a great option.
-Victoria
LikeLike
Hi Victoria,
Thank you for writing such nice articles. I have issue where I hope you can solve. I created a extender window and have made sure that all of our staff can see it but due to some reason some of our staff are not able to see the extender window, niether by the hot keys nor by the Extras>Additional. It is greyed out for some of the staff.
Kindly advise.
LikeLike
Hi Zafar,
Have you confirmed whether it’s an issue with GP security (so logging in as ‘sa’ or a power user on the same computer works, but logging in as another user does not) or with the computer/GP install (so logging in as ‘sa’ or a power user also does not work)?
-Victoria
LikeLike
I followed the steps above and I was able to create and view the smartlist report, however on the blank fields I am getting this garbled data: aµ. Do you know why? I noticed that this data comes up on the fields assigned as short or long string in the extender window.
Also I was advised to not use the smartlist button in the extender window because it leaves behind unwanted data in the db and Microsoft does not have a fix for this yet. Is this still the case?
LikeLike
Dean,
This is something that got broken in GP 10.0. Basically the issue is that SmartList in GP 10.0 does not know how to handle NULL values in the results. So it shows them as the garbled characters you are seeing. This will be the case for any SQL view you create, not just a view created from Extender.
If you were creating a view with your Extender window only, this would not be an issue. However, as soon as you link to a another table, like Customer Master, if there is not a corresponding Extender record for each customer, you are going to get NULLs in the SQL results and SmartList does what you’re seeing.
Unfortunately, the only way I know of to fix this is to alter the view in SQL to account for NULLs. I still think it’s better than writing the view from scratch, but it does add an additional step to this.
For your other question, I suppose if you had created an Extender Window, added it to a SmartList, then deleted that Extender Window, there could be some remnant data that you may not want in the database, however, this should be a very unlikely event, as Extender Windows should not just be added and deleted at will, there should be more planning and testing (in a separate environment) involved. And this extra data in most cases should not be hurting anyone. So, all things considered, I would opt for more functionality.
-Victoria
LikeLike
Dean,
Here is an update on this from Microsoft: this is a known issue (quality report 54219), but no timing on resolution yet. One additional way to get around this: in SmartList Builder create a calculated column for each field that is coming in with garbled data. In the formula for the calculated field, just point to the original field. Hide the original field and show the calculated field. Now you will see blanks instead of garbled data.
Not sure if this is better that altering the view, that might depend on how comfortable you are with SQL. Also not sure how/if having a lot of calculated fields will affect performance of the SmartList for large data sets.
-Victoria
LikeLike
Hi Victoria, a day after your reply I tried the calculated field but still got the garbled data. I supposed you meant that the value of this calculated field simply equal the value of the original field, right? I decided to add this additional instruction: +” ” to include an additional space to the calculated field and it did the trick. Thanks for pointing me the right direction. Your such a great help to the GP community.
LikeLike
Dean,
Awesome! Thanks for following up and letting me know how you resolved this.
-Victoria
LikeLike
Hi
I have an unrelated question about extender – I’ve just been playing around with it – one of the things I see as an issue is:
I setup additional notes – but a client using GP wouldn’t know the notes are there unless they click on the additional menu. is there any way to have something display on the actual window to show that there are additional notes?
thanks!
LikeLike
Vic,
Very good idea – I sometimes forget where we have notes added, so a visual cue would be great. Nothing I am aware of in Extender will have this feature. You could potentially add a window level note (available on just about every GP window) to let users know about the notes, but that seems a bit counter-intuitive. I think the only options to add any visual cue is with Modifier or a Dexterity customization.
-Victoria
LikeLike
Great article, and a good, thorough example. I like your approach.
LikeLike
Another good topic and a simple/complete narration. I considered this as a nightmare before I read this article, but not anymore.
Thanks
Vaidy
LikeLike