How to create a search function using Excel filters
For a detailed look at how to work with external data sources, watch this webinar recording. In it we pay particular attention to Excel.
Imagine you have already created an Excel interface asset for a spreadsheet comprised of two columns. Your objective is to take user input, search Column 1 for the row containing that input and then display onscreen the value of Column 2 for that same row.
On a Text Input control, add the following trigger/action pair:
- Trigger: WHEN [select your Text Input control] -> "Text Input" -> "Enter key is pressed"
- Action: THEN [select your Excel interface asset] -> "Excel"
-> "Add filter"
- For "Columns", select the column you want to search
- For "Filter", select Contains (case insensitive) to broaden your search
- For "Value", bind the "Text" value of the Text Input asset.
- Check "Clear column filters", a good best practice to ensure no preexisting filters remain applied
The following image illustrates the above example. Click to
- If you need an input reactive search,
meaning when you start typing, automatically start searching, you
need to change the "Enter key is pressed" trigger as
- Trigger: WHEN [select your Text Input control] -> "Text Input" -> "Text is updated"
The Excel interface asset exposes a Number of rows property. When this number is equal to 0, meaning you have no results in your search, you may want to display a specific message.
You can use the Is Equal To converter to bind the visibility of the Text message to this Number of Rows.
To see the theory in action, download a sample experience.