Search Each Value in a List within another Array List

[J_Shockley]

I’ve done a ton of searching and can’t find any answer to the possibility of using an Array (list) in one field of a base as a search input for another field. I do not want to ARRAYJOIN(values) as I am not searching for the total string, I want to take an input SEARCH list of any values, and search each list item independently within another field.

I’ve built a complex Theater scheduling base where there are company members, teams which contain company members, staffing which is a member, availability of each member, and junction tabled them to fit into a scheduling table that is connected to a Show table (date,title)

My goal is to be able to compare the list of members on a team against their individual availability.

Is it even possible to search each item independently (Team Member) that is within an ARRAY/List (Team) within another field that also contains ARRAY data (Member Availability)?

I’m assuming this will need scripting to take the input search list then go one after the other in sequential searching. The return I’d prefer is a total count of found items i.e. Team members who are available, so I can do the percentage against the total team members. If a team had too low an availability % it would be obvious to not schedule that team.

Thank you in advance.

[J_Shockley · Accepted solution]

kuovonne:

let array1count = array1.length

This is what I needed! Thank you. I’ve got it working.

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

kuovonne:

This is true. The documentation is not designed for someone who is trying to learn JavaScript.

I just need more contextual examples. The examples in AirTable go from ultra basic to extremely complex but few in the middle. While I understand this is an advanced feature for those in the know, I didn’t know anything about IF statements prior to AirTable and through their broad example base I was able to build complex logical structures.

I may look into JavaScript courses but I am not the target audience for those courses and I do not code for a living only when it’s required to build functionality and my frustration is to the fact that I’d need to learn an entire system to get a few functions so it doesn’t become worth the time/effort investment.

Thank you to everyone who posted on this thread as I’ve got what I needed from the community of brilliant AirTable members.

[kuovonne]

J_Shockley:

I’ve got what I needed

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

J_Shockley:

code … only when it’s required to build functionality and my frustration is to the fact that I’d need to learn an entire system to get a few functions

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

J_Shockley:

I may look into JavaScript courses but I am not the target audience for those courses and I do not code for a living

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!