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