🆘 Complicated formula help please

[RnJ]

I have a scheduling base for Volunteers helping at Non-Profits. I am wondering if it is possible accomplish these with a formula?

I have attached a generic base to give you an idea of what i am looking for. I have entered the answers into the fields as an Example via text.

#1. Volunteering with -
If multiple volunteers are on the (same date, same location, same start time, same stop time), I would like column " :raising_hand_man: Volunteering with :raising_hand_man: " to show the names of each volunteer on the same detail.

#2. Double Booked -
If a volunteer is signed up for multiple details the same day I would like a column “ :eyes: Double Booked/Overlapping Shifts :eyes: ”, if they are double booked or have overlapping shifts.

#3. Relieving/Relieved by -
I would like two columns " :handshake: Relieving Volunteer :handshake: " & " :handshake: Relieved By Volunteer :handshake: " to list the names of those volunteers if applicable (ie. If one shift ends and another one starts. Shift one is 8:00am-10:00am, shift two is 10:00am-2:00pm, shift three is 2:00pm-4:00pm, etc.)

Thanks for any help you can provide!

Airtable

Airtable | Create apps that perfectly fit your team’s needs

Airtable is a low-code platform for building collaborative apps. Customize your workflow, collaborate, and achieve ambitious outcomes. Get started for free.

[RnJ]

Wow very powerful Kamille. Great job! I am going to try and incorporate this into my flow :slightly_smiling_face: I really need to learn scripting…
I noticed for the Double Booked/Overlapping Shifts it only works when the locations are the same. If the locations are different but the shifts are the same it doesn’t register as “yes”.

[Kamille_Parks]

I adjusted the variable definition for double booking:

const isDoubleBooked = shiftRecords.filter(x => {
    return (
        (x.id !== triggerValues.id) && 
        (x.volunteer == triggerValues.volunteer) &&
        ((triggerValues.start > x.start && triggerValues.start < x.stop) || x.start > triggerValues.start && x.start < triggerValues.stop || (triggerValues.start <= x.start && triggerValues.stop >= x.stop))
    )
}).length > 0 ? 'yes' : 'no'

[RnJ]

Great! Thank you again :slightly_smiling_face:

[RnJ]

Also, I will still work on figuring out formulas but Kamille has created a great script for it too!

I think there must be a way with formulas too, but will probably require several helper fields and possibly junction tables.

Thank you.

[Kamille_Parks]

RnJ:

I think there must be a way with formulas too, but will probably require several helper fields and possibly junction tables.

FYI it is impossible to do it with formulas without linking all the records in your table either to each other, or to a single record in another table.

[JonathanBowen]

Kamille_Parks:

const scheduleT = base.getTable('Schedule')
const scheduleQ = await scheduleT.selectRecordsAsync()
const scheduleR = scheduleQ.records

I like this naming convention :thumbs_up:

[Kamille_Parks]

Its just me being too lazy to type “query” lol

[RnJ]

Well you are much smarter than I, so I thank you with saving me the trouble by confirming it is not possible with my current setup :slightly_smiling_face: Also, thanks again for the script!

[RnJ]

@Kamille_Parks Is it possible to use this script with an automation (ie. if a record is updated - run script)?

Thank you.

[Kamille_Parks]

Yes, with mild alteration:

• Remove output.table(…)
• Change the variable trigger to pull in the trigger record from the automation using an input variable instead of being an user input. Assuming you’ve named the input variable triggerID (you do this on the left side of the screen when editing an Automation Script Action) the changes to the top part of the script will look like this (add the first two lines, edit the trigger line to look like the third line below):

const inputConfig = input.config()
const triggerID = inputConfig.triggerID

const trigger = scheduleQ.getRecord(triggerID)