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!
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.
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.
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.
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.
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)
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.
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.
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
});
}
