How to create a search function using Excel filters

    Overview

    With IntuiFace, an Excel spreadsheet can be used as a searchable data source. To do so, apply filtering actions to any Excel interface asset and display the contents of the now filtered spreadsheet.

    For a detailed look at how to work with external data sources, watch this webinar recording. In it we pay particular attention to Excel.

    Example

    Filtering mechanism

    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 enlarge.
    ti-Actions-Excel_Filter.jpg

    • 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 follows:
      • Trigger: WHEN [select your Text Input control] -> "Text Input" -> "Text is updated"


    Finally, display the value of the second column using a data template. For more about the creation of data templates, see this article.

    Display a message if there are no results

    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.

    SearchNoResult.gif

    Download sample

    To see the theory in action, download a sample experience.