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.
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.
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 
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
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?
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(', ')
})
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(', ')
})
Wow very powerful Kamille. Great job! I am going to try and incorporate this into my flow 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
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.
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.
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(', ')
})
I like this naming convention :thumbs_up:
I like this naming convention :thumbs_up:
Its just me being too lazy to type “query” lol
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.
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 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.
@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)
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)
Awesome thanks Kamille! I am just starting to dig into learning to script
P.S. Do you also do custom scripting for hire? If so, I may need to hire you for some other upcoming challenges I run into.
Thanks again
Awesome thanks Kamille! I am just starting to dig into learning to script
P.S. Do you also do custom scripting for hire? If so, I may need to hire you for some other upcoming challenges I run into.
Thanks again
The answer 9/10 is no. Sometimes if there’s a fun use case within my capability I’ll consider it, but most times I do not. There are several highly talented consultants lurking in the forum that you can consider however.
The answer 9/10 is no. Sometimes if there’s a fun use case within my capability I’ll consider it, but most times I do not. There are several highly talented consultants lurking in the forum that you can consider however.
Well I do appreciate all the help, I just feel bad asking a ton of questions with really no compensation They need some sort of tip button in the forum for the people so dedicated to help others
@Kamille_Parks Sorry, another question if you get time.
Would it be hard to add these to the original script?
For Double Booked/Overlapping Shifts:
If shift date & hours are the same display “Double Booked” & If shift date & hours only overlap display “Overlap” otherwise “No”
For Volunteering,:
If same shift does not have a volunteer assigned yet display “TBD” in place of Volunteer Name.
*this could be accomplished using a formula field IF(volunteer="",TBD,volunteer) but think would make sense to have in the script instead?
For Relieving Volunteer & Relieved By Volunteer:
Same idea - If same shift does not have a volunteer assigned yet display “TBD” in place of Volunteer Name.
Thanks again
@Kamille_Parks Sorry, another question if you get time.
Would it be hard to add these to the original script?
For Double Booked/Overlapping Shifts:
If shift date & hours are the same display “Double Booked” & If shift date & hours only overlap display “Overlap” otherwise “No”
For Volunteering,:
If same shift does not have a volunteer assigned yet display “TBD” in place of Volunteer Name.
*this could be accomplished using a formula field IF(volunteer="",TBD,volunteer) but think would make sense to have in the script instead?
For Relieving Volunteer & Relieved By Volunteer:
Same idea - If same shift does not have a volunteer assigned yet display “TBD” in place of Volunteer Name.
Thanks again
For TBD you could replace the part of the script inside .updateRecordAsync() using this format:
'Name of Field': value ? value : 'TBD'
Replace value with whatever the script currently has there.
As for editing the double booking part: Yes its possible, no I don’t want to do it.
I have been fine tuning this script for my needs and thought I would post the updates in case it can benefit anyone else.
@Kamille_Parks - Thanks again for getting me going on this. It has been fun to learn how scripting works and look forward to learning more.
This is the first time I have ever worked with scripting, so my modifications may have not been the best way, but they seem to work
Changes Made:
For Double Booked/Overlapping Shifts: If shift date & hours are the same display “Double Booked” & If shift date & hours only overlap display “Overlap” otherwise “No”
For Volunteering: If same shift does not have a volunteer assigned yet display “TBD” in place of Volunteer Name. I also created another field so I could separate the volunteers assigned to same location/same hours & volunteers assigned to same location/overlapping hours.
For Relieving Volunteer & Relieved By Volunteer: Same idea - If same shift does not have a volunteer assigned yet display “TBD” in place of Volunteer Name.
Code:
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') ? x.getCellValueAsString('Volunteer'):'TBD',
start: new Date(x.getCellValue('Start')),
stop: new Date(x.getCellValue('Stop')),
location: x.getCellValue('Location'),
}
)})
const sameShiftRecordsexact = shiftRecords.filter(x => {
return (
((triggerValues.start <= x.start && triggerValues.stop >= x.stop)) &&
(x.location == triggerValues.location)
)
})
const sameShiftRecordsoverlap = shiftRecords.filter(x => {
return (
((triggerValues.start > x.start && triggerValues.start < x.stop) || x.start > triggerValues.start && x.start < triggerValues.stop) &&
(x.location == triggerValues.location)
)
})
const isDoubleBooked = (shiftRecords.filter(x => {
return (
(x.id !== triggerValues.id) &&
(x.volunteer == triggerValues.volunteer) &&
((triggerValues.start <= x.start && triggerValues.stop >= x.stop))
)
}).length > 0) ? '
Double Booked' :
(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)
)
}).length > 0) ? '
Overlap':
'
No'
const prevShiftRecords = shiftRecords.filter(x => {
return (x.stop.toISOString() == triggerValues.start.toISOString()&&
(x.location == triggerValues.location))
})
const nextShiftRecords = shiftRecords.filter(x => {
return (x.start.toISOString() == triggerValues.stop.toISOString()&&
(x.location == triggerValues.location))
})
output.table({
t'Volunteer']: triggerValues.volunteer,
o'Start']: triggerValues.start,
e'Stop']: triggerValues.stop,
u'Volnteering (same shift)']: Array(...(sameShiftRecordsexact.map(x => x.volunteer))).join(', '),
i'Volnteering (overlapping shift)']: Array(...(sameShiftRecordsoverlap.map(x => x.volunteer))).join(', '),
i'Double Booked?']: isDoubleBooked,
l'Relieving']: Array(...(prevShiftRecords.map(x => x.volunteer))).join(', '),
i'Relieved By']: Array(...(nextShiftRecords.map(x => x.volunteer))).join(', '),
})
await scheduleT.updateRecordAsync(triggerValues.id, {
'EXAMPLE ♂️Volunteering (exact shifts) ♂️': Array(...(sameShiftRecordsexact.map(x => x.volunteer))).join(', '),
'EXAMPLE ♂️Volunteering with (overlapping shifts) ♂️': Array(...(sameShiftRecordsoverlap.map(x => x.volunteer))).join(', '),
'EXAMPLE Double Booked/Overlapping Shifts ': isDoubleBooked.toString(),
'EXAMPLE Relieving Volunteer': Array(...(prevShiftRecords.map(x => x.volunteer))).join(', '),
'EXAMPLE Relieved By Volunteer': Array(...(nextShiftRecords.map(x => x.volunteer))).join(', ')
})
