Tis the season to be sharing, and so your Best Practices Team is sharing a recent GridView tip that we discovered and are now using in many of our GridView Workspaces. We think it’s too good to wait until AOC for sharing, so merry, merry!

Our tip involves establishing global parameter prompts that prompt one time only and then can be used thoughout the rest of the views on the workspace. This concept replaces the repetitive same query prompts in multiple views, and can be used in filters or calculations for data ranges, cutoff date(s), certain items codes and/or categories, vendors or customers.

I suppose this is comparable to carrying prompted parameters or variables between subreports and main reports in Crystal Reports for Adagio, if you are familiar with Crystal.

A recent example of when we used this feature, was for a client request to have a query to show a rolling vendor history comparison, of current year to previous year purchases. We needed the same date range queries on both current and historical transaction tables. Another example was for our WIP calculator functions in Job Costing where the same cutoff date was necessary to filter multiple tables that required separate subtotaling views.

This tip became viable in the recent GridView updates and uses a string query called GETLINKEDVALUE. The actual syntax are in the Gridview Help – Function List – Query Functions.

To use, create a very simple view called “Prompts.GV” that connects to the company profile and just select the company name. Then add a calculated column “LINK”, with a text string formula of just say “L2”. You could use anything, such as “X” but Link2(L2)is quite common in our office for linking.

In this view, LINK becomes the common column link from this single line table (view) to any view that needs a common prompted value from this Prompts.GV view. (You can have as many separate and distinct prompts as you need.) Next, add a calculated column to define the prompt, such as a “Cutoff Date”, using the QueryPrompt command. This view definition gets saved as an autorefresh on open, save without prompt, and is first entry on the recalc list, meaning that this view will be the first view to open and enter your prompt(s) to be used in all of the views without another prompt.

This means all prompts are maintained in a single view for ease of support, and you have all prompts on a single line table (for reference) that makes use of the GETLINKEDVALUE (in other views). This view can be minimized to save viewing landscape space.

Then for any view that requires a prompt in a filter or a calculated formula, you just use add a calculated column in that view called LINK, with the same text value of “L2” (which you can hide). This defines the link (back to the prompts), and define the link formula as GETLINKEDVALUE("Prompts", "LINK", "Cutoff Date"), where Prompts is the name of the view where you store all of the global prompts, LINK is the name of the column in any linked view, and Cutoff Date is the specific column name of the prompt (or any prompt) that you store in the Prompt view. As a rule, we usually just create a LINK column on every view because this is so effective.

This use of global parameters means that you can eliminate situations where the data is being viewed based upon different (unrefreshed) parameters or duplicate data entry resulting in the wrong prompts selected for a particular view. Don't you just hate comparing data views with the with mismatched data filters!

This means you have many more opportunities to effectively use GridView, and you are more efficient with your time creating and testing views. For us, it has opened up endless opportunities to add value to our clients’ sites using GridView as a management query into their Adagio data. We are also going back to revise and replace existing views that didn't consider this feature. It’s about working smarter, not harder!

And speaking of more efficiency opportunities, check out our new link2systems.com website for more working ideas to link up Adagio modules to add value to your Adagio business solutions. For more details on Link2Systems software modules, please contact your Adagio consultant.

Merry Christmas and Happy Hanukkah!

Brian

Hi Brian:
Thanks for the excellent tip. A creative outside-of-the-box idea, as usual!

Brian,

I don't know if I fully understand what you are doing with the new links, but it seems that I could use if for a workspace I am developing to study daily, monthly and year to date category sales. Each view has a different filter.

We will see, let me dwell on it for a bit.

Happy Kawanzaa,

RES

Hi Brian

This is brilliant. But isn't there already a way to have the workspace remember the query prompts from one view to another?

Thanks for sharing this with us.

Steve

QUERYxxx() takes a parameter to only ask the question "once".

Having a View into a company profile, that contains only a single record, storing the results of the QUERYxxx() into a field in the View and then picking up the value using the GETLINKEDVALUE() function means that the question only gets asked once for the entire Workspace.