Pulling data usable data from a linked record in the same table
Welcome to the community, @Mark_Fredrick! :grinning_face_with_big_eyes: This is very doable. First off, here’s how I suggest setting up some of the fields you listed.
For the {Start Time} formula, I recommend something like this:
IF({Item#} = 1, DATETIME_PARSE("START_TIME", "START_TIME_FORMAT"), {Previous End})
For the {Item#} comparison, change the 1 to whatever number you use for your first item in the show. For START_TIME and START_TIME_FORMAT, build a string that represents the show’s start time, and a matching format string using this formatting guide as reference:
Just be aware that unless you also include a timezone indicator in this formula, the time will be calculated relative to GMT, not your local timezone.
There are other ways to specify the start time for the first item, but I’ll stick with this version for this example.
Instead of a button field—which would give you a button in every record—I suggest just clicking the “Run” button in a Scripting app, though a button field could trigger the same script without any issues.
While writing a script to do this, I found that having {End Time} as a normal date field and letting the script calculate its value worked better. The only way for the script to pull an accurate value from {End Time} as a formula field is to let that formula refresh after the {Previous End} is changed, because {Previous End} drives {Start Time}, and {Start Time} drives {End Time}. Waiting for each record to update before calculating the next record adds a massive delay to the whole process. It’s faster to let the script handle the duration calculation and batch-update the records.
Here’s the test table that I started with:
I put the item numbers out of order in the main view on purpose, to demonstrate how the {Previous Item} field from your example isn’t necessary. When using Airtable’s sorting in a view, and then collecting records in the script specifically from that view, Airtable keeps the records in view order, so there’s no need to add that extra field.
Here’s my sorted view before running the script:
And after the script runs:
Here’s the script:
const eventTable = base.getTable("Events")
const sortedView = eventTable.getView("Sorted")
let query = await sortedView.selectRecordsAsync()
let updates = []
let endTime = null
// Cycle through the records
for (let record of query.records) {
let update = {
id: record.id,
fields: {}
}
// If this isn't the first record, set the start time from the previous end time
if (record.name != "1") {
update.fields["Previous End"] = endTime
}
// Calculate the end time
let startTime = endTime === null ? new Date(record.getCellValue("Start Time")) : endTime
let duration = record.getCellValue("Duration")
endTime = new Date(startTime.getTime() + (duration * 1000))
update.fields["End Time"] = endTime
updates.push(update)
}
// Update all records
while (updates.length > 0) {
await eventTable.updateRecordsAsync(updates.slice(0, 50))
updates = updates.slice(50)
}
If the field names you listed above (which I used for my test) are accurate to your base, you should only need to change the first two lines to set the variables for your table and view.
Sorry for the delay. Here’s the revised script.
const eventTable = base.getTable("Events")
const sortedView = eventTable.getView("Sorted")
let query = await sortedView.selectRecordsAsync()
let updates = []
let endTime = null
// Cycle through the records
for (let record of query.records) {
let update = {
id: record.id,
fields: {}
}
let startTime = record.getCellValue("Start Time")
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["Start Time"] = endTime
}
// Calculate the end time
let duration = record.getCellValue("Duration")
endTime = new Date(start.getTime() + (duration * 1000))
update.fields["End Time"] = endTime
updates.push(update)
}
// Update all records
while (updates.length > 0) {
await eventTable.updateRecordsAsync(updates.slice(0, 50))
updates = updates.slice(50)
}
I chose to keep the setup simple. Similar to what you said you tried, I turned {Start Time} into a date field, but I didn’t use a checkbox field to indicate a fixed date. If there’s something in {Start Time}, it’s treated as a fixed date and used to calculate that record’s end time. Otherwise the previous record’s end time is used. Because {Start Time} is no longer a formula depending on {Previous End}, I got rid of the {Previous End} field.
The only stipulation is that the first record in the view must have a start time. Aside from that, you can scatter fixed start times anywhere in the list that you want.
Here’s a look at my test table before running the script:
…and after:
