See if similar/identical record exists in another table

[bitstreams_red]

As ever, something that I think should he simple is proving anything but.

I have a table with 4 simple fields:

Make	Model	Line	Exists ?
Dell	Latitude	2243
HP	Probook	M123
Dell	Optiplex	C132

I have another table with similar Make, Model, Line fields

I would like to change the value of Exists in the first table to 'True' if there is a record in the second table that has a matching Make/Model/Line combination.

I created an automation that works (sort of) in testing mode. It actually saves one of the values in to the Exists? field as I haven't worked out how to make it say 'True'.

But I can't work out how to run the automation on demand. I have tried having a checkbox field in the first table and watching that field, but that doesn't seem to trigger the automation. I would ideally have a button field on each record - sort of 'Check this' - or a button that would check all the fields in the first table.

How should I attack this ?

[TheTimeSavingCo]

Hmm, for checking them one by one, try creating a checkbox field called "Run check" or something in your second table and create your automation like this:

Link to base

---
Running the check on everything is trickier and gets a bit more involved.  If you're not concerned about the number of automation runs you could just use the above method and copy paste the checkmark to trigger the automation for all your records.  Lemme know if the automation run number is an issue and I'll see what I can figure out

[j-hugg-ins]

As @TheTimeSavingCo says Automation usage can be an issue if you're not careful. It's why I'm a big fan of using scripts which can be triggered with 1 automation run and then do a huge amout of heavy lifting for you. So you could add a checkbox field to your first table next to 'Exists?' call it 'Check'. Then trigger the following script to run when it's checked.

The only input variable you need is 'id' and you want to set the value as the record id of the trigger record.

Here's the script:

const { id } = input.config()
const table1 = base.getTable("Table 1")
const table2 = base.getTable("Table 2")

// Get Record Details

const record = await table1.selectRecordAsync(id, {
  fields: ["Make", "Model", "Line"]
})

// Check every record in Table 2 for a match

const query = await table2.selectRecordsAsync({
  fields: ["Make", "Model", "Line"]
})
const matches = query.records.filter(rec => {
  return rec.getCellValue("Make") === record?.getCellValue("Make") &&
    rec.getCellValue("Model") === record?.getCellValue("Model") &&
    rec.getCellValue("Line") === record?.getCellValue("Line")
})

// Set Exists checkbox to true if a match was found

const exists = matches.length > 0
table1.updateRecordAsync(id, {
  "Exists": exists
})

output.set("message", `${(exists) ? "Match found" : "No matches found"}`)

[bitstreams_red]

Wow, thanks guys I will take a moment and have a play.

[Alexey_Gusev]

Hi,

There is a way to do it without script and automation.
Create formula
TRIM(Make  & '_' & Model & '_' & Line), use it as primary field in a second table
add the same formula field in a first table. it's not mandatory to make it primary, but you can do it.
Create link field from table 1 to table 2.
In table 1, copy-paste whole formula field into the link field
Existing will be linked.
You can modify 'Exists ?' field to a formula
IF({Link to Table2}, 'Yes')

You can hide formula and linked fields in the table 1

If you added some data in table(s) and want to check again, unhide formula and linking fields in table 1 and again do the copy-paste operation.

Of course, if you don't want to add fields, you can use automation or script above. In Airtable scripts, you usually have some input data, which you need to convert to output value, or array of output data. Automation GUI lets you manage input and output procedure. Sometimes you can do the whole task without code, like in example above. So, its up to you.
You can use script above in scripting extension, and then add buttons instead of checkboxes. Thus, you don't spend any automation runs.
Some things must be changed for that - line 1 & lines 7-9  It is the way you input data in automation. Scripts receive data in a different way.
To get data from the record, on which button pressed, see Example 'Record Picker', in Scripting Extension editor window. You don't need whole piece of code, just the beginning.

Also, output.set works only in automation. It has 2 parameters: variable name and value. You don't need it in svcript, so you can just show message instead. The last line must be

