Script comparing and updating records

[Pierre_Delannoy]

Hello dear community members,
I hope you’re doing great.

I am requesting your help because I would like to use a script, so that when a partner edits a Linked Field (here ‘Status’) in his Table View, it will directly compare its value to the Status of the client in my Lead Table (lookup field) to update it, based on the one the partner selected.

I was using “edit shared view” before but it has been removed by Airtable, and I tried different options but seems the “only way”. I already have different synced tables within my main base, and I manage to organise everything between them, but he cannot work for this specific need of editable view.

Because the “Status” that is to be chosen from the partner in its view is creating a duplicate in my ‘Status’ table, and thus do not update my CRM, which therefore do not stop/ launch my sequencing and is a big problem for me because it means I will/or will not send email to people who should receive/or not one.

The thing is that in both table my ‘key field’ is not the same. That’s why I think only a script can help me here…I have been able to prepare what I want using VSCode but I know nothing about scripting in JS or JSON. Could someone please help me redact it ?

I am adding some pictures to show the structure and what I imagined.

Thanks a lot in advance for your help,
Pierre

[Karlstens]

When comparing fields in Airtable, I often use a Formula Field with Regex_Match() to check if they match, could that be of some assistance here?

REGEX_MATCH({Linked Field A}, {Linked Field B})

[Pierre_Delannoy]

Hello @Karlstens,
Thanks a lot for you help!

I have been trying to use the REGEX_MATCH Formula, but do you think there is a way to create a formula or script to update {LinkedField A} based on {LinkedField B} if they are different ?

Because for now the only thing I have been able to make is to compare and display a specific message in the formula case…

Thanks :slightly_smiling_face:

[kuovonne]

Pierre_Delannoy:

to update {LinkedField A} based on {LinkedField B} if they are different

You need an automation to actually update the link. Use the formula field to trigger the automation.

[Pierre_Delannoy]

Hello @kuovonne,
Thanks for your help!

I wanted to do a REGEX_MATCH to compare both linked fields and display ‘to change’ if they were different and then launch an automation to:

• look for records with same ID in both Table
• update record from Table A based on status from Table B

But I am not able to make this automation because my primary field is not the same in both Table (as I use it to make my clients anonymous), therefore I am unable to compare a name to an ID…

Do you know a workaround maybe ?

[kuovonne · Accepted solution]

Pierre_Delannoy:

therefore I am unable to compare a name to an ID

Have a lookup field of the name. Then you can compare name to name. Or vice versa.

[Pierre_Delannoy]

Hello @kuovonne,
Thanks a lot for your advice!

I created a IF(AND(OR)) function to display OK or Change, and added a lookup of the RECORD ID from my main database.

When the formula displays “Change” it is triggering an automation to find and update my main database based on the secondary one!

There is a little ‘lag’ as it is not ‘instant’ but works quite well if explained accordingly!

Thanks a lot