Search Each Value in a List within another Array List

Unfortunately this isn’t currently possible. Airtable formulas don’t provide any way of iterating through the contents of an array to do specific things with each array item.

Correct. Formula fields are good for many things, but detailed searching and processing can’t be done with formulas. A script is the way to go.

Thanks for the clarification Justin, I had fooled around with array functions in formulas and returned only frustration :slightly_smiling_face: . I posted in the Scripting thread hoping to see someone post a scripting example. I’ve tried searching for hours but can’t seem to find an answer. I’m assuming I’m not using the correct search terms like list, array, values, etc in the right order to explain in the scripting parlance what I’m asking for.

In plain English I need to search for multiple items in a list that also contain multiple items. The end result would be the count of positive hits at least, or a count and the terms that were found.

I do not know any javascript so I’m stuck without help.

Even though this might sound like a common problem, there’s enough uniqueness to it that it’s tough to write a generic script for something like this, which puts the script development in the category of work for hire. Normally I’d offer to help, but my plate is already quite full. I’ll tag @Kamille_Parks @kuovonne @Jeremy_Oglesby @JonathanBowen in case they can help (you’ll have to talk privately with them about their respective rates). You might also consider posting to the #developers:work-offered category.

The most basic Javascript would be something like:

let array1 = []
let array 2 = []
let intersection = array1.filter(element => array2.includes(element));
let count = intersection.length

let array1 = []
let array 2 = []
let intersection = array1.filter(element => array2.includes(element));
let count = intersection.length

Kamille’s JavaScript is simple way of getting the count of how much overlap there is between the two arrays. However, for your actual scheduling use case, it sounds like you will want far more than that. You have a complex base design, and it isn’t completely clear where the arrays are coming from.

If you are interested in hiring Kamille, she is an excellent script writer (as are all the other script writers that Justin recommended). If you are interested in hiring me, you can book an appointment to describe your project.

let array1 = []
let array 2 = []
let intersection = array1.filter(element => array2.includes(element));
let count = intersection.length

Thank you @Kamille_Parks. This helps me understand the syntax a little bit. I still can not figure out after reading the AirTable documentation the syntax to turn “let array1 = [field in a table]” and then “let array2 = [another field in the same table]” then do the intersection function. The number return could be great. I’d need to count the total number in the “array1” against the total number found in “array2” (not the total number of array 2) and then a simple divide for the %

so if Array1 = “(data would come from a field) “Suzie”, “Will”, “Tyrone”, “Ellis””
and array2 = "(data coming from another field on the same table) “Suzie”, “Bill”, “Jessa”, “Ellis”, “Will”, “Jeremy”, “Elaine”

the result I’d want would be:

count of array1 = 4
count of array1 found in array2 = 3
3/4=.75
75% *update the record being evaluated into a number field. Script triggered by per-record button rather than running a script on the entire table

The array data will be record IDs so they will be all unique

Thanks again for all the help. It’s the javascript syntax and the Airtable syntax that I do not have a foundation with and they are not as Cleary demonstrated, only documented, as the IF statement documentation.

What I want to do is not logically complex.

the syntax would probably be

let array1 = record.getCellvalue("name of field")

or

let array1 = record.getCellvalue("name of field").map(item => item.name)

let array1 = record.getCellvalue("name of field")

let array1 = record.getCellvalue("name of field").map(item => item.name)

@Kamille_Parks getting there!

my code that is returning the count is (script run from a button field on the record)

let table = base.getTable("Scheduling");
let record = await input.recordAsync('Select a record to use', table);
let array1 = record.getCellValue("Team Member Record ID");
let array2 = record.getCellValue("Availability Member Record ID");
let intersection = array1.filter(element => array2.includes(element));
let count = intersection.length;
output.text(count);

I am going to try and figure out how to get the total count of array1/the output of “let count” and then try and figure out how to update a field in the record with the results of the math division.

Thank you for your help Kamille I’m so close to the desired results.

My console log shows the array count but I can’t find any documentation on how to call that count from the console. It’s very frustrating. Like the documentation is written for someone who already knows every possible syntax and variable call not someone trying to learn how to script.

let table = base.getTable("Scheduling");
let record = await input.recordAsync('Select a record to use', table);
let array1 = record.getCellValue("Team Member Record ID");
let array2 = record.getCellValue("Availability Member Record ID");
let intersection = array1.filter(element => array2.includes(element));
let count = intersection.length;
output.text(count);

This is true. The documentation is not designed for someone who is trying to learn JavaScript. Documenting the features accurately is hard enough without also trying to teach people how to code at the same time. On the other hand, learning how to read documentation is a useful part of learning how to code.

It sounds like you are making progress in learning how to code. If you are serious about learning to code, (versus just getting this script to work), taking a basic JavaScript class will help with this. There are several free resources on the internet.

This might be a terminology issue. I’m not sure what you mean by “call that count from the console”. Do you mean how to get the length of an array?

let array1count = array1.length

let table = base.getTable("Scheduling");
let record = await input.recordAsync('Select a record to use', table);
let array1 = record.getCellValue("Team Member Record ID");
let array1count = array1.length
let array2 = record.getCellValue("Availability Member Record ID");
let intersection = array1.filter(element => array2.includes(element));
let count = intersection.length / array1count;
await table.updateRecordAsync(record, {"Team Availability %": count});

Glad to hear that you were able to get your script working!

You might be overestimating what you need to learn in order to get past that frustrating phase.

There are free, online, self-paced resources for the code-curious. You don’t have to code for a living or even want to code for a living to take them. For me, for many years writing scripts was a fun, affordable, hobby that occasionally made the day job easier. But if you are not interested in learning coding, that’s fine too!