Help

Re: Search Each Value in a List within another Array List

Solved
Jump to Solution
3209 1
cancel
Showing results for 
Search instead for 
Did you mean: 
J_Shockley
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions
J_Shockley
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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.

See Solution in Thread

11 Replies 11

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

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.

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)

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

Screen Shot 2021-07-24 at 11.02.23 AM

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
J_Shockley
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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.

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!