Aug 11, 2021 07:43 PM
My use case is that two people share availabilities. When there is a common availability, a calendar invite goes to both people on that common time.
Aug 11, 2021 10:27 PM
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.
Aug 11, 2021 10:30 PM
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?
Aug 11, 2021 10:35 PM
The basic setup would look like this after you’ve collected your availabilities into two arrays:
const filteredArray = array1.filter(value => array2.includes(value));
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?
Aug 11, 2021 10:39 PM
Thanks for the link!!
I plan to run it automatically.
How it works:
Aug 11, 2021 10:42 PM
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?
Aug 11, 2021 10:52 PM
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
Aug 11, 2021 11:22 PM
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:
filteredArrayisn’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
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.
Aug 12, 2021 12:49 AM
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.