Sign in or start a free trial to avail of this feature.
5. Adding Dynamic Filters
In the penultimate lesson detailing your first Analytical App you will learn how to update interface tools dynamically so that the choices available to the user are not hardcoded but rather determined automatically by the data in the workflow.
- The Listbox tool gives users the option to filter for multiple records
- Lists can be manually added to the Listbox tool, or imported from an external source
- External lists must be in a specific format, where each list item is a field header
In our investigation of the analytical at feature we've set out a simple workflow looking at daily sales in different currencies by product. We've adding flexibility to this workflow by using interface tools to allow the user to toggle the period under review. Our workflow currently filters data by one product, Fabaceae. Wouldn't it be great if the user could specify which product to filter by? In this lesson we're going to add new interactive tools to the workflow allowing users to choose specific products to display through the app.
We'll accomplish this goal through four key steps.
First, we'll connect the Listbox tool to the Filter tool. Next, we'll develop a list of products for a Listbox to pull from. Third, we'll configure the interface tools.
Finally, we'll test the app interface.
For the first step, we'll navigate to the Interface tab and connect a Listbox tool to the Filter tool.
The action tool connected to the Listbox enables us to specify which product we want to filter by. We're now ready to move onto step two and develop a list of products for the Listbox to pull from.
Before doing anything, we need to think ahead. We're going to allow the user to choose to filter by a choice of products but which products should be presented? We could manually enter a hard coded list of products into the action tool but that wouldn't protect us going forward. As we attach records from subsequent sales periods, the list of available products will not include any new lines. What we really want is for the list of products to update dynamically according to the total number of products available. To do this, we need to create a list of products sold during the period and then connect that list to the Listbox tool.
Note that the Listbox tool needs our incoming product list to be presented in a horizontal format with one product per row.
We can accomplish this by using a summarize tool to consolidate our product list so that all products appear once and then use a cross tab tool to pivot the list into a row. We'll need to start by connecting a summarize tool to the date filter tool creating a new branch for our workflow. In the Configuration Window, we'll choose to group by product. However, we also need to include a second field so that we can incorporate a cross tab tool downstream. The date field is a convenient field for this purpose. We'll choose to count the date field. We'll run the workflow, and see that we now have a list of products presented vertically together with a number of different sales entries for each product during the period. We'll now connect a cross tab tool. In the Configuration Window, we'll set the column header as product, values as count, aggregate by sum and run the workflow. Our list of products is now presented in a horizontal manner.
We can now run a connection from the cross tab tool to the queue input icon of the Listbox.
This will bring the entire list of products contained in the daily sales file into the Listbox dropdown.
We're now ready to move onto step three and configure our Listbox and action tools. We'll begin by going to the filter tool and changing our settings. Rather than using the basic configurations, we'll use a custom expression.
We want the filter tool to return all products listed rather than just the single product it's returning now.
For example, if there were two products, the formula would read product in fabaceae, Asterceae.
Remember, the Listbox is going to toggle these fields according to the user's selection, so the text is just a placeholder for now.
We want the Listbox to replace the second half of this formula, the one that contains the product names. We'll navigate to the Listbox tool, and select generate custom list.
Next, we need to specify the start text, separator, and end text in the boxes provided.
We'll enter an open parenthesis and a single quote, a comma surrounded by quotes, and a single quote followed by a closed parenthesis.
These items represent the start text, separator, and end text in the product section of our filter formula.
We now have a dynamic list of products flowing into the Listbox tool, so we can simply choose all field types in the properties section.
We'll now head to the action tool and see that the filter lists the expression value.
We can highlight this indicating that it's the field we wish to change. We must then tick the box to replace this specific string to specify the text included in our filter tool. We'll also need to remove the formula section of the string since we only want the action tool to replace the values. At this point, we'll move onto step four and test the app.
We'll navigate to the Interface Designer, go to the test view and see that it would be helpful to give the text box tool instructions.
We'll click on layout view, select the Listbox section, and in the Configuration Window, enter choose products as our directions. We'll click on the magic wand icon, and see that we've given the user the ability to choose a range of dates and then specify the products they wish to list for the sales report. Let's quickly recap the lesson.
First, we connected a Listbox tool to the filter tool. Next, we developed a dynamic list of products for our Listbox to pull from that automatically updates with the dataset.
Third, we configured the interface tools to allow users to pick specific products to filter by.
Finally, we tested our app in the Interface Designer. In this lesson we've added a dynamic product list filter to our workflow and increased its flexibility.
However, what if the user instead wanted to filter by percentage of total sales rather than product name? We'll add this further functionality in the next lesson.