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