Search & Filter LocalDB


:question: Searching & Filtering

This tutorial will focus on working with data that is stored in the LocalDB, specifically searching and filtering it.
You can type in a search term to drill down into your data, or you can click on different buttons to filter by specific categories.

:information_source: Raw Data

We have two columns and 10 rows of data. Note that there are two different types of fruit in this example that we will use for sorting later on.


:recycle: Remix this app

If you want to remix this app for yourself, click here

:mag: Searching

For a more thorough explanation of how to add search functionality, take a look at Jane’s post here:

When the app loads we set everything up with two variables. listOfFruit gets data from the LocalDB and creating a separate listToSearch will make things easier for us when we start filtering data later on.

In the next part we create a function called searchList to do partial matches for what the user is searching for. The two UPPER CASE blocks here make searching case insensitive.

The ListViewer is then updates with all the matches that are found - if you wanted to be more thorough here then you could include a test where a message like “No results found” is displayed of the temp list is empty.

The simplest way to call this function is by clicking on the search button…

…but it’s a lot more useful if it happens “automagically”. To do this we create two counters, every time the length of the search string changes (i.e gets longer or gets shorter) our searchList function is called.

:file_cabinet: Filter

What if we only want the apples? Or just the grapes? What about resetting everything back to the way it started? To do this we need to create a simple filter.

We start by creating a variable, called index in the picture below, to keep track of where we are in the listOfFruit. While it's not strictly necessary to have separate lists for the grapes and apples (in the pictures I just usedtemp` for everything), you can do this if you find it easier to understand.

When filtering, we look down the first column (either apple or grape) and if it matches what we’re looking for we add the adjacent value (the specific variety) to our list. By having the index variable we can look up the value in the next cell. See more in the VLOOKUP example.

Filtering out the grapes only is the exact same idea, in fact this could all be replaced with a function if you wanted to make it more efficient/extensible. Again, I created a listOfGrapes but opted to use temp instead.

Finally, the reset button is very easy to implement:

Making a Search Bar
Spreadsheets connected to FREE databases (airtable is not free)
How do I use the index rownumber in Airtable?
Searching in list
On change event input field

This example does not work correctly.

Look at the block.


The “searchlist” block should be used after the “set app prevCharCount to app currentCharCount” block. Otherwise, the “searchList” function is repeatedly called and the “empty list” block does not clear the temporary list.

Making a Search Bar