Pulling data usable data from a linked record in the same table

This seems like something super simple for a script but I’m not code savvy, so I’m stumped. I’m far from an Airtable novice, just not into Scripts. I’m trying to Create a run of show base for live events where the Start of one record is populated by the end time of the record that precedes it in the show. Each record has a “Item #” as it key field, so I tried linking records the old fashion way in the table manually, but this is tedious AND it won’t let me do any calculations with the data I pull because it’s circular.

I have a few important fields:
Item# - unique key, all records sort by this.
Start time - Ideally this is an IF formula, so that if it’s the first line in the show, it doesn’t try to find the previous item.
Duration - Manual entry
End time - Formula= Start Time + Duration
Previous Item - Formula= Item# - 1
Previous End - thing I’m trying to get

My logic is this: Script that find record with Item# that matches Previous Item, then takes the Previous Item’s End time and populated the Previous End field without actually linking the 2 records. I would probably create an Update button field to run the script so whenever I change the order or timing I can just re-run it.

any thoughts would be helpful!

Welcome to the community, @Mark_Fredrick! :smiley: 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:

Screen Shot 2021-01-02 at 6.37.52 PM

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:

Screen Shot 2021-01-02 at 6.40.07 PM

And after the script runs:

Screen Shot 2021-01-02 at 6.40.35 PM

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.

1 Like

Justin, I really appreciate this thorough response, and it really helped. Sorry I’m so late in responding, but as soon as I had received your help and implemented into the base I was working on, the needs completely changed and I had to move on. However, going forward this is something I will want to learn better and customize on my own, but I’m still trying to understand all the moving parts in the script.

I’m not completely following the logic you are using with the variables. I tried to drop into a different base and make a small adjustment, but it kept giving me errors.

Here is a scenario that comes up a lot, and if I understood scripting better I could just tweak your script and make it work, but I’m not. So here goes… we don’t always have just 1 fixed start time, like “item #” = 1 that I used in my first example. Sometimes there are different fixed points in the show, and all the other line items flow from there. Like if lunch HAS TO BE at 1pm and end at 2pm, but then the afternoon would flow from duration calculations. In the past, when I’m the only person entering data into the base, I will just create a “Fixed Start” and enter the time there if it needs to be fixed, then my “Start Time” field is a formula IF(“Fixed Start”=blank(),“Previous End”, “Fixed Start”).

With that said, I’m trying build a Run of Show template that my less tech savvy clients can use to build shows that I will then run for them, and I’ve already seen some blank stares when I explain the whole “if it’s a fixed start time do this, and if it’s a variable, just let the script do it”.

I was trying the following, but it didn’t work… I made the Start Time a date field, added a Checkbox field “Fixed”, and tried to tweak your formula so that if “fixed” is checked, the script does nothing to that “Start Time”, but first, I couldn’t get the script to see the condition for “fixed”, and second, I was getting an error when I moved the variables around so that the script filled “Start Time” with it’s result instead of “Previous End”

does this make sense?

@Justin_Barrett forgot to @mention you

Yes, that makes sense, and it’s possible to tweak the script to account for variable fixed times. Unfortunately I don’t have a lot of free time at the moment, but I’ll add this to my list and get to it as soon as I can, though I can’t promise how soon that would be. If anyone else is able to step in and help, be my guest.

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:

Screen Shot 2021-02-06 at 10.43.15 PM

…and after:

Screen Shot 2021-02-06 at 10.44.29 PM