Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: Displaying SQL Data with Restrictions Using Extender Enterprise


My customer had interesting request the other day.

He had a SQL Query that he wanted to run, filter the query by a column or two, and he wanted to do this using Extender Enterprise.

That took me off-guard a bit since to me the “correct” tool would be to use SmartList Builder to create the report since SmartList is a reporting tool and SmartList Builder would be able to run almost any query he can design.

The only catch, however, is the restriction that he wanted to add on the fly should be the values from fields on a GP window he wanted to launch it from.

So we discussed it a bit as I explained the features of Extender and how it wasn’t meant to be a reporting tool although my user assured me that he was told that it was. While I don’t doubt that he could have been told this – obviously there was some misinformation being passed somewhere.

The easy answer to this is “can’t be done” since there isn’t a way to display data easily using Extender (that isn’t its function). What I had considered would be to create an Extender detail for and then using the query create a new record in Extender to put all the detail data into. Then just open the Extender form to display the data. Downside of this is that we only get so many columns of data. And of course creating the records directly via SQL correctly.

Instead the better solution would be to create the report in SmartList Builder as I previously mentioned. But how do I then restrict the query with values from my GP window without having to manually add Search Criteria?

So thinking that this might be an interesting puzzled to solve, I poked around in Extender and as I thought, there isn’t a hidden/unused window that we can borrow for our purposes.

After a bit of digging, I thought of two ways to do this using Extender Enterprise and SmartList Builder.

The first way is using SmartList Builder in that it has a Preview window when in SmartList Builder we choose Preview Data.

While the query results do display OK – the Preview window is modal so we can’t see our results and look elsewhere in GP for whatever we might need to do. Also since we are coding this specific criterion directly in the query, we can’t change it on the fly without changing our SQL query. But this was a solution that did work.

So while I was pretty happy to have gotten that piece working, my thought was still to use SmartList to display the data and of course use SmartList Builder to generate the report itself. But how do we filter it without having to do that manually?

To me that implies that we should use DrillDown Builder since I know one of the things that DrillDown Builder can do is create restrictions when it runs GP or SLB SmartLists.

For this example, we will assume that we want to be able to run a SmartList (custom or not) from the Item Quantities Maintenance window and then filter it using the Item & Location.

Figure 1: Target GP window with the restriction parameters

It didn’t take much to create the DrillDown itself. Here I used the out-of-the-box Dynamics GP Sales Line Items SmartList but the destination SmartList doesn’t really matter.

In it, I chose to put restrictions on the Item Number & Location Code fields. You first have to create the Parameters and then map them to the Search Criteria.

 

At this point we can save the newly created DrillDown.

Now that we have a new DrillDown to our SmartList, we need to find a way to invoke it.

As I noted, this customer was using Extender Enterprise and so we could create a new scripting action, otherwise this wouldn’t be possible.

Figure 2: A GP Script action was created to invoke the drilldown

To invoke the DrillDown to the SmartList, I created a new Extender Action of type Dynamics GP Script.

I put it on the Item Quantities Maintenance window and in the script retrieve the Item Number and Site from the UI.

Next, we run the SmartList Builder script named HandleDrillDown of form DrillDownObject and pass it our restriction parameters.

After exiting Dynamics GP and going back into the company in order to register the Action, we open the Item Quantities window.

 

Figure 3: Executing the DrillDown

 

Selecting an Item and Site, we invoke the DrillDown.

SmartList Opens and run the default Sales Line Items SmartList and applies the filter.

 

Figure 4: SmartList Output with specified criteria restriction

We can clearly see the restriction by Item – 128 SDRAM – has happened. But the Location Code isn’t a default column on the report. But trust me, it was applied in the SmartList execution.

For those few doubters, we can run a SQL Profile trace:

Figure 5: SQL Profile Trace of report query showing criteria

We can see that SmartList did apply both criteria to the report as we expected.

You can download both the DrillDown and Extender Action here.

Always interesting finding solutions and unleashing the power of eOne products working together!

Hope everyone finds a use for this, until next time.

 

Patrick Roth
eOne

3 Comments

  1. Karen Tessier on August 29, 2016 at 11:00 am

    Thank you Pat for this great tip! I just used it and it works beautifully. One disappointment though is that it opens SmartList tool, instead of SmartView. Is there any way to have it open the SmartList in SmartView?

  2. Patrick Roth on August 29, 2016 at 11:24 am

    No I doubt that we’d be able to pull that off.

    The reason this works as it does is because we leverage that the DDB code will open a SmartList for us. So the “trick” is to invoke the code to invoke that DrillDown.

    I don’t know of any place in GP that has similar functionality to open SmartView (ie shortcut, drilldown, something to click on) to be able to invoke.

  3. Karen Tessier on September 7, 2016 at 11:44 am

    Thank you for the reply Patrick. Hopefully in the future it will be possible to configure it to open SmartView.

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.