Performing a VLOOKUP in LocalDB

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.

:question: 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

:art: Design

Let’s start by adding a ListViewer with three items:
22

Next, in our LocalDB, we’ll create a 12 x 2 spreadsheet. Note that the “numbers” column has the same data as our ListViewer
59

:computer: 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
33

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:
40

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
58

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.

:gift: Remix

If you want to take a closer look at my app or use this feature in your own app you can remix my project here.

2 Likes

Hi Domhnall,

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).

image

Hi @7a808d888dc141,

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 :slight_smile: 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”

2 Likes

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 ?

1 Like

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.

2 Likes

Hi everyone!

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.

Anyone could suggest how to solve it?

Thanks in advance

Ale

1 Like

I have the same problem, in airtable this block does not linked

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.

1 Like

did you manage to do this using airtable? even following the tips I can’t fit the blocks. if so, can you share a screenshot of the blocks used?

my problem is that I will not only change the column, I will change the column ACCORDING to the selected item

I will add a picture of my airtable!

I have a query similar to this

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

@37abdulallam378i6 Something like this?

1 Like

Yes but a bit modified. Can these 6 links club into 1 list. So that I can click on the list and the dropdown opens and then I select the link.

1 Like
1 Like

I could not find it. Can U help me on this. Thanks

@37abdulallam378i6 how are you getting on with this project? Anything else we can help you with?

I am back on thunkable after completing some other work. Yes Jared video helped me. Currently working on it

1 Like

print1

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.

@thabattapereira

how the design look like? if you can share a screen shoot or link project would be appreciated.