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

2 years ago
January 5, 2023
6 replies
6 views

Jordan_Summers
New Participant

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?

Karlstens
Inspiring
601 replies
2 years ago
January 5, 2023

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;

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.

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

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

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

The Automation Trigger looks like this;

Hope this helps!

Adam_Van_Essen
New Participant
3 replies
1 year ago
July 25, 2023

Karlstens wrote:

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;

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.

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

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

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

The Automation Trigger looks like this;

Hope this helps!

That is an incredibly helpful explanation! Is there a way to display the values that do not match? For examples a list of people who need to be trained for a revision of a document versus those who have been trained. I'm using what you shared above to flag if training is needed but it would be nice to list who specifically needs to be trained.

Karlstens
Inspiring
601 replies
1 year ago
July 25, 2023

Adam_Van_Essen wrote:

Yah for sure. There's many ways to do this, for now I chose this working method where you'll need to note the placement of '!' and also how I run the method twice against both arrays, to then finally combine their results.

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

let unmatchingElementsA = array1.filter( element => !array2.includes(element));
let unmatchingElementsB = array2.filter( element => !array1.includes(element));

let combinedUnmatched = [...unmatchingElementsA, ...unmatchingElementsB]

console.log(combinedUnmatched);

The return is Cat, Fish and Elephant.

Adam_Van_Essen
New Participant
3 replies
1 year ago
July 25, 2023

Karlstens wrote:

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

let unmatchingElementsA = array1.filter( element => !array2.includes(element));
let unmatchingElementsB = array2.filter( element => !array1.includes(element));

let combinedUnmatched = [...unmatchingElementsA, ...unmatchingElementsB]

console.log(combinedUnmatched);

The return is Cat, Fish and Elephant.

That works beautifully, thank you!

One more question if you don't mind... How to I assign the unmatched values to my "Outstanding Training" field (note this is a linked field to an employees table).

const { RevisionRecord, ToBeTrained, Trainees } = input.config();

let revisiontable = base.getTable(" Document Revisions");

let unmatchingElements = ToBeTrained.filter( element => !Trainees.includes(element));

console.log(unmatchingElements);

if (unmatchingElements.length > 0) {
	await revisiontable.updateRecordAsync(RevisionRecord, {
		"Outstanding training": { unmatchingElements }
	});
}

When I use the code above I receive the Error: Field "fldnbIp2uNpzI2n8R" cannot accept the provided value. I'm assuming it is a simple syntax issue that I'm missing. at main (script:10)

Karlstens
Inspiring
601 replies
1 year ago
July 26, 2023

Adam_Van_Essen wrote:

That works beautifully, thank you!

One more question if you don't mind... How to I assign the unmatched values to my "Outstanding Training" field (note this is a linked field to an employees table).

const { RevisionRecord, ToBeTrained, Trainees } = input.config();

let revisiontable = base.getTable(" Document Revisions");

let unmatchingElements = ToBeTrained.filter( element => !Trainees.includes(element));

console.log(unmatchingElements);

if (unmatchingElements.length > 0) {
	await revisiontable.updateRecordAsync(RevisionRecord, {
		"Outstanding training": { unmatchingElements }
	});
}

When I use the code above I receive the Error: Field "fldnbIp2uNpzI2n8R" cannot accept the provided value. I'm assuming it is a simple syntax issue that I'm missing. at main (script:10)

This is where things get a bit pointy and you need a true understanding of field types in the scripting environment, along with their expected data structures.

For something like a text field, you can do this;

await revisiontable.updateRecordAsync(RevisionRecord, {
	"Outstanding training": "My text goes here"
});

Whilst for a Single Select, you need to be specific by calling a pre-existing option within this specific format;

await revisiontable.updateRecordAsync(RevisionRecord, {
	"Outstanding training": { name: "An already existing option" }
});

But for a Linked field, it's even trickier - as (unless I'm mistaken, or things have changed recently) the value must be an array of id objects. So, let's say I have my "Animal" table, and we have records such as;

Animal Table and record IDs

recABC123	Cat
recEFG456	Mouse
recHIJ789	Dog

await revisiontable.updateRecordAsync(RevisionRecord, {
	"Outstanding training": [{ id : "recABC123" },{ id : "recEFG456" },{ id : "recHIJ789" }]
});

Note how I'm inserting in an array of ID objects that use the animals recordID as the id value.

When I reached this point in understanding a few years ago, I had to hit the JavaScript learning trail and actually learn what was happening here. But that said, I still find it annoying that I can't just use record names directly here (such as "Dog", "Cat", "Fish") as it would have saved me so much headaches over the years.

Adam_Van_Essen
New Participant
3 replies
1 year ago
July 27, 2023

Karlstens wrote:

This is where things get a bit pointy and you need a true understanding of field types in the scripting environment, along with their expected data structures.

For something like a text field, you can do this;

await revisiontable.updateRecordAsync(RevisionRecord, {
	"Outstanding training": "My text goes here"
});

Whilst for a Single Select, you need to be specific by calling a pre-existing option within this specific format;

await revisiontable.updateRecordAsync(RevisionRecord, {
	"Outstanding training": { name: "An already existing option" }
});

Animal Table and record IDs

recABC123	Cat
recEFG456	Mouse
recHIJ789	Dog

await revisiontable.updateRecordAsync(RevisionRecord, {
	"Outstanding training": [{ id : "recABC123" },{ id : "recEFG456" },{ id : "recHIJ789" }]
});

Note how I'm inserting in an array of ID objects that use the animals recordID as the id value.

After spending a few hours on this (including the review of several of your other posts) the I was so close. The issue was that I wasn't mapping my id values, found the solution here: Re: Cannot add multiple record IDs to a linked field. Here is my final code that works great! Thanks again for your help!

const { RevisionRecord, ToBeTrained, Trainees } = input.config();

let revisiontable = base.getTable("📑 Document Revisions");

let unmatchingElements = ToBeTrained.filter( element => !Trainees.includes(element));
console.log(unmatchingElements);

let mappedUnmatching = unmatchingElements.map (x => ({id: x}))


if (unmatchingElements.length > 0) {
	await revisiontable.updateRecordAsync(RevisionRecord, {
		"💼 Training Status": { name: "incomplete" },
		"👨‍🔧 Employees with Outstanding training": mappedUnmatching
	});
}

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

6 replies

Reply

Most helpful members this week

Reply

Related topics

SMS Australia Integrationicon

Product Update: SMS in Canada and More...

What's new in Klaviyo: July product updates

SMS Consent Timestamp via csvicon

Klaviyo Product Event - October 27, 2021

Most helpful members this week

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded