One of the most powerful features in Thunkable is the new LocalDB component, which allows you to insert a mini-spreadsheet directly into your app. This might be the first of a larger series of tutorials mimicking features found in other spreadsheets, but for now let’s get started with VLOOKUP.
What is VLOOKUP?
A VLOOKUP, or vertical lookup, allows you to search vertically down through a column of data and then find a corresponding value in your target row
Design
Let’s start by adding a ListViewer with three items:
Next, in our LocalDB, we’ll create a 12 x 2 spreadsheet. Note that the “numbers” column has the same data as our ListViewer
Blocks
In our blocks, the “sublist” variable will contain the selection of letters we want from the larger list of letters. I’ve also created an “index” variable to make it easier to keep track of where we are in our loop. NOTE: The index variable should be reset to 0. I’ve amended this in the remix-able project below
When the user clicks on an item in the the ListView (which will be either “one”, “two”, or “three”) we start by clearing out the sublist, and resetting the index:
The next thing we want to do is set up our loop, moving vertically through the data in the “numbers” column. After each pass through the loop we manually increment the “index” variable and when we reach the end of the loop we display the sublist in a label
Finally, we need to check whether or not a letter should be added to the sublist. To do this we compare the ListViewer item (called item) to the Loop item (called j). Now, using our index variable we can fetch the corresponding value from the numbers column.
I’m trying to do a vlookup from a local db. I’m not starting from a list viewer like your example but from a map. By clicking a map marker callout I store a latitude value into the local storage (for example 52.383999). This is working fine. How can I do a vlookup into the local db (below) to find which “name” field goes with the stored latitude value? I want a new screen to open with details of the chosen restaurant (name, adress, website link, etc).
That’s a great question, and probably warrants a second tutorial.
My example relies on having the index from the List_Viewer to perform the lookup so you’d need to modify the code to do a reverse lookup as well - does that help at all?
My problem is I’m not literate enough to read all your code I don’t know how “j” really works. I’m now trying like below, but the last piece doesn’t click to “if false”…
“j” works by taking EACH value of the “numbers” column, in sequence. So, it will be ‘one’ three times, followed by ‘two’ 3 times, then ‘three’ 3 times, and then ‘one’, ‘two’ and ‘three’. That loop will be running 12 times (according to the example), doing whatever is described in the “do” section of the block.
As to what you are trying, you are relying on the value of “index2” to access a cell, but you are not looping to change that index to a different one if it is not a match. What you need is something that would start with index2 set to 1, check for a match, break out of the loop if there is a match, and increment index2 if there isn’t a match so that the next loop iteration can check the next value.
Now, your loop could be a "count from (index2) from (1) to (length of (Local_DB1 call GetColumn column: latitude)) ", or it could be a “for each item (next_latitude) in Local_DB1 call GetColumn column: latitude”, but then you do not know what index you are looking for unless you create one and increment it each time the loops goes though without a match.
Or, you can make a use of 'in list (Local_DB1 call GetColumn column: latitude) find first occurrence of item latitude_searched ’ , which returns the index that the latitude you are searching for occupies in the table column.
If that index is zero, that is because you are looking for something that is not in the database, and you have to cater for that. But if the returned index is not zero, that is the value of index2 you are looking for, and that you can use to extract the value in the cell “name, index2”
Can this VLOOKUP be done in airtable to thunkable X?
The user Chooses a “name” (from a list viewer) and then it searches the air table “names” column. Once it finds the matching name it moves to the right 1 or 2 cells and retrieves the information in the same row…? How would this blocks compare to this ?
Yes, vlookup works. But, if the name appears more than once this may cause you trouble.
I usually do a vlookup based on 2 columns. name & date. My airtable is being filled each day by my regular users though so when they want to find particular cells, this 2 column vlookup is needed.
As per the kind suggestion of @jared, I was looking at this tutorial in order to obtain the display of an image from a List viewer with an Airtable spreadsheet linked.
I followed all the steps, but I think I’m doing something wrong! Here is my block
As you can see, in the block “for each item j in list”, according to the tutorial I should add a “in Airtable_ITA call GetColumn”, but the blocks cannot be linked one to the other - or better, there are no blocks for the element Airtable_ITA that can be linked to the block “for each item j in list”.
I gave a try adding a LocalDB component and I could replicate the tutorial, but when I’m using Airtable, apparently I cannot follow these instructions.
Are you certain you have the api key and base if and table name and view correct?
Can you ever retrieve data from your table?
Can you get the row “on screen start” and save it to a variable
That would be the list you search through. Each cell of the column is 1 object.
The idea is to be able to sort the listviewer. It looks through the column, starting at row 1 until it find the matching content. When it finds matching content, that is the row.
I want to store “n” number of Web name and its respective Web urls in 2 text boxes. Then all the web names stored shall be displayed in 1 list view when I click on it and it shall show all web names where I can select it. After I click and select the list view shall minimize with he web name selected and then its respective url shall open in the web page. How can I do this
I solved it using the codes from the first image. Thus, when the student clicks on his name, he pulls the “current training” column on the same line as the name and shows the column items in the listviewer.
BUT I used the “access filter” block so that when the person enters, only their name appears in the listviewer, preventing them from seeing someone else’s training. But that doesn’t work, because the variable “line” is always 1, which makes each person click on their own name correctly, but EVERYONE sees the “current training” of the first person in the airtable.
What is VLOOKUP?
Design
Blocks
Remix
I don’t know how “j” really works. I’m now trying like below, but the last piece doesn’t click to “if false”…
