Help

Re: How can I do Inner Join?

Solved
Jump to Solution
2477 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Sara
8 - Airtable Astronomer
8 - Airtable Astronomer

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😊

 

1 Solution

Accepted Solutions
Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

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.

 

See Solution in Thread

10 Replies 10
Tyler_Thorson
6 - Interface Innovator
6 - Interface Innovator

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

Hi Tyler, Thank you very much for your fast reply and for your help!

7.PNG

Those are the two columns that I'm trying to join, They have separators.

Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

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.

 

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:

8.PNG

 

Thank you again, and sorry for all those questions 🙏

Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

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

Andrey_Kovalev_0-1670941398058.png

Please let me know if you'll have further questions.

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?

9.PNG

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

Andrey_Kovalev_0-1670944815437.png

 

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
8 - Airtable Astronomer
8 - Airtable Astronomer

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

Andrey_Kovalev_0-1670965205393.png

 

It works!! Thank you thank you very much!