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

Hi all
I’m trying to do this same thing - creating Run of show with timings for Live event show. I used a script in another discussion and thought I almost had this work… but what happens is the new “end time” doesn’t show up in the next row under the “start time”. I have 3 columns Start TIme (date/time field), Duration (duration field) and End Time (date/time field). Not sure what is going wrong… :(. Any help would be appreciated! THanks! Pam

Have you tried the script that I included above? It does exactly what it shows in the screenshots.

Wow - thank you so much @Justin_Barrett - this script is suuuuuuuuper helpful when I’m planning run of show agendas.

What I’m noticing is that it only works when the start/end time records below do not have any data in them. If I need to tweak a duration- I need to clear all that data and rerun the script to get accurate times again. (Not un-doable, but a little tedious.)

Is there a way to automate this script so anytime I update a duration it will reformulate all the times in the table? Or is there something to add to the script to do that? (I am super new to scripts, which is why I really appreciated how simple this script was to implement and update.)

Thanks again!!
Liz

Welcome to the community, @Alex_Friedman! :smiley: The current design relies on the presence or absence of times in the {Start Time} field to indicate whether the record’s start time is rigid or flexible. The only way to allow for the kind of editing that you describe would be to use another field to mark records with rigid start times differently than the rest. A checkbox field is probably the most compact option, but I sometimes shy away from such fields because they’re easy to accidentally change with an imprecise click. However, you could always hide that field after marking the records with rigid start times. Do you think that would work for your use case?

To make setup easier, I should probably revise the script to create a setup UI for selecting the table and fields involved. Maybe I can wrap that into the next version.

That’s definitely possible, but the problem there lies in the trigger mechanism. The moment you type a single character into any field, Airtable treats that as a change. If an automation is set to trigger when a record has updated, and you want to change a duration of an event from 30 to 45 minutes, the automation would trigger once when you type the “4” in “45”, and again a moment later with the full “45” value. This would just eat up automation runs and possibly confuse the user who sees data changing when you/they may be in the middle of an update.

There are ways to use a formula to delay the trigger until some time after editing is complete, but because of limitations tied to Airtable’s tracking of time using the NOW() function (which would be required in the aforementioned formula), that could be as long as 15 minutes after the update is complete—and that’s assuming that you keep the base open. If you make a change and close the base, the update could possibly take an hour. Not exactly convenient in many cases.

The only other workaround that comes readily to mind would be to change the {Duration} field to a single select, with options set to represent whatever intervals make sense for your use case. Because you’re picking an option from a list and not typing, there would only be a single trigger. I could probably retool the script to work with either option, and just include a very strong recommendation to use a single select for the duration field if you want to use it as part of an automation. :slight_smile:

@Alex_Friedman I just posted a new thread about this (figured it was past time to stop threadjacking this one with script updates and support). The script linked there now supports a checkbox field for marking records with fixed start times, so you can tweak durations and re-run the script without needing to clear fields first.

I also spent some time testing a method of delaying an automation while a field is being edited, which will mean that the automation version—which should be posted to that same new thread in a day or two—will allow you to edit on the fly and see updates fairly quickly.