Compare two Tables (VLOOKUP on Excel)

[Felipe_Apolinar]

Hi SQUAD,

Maybe this is a stupid question, but I’ve been wracking my brains trying to figure it out and couldn’t find a solution.

My Scenario:
Table 1 - list of 300 users with assigned devices by serial number.
Table 2 - list of 1000 devices with other specs, including serial number.

What I am trying to do
Create a third table with all Serial Number listed on Table 1 (I can copy manually) and display on Field 2 the name of the user (record from Table1) and display on Field 3 Other Spec (record from Table2).

On excel, it’s something simple that I can resolve with VLOOKUP (value, table, col_index, [range_lookup]). Any solution/idea for Airtable?

Thanks so much!

[Kamille_Parks]

You’re going to want to link your records from Table 1 and Table 2 together in some fashion using the {Serial Number} field, which means you want to make the {Serial Number} the primary field in [Table 2] (equipment)

The arrangement you’ve described involves a third table, [Table 3], as a junction table. This might not be necessary in your case. You will need to do one of the following:

/Option 1: Two table arrangement/

• Make sure the {Serial Number} is the primary field of [Table 2]
• Convert the {Serial Number} field in [Table 1] into a Link to Another Record field, pointing at [Table 2]'s {Serial Number}
• Add a new Lookup field pointing at [Table 2] to pull in any other spec info you want from that table

/Option 2: Three table arrangement/

• Instead of pairing serial numbers to users in [Table 1] and looking up related information in [Table 3], you actually want to do it the other way around. You link records (users to equipment) together in you junction table, and lookup related information in either or both of your “parent” tables if necessary.
• [Table 3]: Add one Link to Another Record field pointing to [Table 1]
• [Table 3]: Add one Link to Another Record field pointing to [Table 2]
• [Table 3]: For each equipment assignment, record it in this table.
• If you want, go back into [Table 1] and add Lookup fields pointing to [Table 3] to pull the corresponding serial number of assigned equipment. Follow the same process for [Table 2] if necessary.

[Greg_F]

Hi,

you can also use JS scripting module here is link to the code:

gist.github.com

https://gist.github.com/gregonarash/f1e8ce0b81141c6b3f4b023ffd6a8027

Airtable simple VLOOKUP style script.js

//Substitute "Orders" for table name which contains values
//on which you want to run the vlookup
let mainTable = base.getTable("Orders");
let mainTableRecords = await mainTable.selectRecordsAsync();

//Substitute "Product" for table which contains range to search in
let lookupTable = base.getTable("Products");
let lookupRangeRecords = await lookupTable.selectRecordsAsync();

//Replace "Item.barcode" with column name which has the values you want to look up 

This file has been truncated. show original

It has enough comments to be self-explanatory but if not you can also check out video below:

[Kai_Dickas]

Hi Greg,

thanks so much for the script. I just have started using airtable scripts so it helped me a lot to understand how things going.

One question: Is it possible to look up not for the exact match but a match where a keyword contains in (instead of order number).

For example: I have a keyword in one table and i like to compare it to another keyword in another table. There should be a match if the one keyword contains in the other.

Table 1: Keyword is: “managed”

Table 2: Keyword is: “managed services”

Like i said i’m new to airtable scripting and coding in general as well, so i would be really happy if you find the time to answer me that question.

Thanks a lot.

Kai

[Justin_Barrett]

There are several methods you can use on string values to see if one is contained inside the other. The most basic is the indexOf() method. This will return the position of the source string in the destination string if it exists, or -1 if it doesn’t. The method is run on the string where you want to search, and you pass in the string you’re hoping to find. Here’s a quick example:

let source1 = "managed"
let source2 = "optimized"
let destination = "managed services"
// The following will return 0 because the destination string
// begins with the source (0-based indexing)
output.text(destination.indexOf(source1))
// The following will return -1 because the source string isn't found
output.text(destination.indexOf(source2))

[Kai_Dickas]

Hi Justin,

thanks a lot for your answer. It helps me to understand what is possible and how to do it.
Like i said i’m a newbie when it comes to coding so for a better understanding: For my requirement it’s necessary to write a new script or is it possible to just modify the script @Greg_F posted just a little bit?

