Data grids integrated with dropdown filters make for a popular combination in many Nitro App Builder-based business apps. A typical grid configuration may include a dizzying collection of columns and rows — all easily tamed with a filter toolbar situated directly above the header row, making it simple for users to winnow the records down to something more pertinent.
In some cases, the value selected in one dropdown filter would naturally make certain values non-applicable in another filter. For instance, say you have one dropdown filter in which users can specify a country constraint, with an adjacent filter for also specifying a state/province constraint. In such an arrangement, if the user were to select “Canada” for the country, it would make no sense to be able to specify “Texas” in the state/province filter.
For such scenarios, a dependent filter setup can be helpful, wherein you limit the contents of one filter based on the selection made in another. This is accomplished through a combination of vvIn_virtual in the dropdown filter data source and a clever app variable expression.
Let’s quickly demonstrate how this can over very simple grid app using our favorite fake customer master table DEMOCMAST, which includes both a country column (CCOUNTRY) and a state/province column (CSTATE). Our goal is to setup dropdown filters for these two columns, where the selected country will automatically limit the contents of the adjacent state/province column accordingly. We also want the state/province to revert back to listing all possibilities whenever the Reset button is hit or no country is selected.
STEP 1: Create data sources for the country and state/province filters
The data source feeding a list of values to the country dropdown is quite simple:
select distinct CCOUNTRY
from DEMOCMAST
order by CCOUNTRY
Now, to get the state/province dropdown working as described, we’ll create a data source that is limited by any specified country through the use of a vvIn_virtual reference. Recall that the value of a vvIn_virtual clause can be set through app variables, adjusted by either the user interface front-end or a back-end RPG program.
So we’ll include in this second data source a where clause with a virtual column called filterCountry:
select distinct CSTATE
from DEMOCMAST
where vvIn_virtual('filterCountry', ' ', 'char', 20, NULL, 'true')
in (' ', CCOUNTRY)
order by CSTATE
This statement will pull all distinct states in DEMOCMAST whenever filterCountry evaluates to blank; otherwise it will be limited to records where CCOUNTRY matches the filterCountry value.
STEP 2: Create filters on the grid widget using the new data sources
With the data sources created, the next step is fairly straightforward: Add two filters to the grid widget’s Filters tab — one for CCOUNTRY, one for CSTATE — and transform both of them into dropdowns that pull their lists from the appropriate data sources.
Note that you may also want to consider unchecking the Autoload property in the Configure > Data tab of the grid widget, if you want to require the users to specify some sort of filter before anything is displayed. We have it unchecked for the sample screenshots that follow, hence the grid shows no results in the Designer.
STEP 3: Create app variables to pull in one filter value and signal a reload of the other
Now we move on to where everything comes together at the App level. We first need to create a couple app variables: One to pull in the selected value on the Country filter, and another to signal the State/Province filter to reload itself whenever the Country value changes.
So go into the App Variables list, which is accessed via the App Variables button in the upper left:
Then add one app variable called “filterCountry”, and another app variable called “countryFilterChanged”. We will link these up to grid widget functions in a moment.
And now we come to the key point of this tip… We want the “countryFilterChanged” to be set to true when applicable in order to tell the State/Province filter it needs to be reloaded. To do this we will exploit a key nature of app variable expressions: whenever an expression includes references to another app variable, any change detected in that app variable’s value will automatically force the expression to be reevaluated.
So, clicking inside the Expression cell for countryFilterChanged brings up an expression editor…
… and in that editor we will enter the following JavaScript code:
This statement is merely pulling in the filterCountry app variable (which we will be linking to the Country dropdown in a moment), and then returning ‘true’ unless the app variable is undefined, which should never really happen in our simple filter configuration.
Note that after hitting Save in the expression editor, you will see a shorthand representation of your code inside the Expression cell, wherein line breaks are removed and appVar.filterCountry is shortened to {[filterCountry]}:
STEP 4: Link the app variables to the appropriate grid elements
That last step is to tie (link) our two new app variables to grid actions. So, returning to the main view of the app, hover your mouse over the grid widget and click on Link…
Click on the “Get Filters” category and associate the “filterCountry” app variable with the CCOUNTRY filter dropdown, so whenever the user selects a country this app variable will be updated (which, in turn, will cause the “countryFilterChanged” app variable expression to be reevaluated):
Finally, click on the Reload Filters and link it to the “countryFilterChanged” app variable to force a reload of the State/Province data source:
And that’s the final step! Save the app and you’ll see that a change in the Country dropdown is now limiting the available values in the State/Province dropdown…
