🆘 Complicated formula help please

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!

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 :slight_smile: 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.

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 :slight_smile:

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(', ')
})
1 Like

Wow very powerful Kamille. Great job! I am going to try and incorporate this into my flow :slight_smile: 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”.

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'

Great! Thank you again :slight_smile:

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.

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.

I like this naming convention :+1:

1 Like

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

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 :slight_smile: Also, thanks again for the script!

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

Thank you.

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)