How can I do Inner Join?

[Sara]

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😊

[Tyler_Thorson]

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

[Sara]

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.

[Andrey_Kovalev · Accepted solution]

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.

[Sara]

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 🙏

[Andrey_Kovalev]

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.

[Sara]

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?

[Andrey_Kovalev]

No, you don't need adding a column. Just enter a variable name, and select the first line

[Sara]

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.

[Andrey_Kovalev]

In my case InnerJoin is a column where you store the result.