console.log(`${(exists) ? "Match found" : "No matches found"}`)

and a little fix, put await before async operation 

table1.updateRecordAsync

[Alexey_Gusev]

About automation and script, I prefer DRY code (means 'don't repeat yourself'), especially when 'hardcoding' field names. It's hard to maintain when you need to change their name or add some.
Also, you don't need to query first table if you put all values to check as input data.
That's light version (checked, working):

When you run automation by clicking checkbox, unchecking it in the last step is a good way to show it's done.
So I decided to  put 'Exists ?' field update in the same step outside the script.

And the script itself. I just realized I don't need record id in script anymore, but it's a good example of destructuring assignment, so let it be here.

let {id, ...mydata}=input.config()
let table=base.getTable('table2')
let check=rec=>Object.keys(mydata).every(key=>rec.getCellValue(key)===mydata[key])
let reallyExists=await table.selectRecordsAsync().then(q=>q.records.some(check))
output.set('Exists',reallyExists? 'Exists' : 'nope' )

[bitstreams_red]

Sorry, I'm prpbably being thick, but I'm trying to use the formula method and I don't understand what you mean by "In table 1, copy-paste whole formula field into the link field"

Create formula
TRIM(Make  & '_' & Model & '_' & Line), use it as primary field in a second table
add the same formula field in a first table. it's not mandatory to make it primary, but you can do it.
Create link field from table 1 to table 2.
In table 1, copy-paste whole formula field into the link field
Existing will be linked.
You can modify 'Exists ?' field to a formula
IF({Link to Table2}, 'Yes')

Simon

[bitstreams_red]

I have been playing with the script again and have gone from it saying that there are no matches to it matching every time.

Here is a screen shot of the Input variables - I'm testing using the ID, Brand, Model and Line and these appear ok when I hover over the script (and in the console log as inputs)

and this is the script I am using:

console.log(`Hello, ${base.name}!`);

let {id, ...mydata}=input.config()

let table=base.getTable('Database')

let check=rec=>Object.keys(mydata).every(key=>rec.getCellValue(key)===mydata[key])

let reallyExists=await table.selectRecordsAsync({fields: table.fields})

output.set('Exists',reallyExists? 'Exists' : 'nope' )

Sorry to be a pain with this - am sure I'm missing something fundamental, but I can't get it to test ok.

[Alexey_Gusev]

Hi,
I mean, select the whole field, press CTRL+C  (I'm on Windows, don't know how it's done on Mac)

then select the whole link field

and press CTRL+V

That's all.

But I need to add note and explain a bit.

Primary field in Table 2 must be formula.
Link by pasting a column of values, works in following way, and it's very important to understand:
Each value from list  will be  searched in the primary field of the linked table. Settings 'Limit record selection to a view' and 'Allow multiple linking' are ignored (means multiple linking allowed despite on option checked.or not) I think, new feature filter ignored as well.
 
If it finds value, it links to it.
If not, it creating the new empty record, puts a value in primary field and links to it.
Sometimes, when using 'link by pasting a column' in a wrong way, you can accidentally add hundreds of new records in a linked table.
One of the possible ways to avoid this - put a formula in a primary field. Thus, table cannot auto-create new records. (Second way - temporary set permission 'who can create records' to Nobody)

So, it will link only to records where Make  & '_' & Model & '_' & Line are the same

[Alexey_Gusev · Accepted solution]

You are just missed the vital part - after the table records queried, each must pass the check:   .then(q=>q.records.some(check))

let reallyExists=await table.selectRecordsAsync().then(q=>q.records.some(check))

then check is in line 4 and it seems like OK in your code

let check=rec=>Object.keys(mydata).every(key=>rec.getCellValue(key)===mydata[key])

you can add console.log(mydata) after line 1 to understand more.
keys of mydata are three field names. every  means 'true when each condition is true' (all three values are equal)
some means if at least one is true then its true. (if such record found then the item exists)

Indeed, it's my fault as well. I should have been split query into 2 lines, to avoid overcomplicated things.