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 :slightly_smiling_face:
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({
['Volunteer']: triggerValues.volunteer,
['Start']: triggerValues.start,
['Stop']: triggerValues.stop,
['Volnteering (same shift)']: Array(...(sameShiftRecordsexact.map(x => x.volunteer))).join(', '),
['Volnteering (overlapping shift)']: Array(...(sameShiftRecordsoverlap.map(x => x.volunteer))).join(', '),
['Double Booked?']: isDoubleBooked,
['Relieving']: Array(...(prevShiftRecords.map(x => x.volunteer))).join(', '),
['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(', ')
})