Have a nice day.

Kai

[Justin_Barrett]

The script from @Greg_F could be easily modified to do this. The line to tweak is #17, which currently reads:

        if (rangeRecord.getCellValue("Barcode") === lookupValue) {

That looks for an exact match. To see if the target cell contains the lookup value, it should look like this:

        if (rangeRecord.getCellValue("Barcode").indexOf(lookupValue) != -1) {

Something else to consider is case sensitivity. JavaScript sees “managed” as different than “Managed” or “MANAGED”. Because of that, a good practice when comparing strings is to force all options to be lowercase. That’s done with the toLowerCase() method on any string. Returning for a moment to my earlier example, adding that feature to the code would look like this:

let source1 = "MANAGED"
let source2 = "optimized"
let destination = "Managed services"
// The following will return 0 because the destination string
// begins with the source (0-based indexing)
output.text(destination.toLowerCase().indexOf(source1.toLowerCase()))
// The following will return -1 because the source string isn't found
output.text(destination.toLowerCase().indexOf(source2.toLowerCase()))

In the tweak we’re making to Greg’s code, it would look like this:

        if (rangeRecord.getCellValue("Barcode").toLowerCase().indexOf(lookupValue.toLowerCase()) != -1) {

[Kai_Dickas]

Hi Justin,

thanks so much for your time and your example. It really helps me to get a better understanding how things work - even if i’m still at the beginning. :slightly_smiling_face:
I’ve changed the script like you’ve did in your example and it works like you described it.
But there is still an issue i need to fix so it meet my expectations.

Let’s say a have a Keyword in one table which is “managed service” and in the other table it’s “managed services”. Or in one table it’s “training” and in the other table it’s “it training”. I like to have a match in that two examples. At the moment it’s not. Can you give me a little explanation/example how that can be done?
Thanks in advance and have a great day.

Kai

[Justin_Barrett]

Kai_Dickas:

Let’s say a have a Keyword in one table which is “managed service” and in the other table it’s “managed services”. Or in one table it’s “training” and in the other table it’s “it training”. I like to have a match in that two examples. At the moment it’s not. Can you give me a little explanation/example how that can be done?

It all depends on which direction you’re making the comparison. In your examples, you just said that these values were coming from “one table” and “the other table” and didn’t indicate which order those values were fed into the code.

Using the .indexOf() method, the value passed to that method is the text you’re trying to find inside of the other item, and it’s looking for the whole thing, not just a partial match. If I write:

let a = "training"
let b = "it training"
output.text(b.indexOf(a))

I’m looking for “training” inside of “it training”, which will return 3 because “training” is three characters into the string “it training.” If you reverse the order, though:

let a = "training"
let b = "it training"
output.text(a.indexOf(b))

This returns -1 because the whole string “it training” can’t be found in “training”. My gut says that your non-matching examples above are closer to this example in both cases. The comparison was just run the wrong way.

Now, it’s also possible that there was somehow an extraneous space at the start or end of either item, which could also lead to a mismatch. Those can be cleaned up using the .trim() method.

With all of that in mind, it’s still possible to find a match by trimming both text items, then testing both directions and proceeding if either one matches. While this could be done on a single line of code, it would be long and messy IMO. First, I suggest changing line 12 in Greg’s code to:

     let lookupValue = record.getCellValue("Item.barcode").toLowerCase().trim();

Next, insert this at line 17 (pushing the if() line down to line 18):

        let barcode = rangeRecord.getCellValue("Barcode").toLowerCase().trim();

Now that both items are forced to lower case and trimmed, we can change the if() statement (now line 18) to this:

        if (barcode.indexOf(lookupValue) != -1 || lookupValue.indexOf(barcode) != -1) {

That will run the test both ways. The vertical bars || (often called pipes) make the OR operator, meaning that the code will continue to the contents of the if() statement’s block if either test is true.

[Kai_Dickas]

Hi Justin,

thank you very much for your time and all the information - once again. I really appreciate that.
I was ill the last week so it took me some time to answer.

I will check this out.

Have a great start in the week.

Kai