- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 06, 2022 10:06 AM
Hi, I'm trying to do an inner join (find elements in common between 2 columns).
How can I do that here?
I saw the functions Concatenate and Arrayjoin, but I didn't find anything about Inner join, maybe I'm looking in the wrong place. Is it possible to do it? Thank you😊
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 12, 2022 12:05 PM
Hi @Sara ,
Here is an automation example that can help you to solve your problem.
//collect input
let Rec = input.config()
//load records
let table = base.getTable("tbllsZTAQCsX3JxGi")
let record = await table.selectRecordAsync(Rec.RecID)
//declare inner join array
let innerJoin = []
//function for filtering unique array entries
function onlyUnique(value, index, self) {
return self.indexOf(value) === index;
}
//check existence of the record and not empty arrays
if(record !== null &&
record.getCellValueAsString("Array1") !== "" &&
record.getCellValueAsString("Array2") !== "")
{
//variables declaration
let array1 = record.getCellValueAsString("Array1").split(",")
let array2 = record.getCellValueAsString("Array2").split(",")
//check if entries of array1 exist in array2
for(let entry of array1){
if(array2.includes(entry)){
innerJoin.push(entry)
}
}
//filter unique array entries
innerJoin = innerJoin.filter(onlyUnique)
//update InnerJoin field with contents of innerJoin array
await table.updateRecordAsync(Rec.RecID, {
"InnerJoin": innerJoin.toString()
})
}
There are a couple of clauses:
- single line text fields assumed. To deal with lookup and formula fields you need to ensure converting their values into strings. This can be achieved by reading field content as a string or concatenating its value with an empty string.
- The names of the source fields are Array1 and Array2 instead of Report 1 and Report 2
- RecID variable is used to address current record to deal with.
Hope this helps.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 06, 2022 10:28 AM
This is fairly simple to do if the values in each column are separated by a character, like a comma or space.
It is much more difficult if you want to compare every possible selection of characters from a string in one column, to every possible selection of strings in another column.
For example comparing:
[Apple, Orange, Banana] to [Grape, Carrot, Banana]
is relatively simple.
But Comparing:
[Appleorangebanana] to [Grapecarrotbanana]
is extremely complicated.
Can you give a little more context about the format the content you want to compare is in?
Tt
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 06, 2022 10:31 AM
Hi Tyler, Thank you very much for your fast reply and for your help!
Those are the two columns that I'm trying to join, They have separators.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 12, 2022 12:05 PM
Hi @Sara ,
Here is an automation example that can help you to solve your problem.
//collect input
let Rec = input.config()
//load records
let table = base.getTable("tbllsZTAQCsX3JxGi")
let record = await table.selectRecordAsync(Rec.RecID)
//declare inner join array
let innerJoin = []
//function for filtering unique array entries
function onlyUnique(value, index, self) {
return self.indexOf(value) === index;
}
//check existence of the record and not empty arrays
if(record !== null &&
record.getCellValueAsString("Array1") !== "" &&
record.getCellValueAsString("Array2") !== "")
{
//variables declaration
let array1 = record.getCellValueAsString("Array1").split(",")
let array2 = record.getCellValueAsString("Array2").split(",")
//check if entries of array1 exist in array2
for(let entry of array1){
if(array2.includes(entry)){
innerJoin.push(entry)
}
}
//filter unique array entries
innerJoin = innerJoin.filter(onlyUnique)
//update InnerJoin field with contents of innerJoin array
await table.updateRecordAsync(Rec.RecID, {
"InnerJoin": innerJoin.toString()
})
}
There are a couple of clauses:
- single line text fields assumed. To deal with lookup and formula fields you need to ensure converting their values into strings. This can be achieved by reading field content as a string or concatenating its value with an empty string.
- The names of the source fields are Array1 and Array2 instead of Report 1 and Report 2
- RecID variable is used to address current record to deal with.
Hope this helps.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 13, 2022 06:00 AM
Hi @Andrey_Kovalev , Thank you very much for your help!
I've never used a script in automation, so I have some questions.
When you wrote this:
- single line text fields assumed. To deal with lookup and formula fields you need to ensure converting their values into strings. This can be achieved by reading field content as a string or concatenating its value with an empty string.
do you mean using, for example an ARRAYJOIN?
And
- RecID variable is used to address current record to deal with.
what exactly is RecID? How can I convert it in a string? because I have this error:
Thank you again, and sorry for all those questions 🙏

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 13, 2022 06:24 AM
Hello @Sara,
1. do you mean using, for example an ARRAYJOIN?
I mean, to work correctly, you should ensure that your cell values are STRINGS. However, in the script above cell values are taken as strings using method getCellValueAsString, so this should be OK.
2. RecID is used as the script input. You put variables on the left pane of the screen (see screenshot).
Please let me know if you'll have further questions.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 13, 2022 06:31 AM
I didn't know that! thank you!
Do I have to create this column before running the automation? or maybe I should specify it as in RecID?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 13, 2022 07:20 AM
No, you don't need adding a column. Just enter a variable name, and select the first line
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 13, 2022 07:33 AM
I'm sorry I explained myself in a bad way. I have a problem with the field "InnerJoin," as you can see from the previous screenshot. Airtable couldn't find it.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 13, 2022 01:00 PM
In my case InnerJoin is a column where you store the result.
