@Viveca_Gardiner Below is a version of the script that gives you a simple interface where you can choose the table and view where these event records are set, as well as the three fields needed for the script to do its job (start time, duration, and end time). As long as the first record in that view has a defined start time, the rest will be calculated by the script.
I’ll probably publish this to the Airtable marketplace, but that’ll take some time. In the meantime, here’s a how to add this to your own base:
- Install the Scripting app
- Close the popup dialog once the app opens and delete the short sample script in the app window
- Copy the full script below (scroll to see the whole thing) and paste it into the Scripting app, then click “Finish editing”.
- You should see a UI and some brief instructions. Use the UI to select your event table, a view in that table where you’ve set the event order, and the three fields mentioned above.
- Once these are chosen, the Run button will be enabled, and you can run the script.
And here’s the script:
// Allow user to select the table and view to use, and which fields will be affected
const config = input.config({
title: "Set Sequential Event Times",
description: `Update a collection of events that occur in sequence so that each new event starts when the previous event ends.
The first record in the selected view **must** have a set start time to begin the calculations.
Any other record with a set start time will use that as the start time for that record.
All other records will have the start time calculated based on the end time of the previous record.`,
items: [
input.config.table("eventsTable", {
label: "Events table",
description: "The table containing the event records"
}),
input.config.view("eventsOrderedView", {
label: "Event order view",
description: "The view where you have set the order for the event records",
parentTable: "eventsTable"
}),
input.config.field("startTimeField", {
label: "Start time field",
description: "The field to contain the event start time",
parentTable: "eventsTable"
}),
input.config.field("durationField", {
label: "Duration field",
description: "The field containing the event duration",
parentTable: "eventsTable"
}),
input.config.field("endTimeField", {
label: "End time field",
description: "The field to contain the event end time",
parentTable: "eventsTable"
})
]
});
async function main (records) {
// Begin processing
let updates = []
let endTime = null
// Cycle through the records
for (let record of records) {
let update = {
id: record.id,
fields: {}
}
let startTime = record.getCellValue(config.startTimeField)
let start = null
// If this record has a fixed time, use it
if (startTime !== null) {
start = new Date(startTime)
// Otherwise use the previous end time
} else {
start = endTime
update.fields[config.startTimeField.name] = endTime
}
// Calculate the end time
let duration = record.getCellValue(config.durationField)
endTime = new Date(start.getTime() + (duration * 1000))
update.fields[config.endTimeField.name] = endTime
updates.push(update)
}
// Update all records
while (updates.length > 0) {
await config.eventsTable.updateRecordsAsync(updates.slice(0, 50))
updates = updates.slice(50)
}
}
// If no time is set for the first record, alert the user
let query = await config.eventsOrderedView.selectRecordsAsync();
if (query.records[0].getCellValue(config.startTimeField) !== null)
// Kick off the process
await main(query.records);
else {
output.clear();
output.text(`❌ ERROR: The first record has no start time. Please set a specific start time for the first event and re-run the script.`)
}
