Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Script to check if any items in a lookup array are in a different lookup array

78 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi there,
I'm trying to script something but it's been ages since I used Javascript and I'm getting myself tied up.
I have a list of rehearsal  dates,  and next to each date I have a linked record showing the actors required for that day's scene, and a linked record showing the actors unavailable for that day. I would like to script something that checks if any item of the array in "Actors Unavailable" for each record (rehearsal) matches any of the people in the "Actors Required" field. If so, we have a conflict and I need to change something. So I'd like it to then either check a checkbox or colour the record to indicate it needs fixing.

To duck debug for a second, what I want in plain language I think is
"For each record in table "Rehearsals"
Check each item in array "Actors Unavailable"
See if it appears in the "Actors Required" array of this record
If so, change record colour (or tick a box)
If not, do nothing"

Trouble is my brain isn't doing the work to turn that into javascript. Can someone help me?

1 Reply 1

To solve this array comparison, I'm using a filter() to find included elements of each array in one another.

const array1 = ["Cat", "Dog", "Mouse", "Rat"];
const array2 = ["Rat", "Dog", "Mouse", "Fish", "Elephant"];

let matchingElements = array1.filter( element => array2.includes(element));
console.log(matchingElements);

So within an Airtable Automation, we could have this script;

const { rehersalRecordId, requiredActors, unavailableActors } = input.config();

let rehersalTable = base.getTable("Rehearsals");

let matchingElements = requiredActors.filter((actor) =>
	unavailableActors.includes(actor)
);

console.log(matchingElements);

if (matchingElements.length > 0) {
	await rehersalTable.updateRecordAsync(rehersalRecordId, {
		Automation: { name: "Clash Detected" }
	});
} else {
	await rehersalTable.updateRecordAsync(rehersalRecordId, {
		Automation: { name: "Checked" }
	});
}

The inputs of that Script could be setup like this;

Karlstens_0-1672893131471.png

The idea being, when any of the data changes in columns that could raise a clash, that will trigger the check. Here is the result from my script test running above - Simon Pegg is double booked.

Karlstens_1-1672893187024.png

If the data is changed, the check kicks in and updates the problem record;

Karlstens_2-1672893254250.png

As the data gets updated, checks are re-done;

Karlstens_3-1672893313849.png

A record can be updated as many times as needed, the check will still occur.

Karlstens_4-1672893344913.png

The Automation Trigger looks like this;

Karlstens_5-1672893400724.png

Hope this helps!