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.

58

: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.
52

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…
36

…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.
57

: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 used temp` 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:
14

5 Likes

This example does not work correctly.

Look at the block.

%D0%B8%D0%B7%D0%BE%D0%B1%D1%80%D0%B0%D0%B6%D0%B5%D0%BD%D0%B8%D0%B5

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.

1 Like

This system still working well? i try begin but app are crashing…
I dont know if are better open another topic or edit this message and put what i like to do…

@domhnallohanlon

2 Likes

With Fiberbase ?
The same thing or anything different??
Thanks

1 Like

Hello! Did you get a feedback if this ‘system’ still working?

1 Like

Hey @Canteri,

I haven’t tested this app in a while - is everything working as expected for you?

Hello, sorry for the delay.
No, actually it is not working for me.

2 Likes

Wow, so great of you, any hope of a tutorial of images in local db? Really waiting for this!!!

it worked for me but i am try to change from local db to airtable.

it seem block not same like local db. or is there any way to from airtable save to localdb? i read somewhere but not found it in forum.

@ozel1978 just save the response from Airtable to a variable and use the loop to iterate through the variable instead.

Dom,

you mean to save to variable, something like this?

Dom,

if i can get example in block for that.

A post was split to a new topic: Can we search and filter the Data Viewer List?

How to ignore upper case and lower case? the block on text block section are not ignoring upper case sensitive… so sometime items on the list are not showing even there are an item it just on a upper or lower case

1 Like

There’s a block in the Text drawer called to UPPERCASE and you can set it to lowercase as well:

1 Like

what i mean is ignore any case regardless it is a uppercase or lowercase. Because if i will use any of those 3 option either UPPER, lower or Title case still doesn’t ignore


Imagine youre having a list of locations, and you want to search and filter even the suburb,street names and city, not every thing there is titlecase, some are lower case and upper case if the area is an acronym… it doesnt ignore, it just filter what the conditions type on the textbox either start with uppercase or lower case, how about if theres an item on the list but didnt show up while filtering becuase the user type an UPPER case but the item is in a lowercase?

1 Like

Right but you have to make sure the data you are searching and the input/search key is in the same sentence case. So it doesn’t matter if some of it is UPPER case or lowercase or Title case as long as you convert everything to the same case (I recommend lowercase) and then compare.

2 Likes

its like convert all list into lower case, like same format for the list and same textbox input right?

1 Like

Yes, so the screenshot you provided should be does to lowercase app variable tempobject2 contain to lowercase app variable searchstring2

Making both sides to the same “case”.

Yeah, that’s basically making it case-insensitive, because you are never sure of what case people are writing in.