Why we love Extender (Part 2) – Extender Views

I was at GPUG Summit a couple of years ago, and was sitting in a great session about Excel Report Builder being presented by eOne Solutions. During the session, a woman asked a question and in describing her situation she mentioned something about building a SQL query to pull some Extender data. Before answering the real question she was asking, the presenter asked her “why would you do that, why wouldn’t you just use an Extender View?”

I quickly wrote a note to myself to go find out what an Extender View was. I had been using Extender for a few years at that point and whenever I needed to include Extender data in a report or Smartlist I would go through the process of figuring out which EXT tables I needed, and re-joining them to make sure I got all of the fields.

Extender Views provide us an interface to easily build SQL Views against Extender Forms and Windows as well as any other Dynamics GP table.

Extender Views

To build an Extender View, click on New (or to edit one, select it from the Extender Resources pane and click on Open). You should get the following window:

Extender View Window

Populate the View ID, Description, and SQL Name fields. The SQL Name will be the name of the SQL View that gets created. This is the name you will use from your reporting or integration tool.

To add a table to your view, click on the Add button.

Adding Primary Table

Extender allows you to create a view that includes either Dynamics GP tables or Extender Windows, Forms, Detail Windows , or Detail Forms. Or all of the above. If you select Dynamics GP Table, you’ll need to select the Product, Series, and Table from the drop down lists:

Add a Dynamics GP Table

HINT: If you ever need help finding Dynamics GP Tables, check out Victoria Yudin’s blog.

For our project, we just needed to create a view against an Extender Form we had created:

Using an Extender Form

Click on Add to return to the Extender View Window. If you click on Add again, the Add Table window looks a little different:

Adding a Secondary Table

You’ll need to select the Parent Table to link to before you select the Secondary table. Click on the + to configure the field(s) to join on.

Once you have selected the table(s), you are ready to start selecting columns for your view:

Selecting Columns

The Fields pane on the right shows all of the fields available for the Table currently selected in the Tables pane. Simply click the checkbox next to the field to add it as a column in your view.

When your done adding columns, click on the Options button.

Options

In case you don’t already know, when the user selects a value from a List data type Extender actually stores a number referring to the index of the selected value. It also stores a 1 or 0 for Checkboxes. Save Lists and Checkboxes as Strings saves the user from having to remember what the numbers mean.

I prefer to Use Display Names in View, so that it is easier to identify the data in the columns.

Click save to return to the Extender View window.

If you want to see how your view is going to look, click on the Preview button:

Extender View Preview

Once you click on Save, your view is created in the Dynamics GP Company database. You can now use it anywhere that SQL Views can be used:

SQL Server Management Studio

As shown here, you can create and use SQL Server views without knowing anything about SQL by using Extender Views. These views can be used in SmartList/Excel Report Builder, SSRS, Excel, or any other tool that can read a SQL Server view.

About Briware Solutions

Based in the Greater Toronto Area, Briware Solutions provides enterprise level consulting services to growing companies throughout Ontario.  With a focus on improving the way Dynamics GP fits into business processes, Briware Solutions services customers in multiple industries including television, utilities, manufacturing, and distribution.

www.briwaresolutions.com

The post Why we love Extender (Part 2) – Extender Views appeared first on goERPcloud.