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.

[Justin_Barrett]

J_Shockley:

I want to take an input SEARCH list of any values, and search each list item independently within another field.

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.

J_Shockley:

I’m assuming this will need scripting to take the input search list then go one after the other in sequential searching.

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.

[J_Shockley]

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.

[Justin_Barrett]

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.

[Kamille_Parks]

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

stackoverflow.com

How can I find matching values in two arrays?

javascript, arrays

answered by jota3 on 03:00PM - 26 Oct 17 UTC

[kuovonne]

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.

[J_Shockley]

Kamille_Parks:

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.

[Kamille_Parks]

the syntax would probably be

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

or

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

[J_Shockley]

@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.

[kuovonne]

J_Shockley:

Like the documentation is written for someone who already knows every possible syntax … not someone trying to learn how to script.

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.

J_Shockley:

My console log shows the array count but I can’t find any documentation on how to call that count from the console.

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