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

Development and integrations Scripting app
#1

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!

#2

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