FACT: eXtender views have been a core part of all versions of eXtender since January 2003.
FACT: eXtender views are critical to most successful eXtender implementations.
FACT: eXtender views are the best kept secret since the location of the Holy Grail.
I am including below an extract from an email exchange I had with an eXtender customer earlier this week:
GP Customer: “The current extender architecture makes it difficult to collect data from them and use them in other systems”.
Martin: “First off the data in eXtender is NOT hard to get at. Use the eXtender view function which will create a SQL view for you – and you would reference all the data direct from the view. It is really easy.
GP Customer: “Wow, the views are great, thanks for pointing them out! That greatly simplified the data retrieval and has revived our faith in extender.”
This scenario above and the frustration of a GP customer need never have occurred. This blog post is not going to be a detailed training session but I want to address a few related topics here:
1. Why consultants have missed the view functionality: Dynamics GP has an amazing group of consultants who are really great at what they do. Since about V4 of GP when a customer asked for a report to be written these consultants would open Resource Descriptions within GP and take a look direct into SQL to identify which tables and fields they needed to pull onto a report. This is the correct thing to do and consultants do it well. Unfortunately this process does not work with eXtender as a result of the ‘unlimited field’ structure of eXtender.
2. Why does eXtender have such whacky tables: eXtender is great in that you can create an unlimited number of user defined fields and have them located on windows, forms, detailed forms, linked forms etc without any thought about the backend table structure. That is right unlimited. So this means that there is not a big empty table waiting for you to create some fields or that eXtender creates new tables for each of your creations. If we did either of these you would wither not have unlimited fields or you would really struggle to upgrade to the next version of GP
So what eXtender does is write all the data you capture into a series of eXtender tables. A separate table is used for strings, numbers, dates, and time. In each table eXtender stores the window or form name, the field position and the field value. This table has only a few columns but plenty of rows over time.
3: Is it possible to write reports direct from the eXtender tables? Yes it is but it is way too hard. All power to those that have achieved it but you are both much smarter than I am and much more patient.
4: To access eXtender data what should we do? Create an eXtender view
5: So what is an eXtender view? We have done all the hard work for you. An eXtender view creates a real SQL view, which extracts all the data from the table structure described above and presents it neatly to you for reporting and other data access needs. You can then read this data, report on it with any reporting tool, use it for integrations etc.
So to conclude, Is it hard to report on eXtender data? No it is easy.