Help

πŸ†˜ Complicated formula help please

3242 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
Kamille_Parks
16 - Uranus
16 - Uranus

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.

RnJ
8 - Airtable Astronomer
8 - Airtable Astronomer

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.

Kamille_Parks
16 - Uranus
16 - Uranus

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.

RnJ
8 - Airtable Astronomer
8 - Airtable Astronomer

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.

Kamille_Parks
16 - Uranus
16 - Uranus

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.

Kamille_Parks
16 - Uranus
16 - Uranus

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?

RnJ
8 - Airtable Astronomer
8 - Airtable Astronomer

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.

Kamille_Parks
16 - Uranus
16 - Uranus

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(', ')
})
RnJ
8 - Airtable Astronomer
8 - Airtable Astronomer

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
16 - Uranus
16 - Uranus

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
8 - Airtable Astronomer
8 - Airtable Astronomer

Great! Thank you again :slightly_smiling_face:

RnJ
8 - Airtable Astronomer
8 - Airtable Astronomer

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

Its just me being too lazy to type β€œquery” lol

RnJ
8 - Airtable Astronomer
8 - Airtable Astronomer

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
8 - Airtable Astronomer
8 - Airtable Astronomer

@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)