Help

🆘 Complicated formula help please

3766 29
cancel
Showing results for 
Search instead for 
Did you mean: 
RnJ
8 - Airtable Astronomer
8 - Airtable Astronomer

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!

29 Replies 29

You could set up a table for “Shifts”, and for the Schedule table instead of each volunteer entering their start and end times, have them link to pre-made shifts. Then you could make your {Volunteering With} field a Lookup to pull in all the people signed up in that shift. Your {Reliving} and {Relived by} fields would be trickier to set up.

Thank you for the suggestion. I don’t think a “Shifts” table will be feasible with my current base as shifts can really be anything (8:35am-8:55am, 9:00am-9:15am), etc.
I am hoping to accomplish using lookups, rolloups, formulas & possibly a junction table. I understand will get complex with several helper fields if it is even possible.
Thanks agian.

Not entirely sure how it would be done with formulas, so I instead wrote a script for the Scripting App which fetches the names of people in the same shift, previous shift, and next shift. Its already in your base, check it out. The script can be extended to actually update the relevant field values.

Wow very impressive :slightly_smiling_face: Can’t believe you wrote that script so fast!
It works great for searching the info for the answers, however I should have probably mentioned I plan to use the data in the fields to send a confirmation email to the volunteers.
I really appreciate all the help! Thanks again.

RnJ
8 - Airtable Astronomer
8 - Airtable Astronomer

I have the double booked portion figured out if the dates and shifts are exact (see example base), but not the overlapping shifts. I assume the overlapping shifts would be a formula a mile long trying to break out the time into decimals or something, then finding decimal within a range of decimals. Like I said, not sure it it would work and is way over my head :slightly_smiling_face:

The “Volunteering with” is really my #1 priority so I can let them know who they will be with without having to manually enter each name on every record.

Thanks again.

If you’re going to be using this to send emails, it may make sense to use an Automation, and instead of the Scripting App use an Automation Script Action. You could have it where once a person fills out their stat/stop/location information the script is run to fill in the other fields like “Volunteering with”.

And modifying the script to handle overlapping (not just exact) shift times isn’t a bother, but creating a formula to do it would be tremendous effort, if it is at all possible to begin with.

If start and end times aren’t consistent, how do you want to define who gets relieved by whom?

My first iteration of the script said: A relieves B if A's start time = B's end time. Should it instead be: A relieves B if A's start time is the next start time to occur after B's end time?

Your first iteration is correct A relieves B if A's start time = B's end time and the “Agency” & “Location” are also the same.

Alright. The script now updates the necessary fields (the ‘Example’ ones, not the ‘Result’ formula fields).

For the benefit of others, the script used was:

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

const trigger = await input.recordAsync('Which schedule record do you want to gather info about?', scheduleT)

const triggerValues = {
    id: trigger.id,
    volunteer: trigger.getCellValueAsString('Volunteer'),
    start: new Date(trigger.getCellValue('Start')),
    stop: new Date(trigger.getCellValue('Stop')),
    location: trigger.getCellValue('Location'),
}

const shiftRecords = scheduleR.map(x => {return (
    {
        id: x.id,
        volunteer: x.getCellValueAsString('Volunteer'),
        start: new Date(x.getCellValue('Start')),
        stop: new Date(x.getCellValue('Stop')),
        location: x.getCellValue('Location'),
    }
)})

const sameShiftRecords = shiftRecords.filter(x => {
    return (
        (x.id !== triggerValues.id) && 
        ((triggerValues.start > x.start && triggerValues.start < x.stop) || x.start > triggerValues.start && x.start < triggerValues.stop || (triggerValues.start <= x.start && triggerValues.stop >= x.stop)) && 
        (x.location == triggerValues.location) 
    )
})

const isDoubleBooked = (sameShiftRecords.length > 0 && sameShiftRecords.filter(x => x.volunteer == triggerValues.volunteer).length > 0) ? 'yes' : 'no'

const prevShiftRecords = shiftRecords.filter(x => {
    return (x.stop.toISOString() == triggerValues.start.toISOString()) 
})

const nextShiftRecords = shiftRecords.filter(x => {
    return (x.start.toISOString() == triggerValues.stop.toISOString()) 
})

output.table({
    ['Volunteer']: triggerValues.volunteer,
    ['Start']: triggerValues.start,
    ['Stop']: triggerValues.stop,
    ['Volnteering With']: Array(...new Set(sameShiftRecords.map(x => x.volunteer))).join(', '),
    ['Double Booked?']: isDoubleBooked,
    ['Relieving']: Array(...new Set(prevShiftRecords.map(x => x.volunteer))).join(', '),
    ['Relieved By']: Array(...new Set(nextShiftRecords.map(x => x.volunteer))).join(', '),
})

await scheduleT.updateRecordAsync(triggerValues.id, {
    'EXAMPLE 🙋‍♂️Volunteering 🙋‍♂️': Array(...new Set(sameShiftRecords.map(x => x.volunteer))).join(', '),
    'EXAMPLE 👀 Double Booked/Overlapping Shifts 👀': isDoubleBooked.toString(),
    'EXAMPLE 🤝Relieving Volunteer🤝': Array(...new Set(prevShiftRecords.map(x => x.volunteer))).join(', '),
    'EXAMPLE 🤝Relieved By Volunteer🤝': Array(...new Set(nextShiftRecords.map(x => x.volunteer))).join(', ')
})