Is there a formula/automation for intersection of two arrays?

Airtable doesn’t currently have a formula function for array intersection. What you want could be done via a script, which could run either manually in the Scripting app or automatically as part of an automation.

Thanks!

I am not super technical but is there a script that you have come across that I can repurpose (with editing on my end) for this? Something that is doing close to array manipulation?

The basic setup would look like this after you’ve collected your availabilities into two arrays:

const filteredArray = array1.filter(value => array2.includes(value));

(credit: StackOverflow)

Without knowing more about your base design, it’s tough to know how to fill this out into a more complete script. Do you know whether you plan on running this manually or automatically?

const filteredArray = array1.filter(value => array2.includes(value));

Thanks for the link!!

I plan to run it automatically.

How it works:

• When a condition is matched (to call this script), the automation will call this script for a particular row
• The script looks at two fields of timings (in the same row) and derives the first common time available and outputs on a third field (the finalised time)
• The automation then proceeds to send a calendar invite to both users

Thanks for the update. How are the timings specified? What field types? If they’re lookup fields, what are the target field types in the linked records?

How are the timings specified?

The users share the timings to a multi-select using the format “19 Aug 2021 6:00 PM” - which aligns to date and time

What field types?

Multi-select where both users share the timings in the above format.
The “finalised time” where the script outputs will be in date and time format such that the automation can pick it up to send calendar invites.

If they’re lookup fields, what are the target field types in the linked records?

No field is a lookup type field. They get their input from a mix of automations and inputs from the frontend on stackerhq.com

While a multiple-select field might work in the short term, I can see that becoming very cumbersome to manage over the long haul, with the field options quickly becoming unwieldy unless someone goes in and manually strips out old options on a regular basis. Of course that will mess up any old records that use those outdated options, but then again I don’t know the specifics of your use case, so maybe clearing out old records for past appointments isn’t a problem. Anyway, I suggest considering a switch to using linked record fields instead of multiple-select fields. You’ll still likely find yourself wanting to strip out old records as old dates become invalid, but the changing list will be much easier to manage with the options in a separate table, not items in a field setup.

Unfortunately I don’t have time to invest into writing a full script to solve this problem, but here’s a very rough outline:

• Get the the timing data from the automation trigger step (you can use input variables for the script action to make this a little easier) into two arrays
• Find the intersection of the arrays using the snippet above
• If filteredArray isn’t empty, you’ll then need to parse the text of the matching dates into actual date objects so that you can compare them to find the earliest option
• Set the target field of the original record to this final date & time

The best recommendation that I can make is to just take it a piece at a time. Write a simple script that will collect and display the raw data first to ensure that you’re collecting it correctly. Then move on to the intersection. Test it and confirm the output. Keep going until you have a finished script, then test the full automation. The API documentation at the bottom of the script editor is a great resource, and we can always help you to troubleshoot if you get stuck. If you’d rather have someone else write the script, there are several of us here who are available for hire, but full development from scratch always comes with a price tag.

Thank you Justin for this. Really appreciate you taking out time to guide me here.

Let me use your suggestions to give this a go. If it doesn’t work out, I will reach out to you.