Rollup with HAVING clause

[pastaclub]

I am looking for the Airtable equivalent of a HAVING clause in SQL.

I have a view with the following fields (columns):

• Name
• Timestamp
• PointsDelta

Now I want to add another field named PointsTotal which should be automatically computed as the sum of the PointsDelta of all records in that same table having a Timestamp <= the Timestamp of the current row.

I tried to do this with rollup but there is no way to enter that condition. How can I accomplish it?

Example data:

Name  | Timestamp    | PointsDelta | PointsTotal
------+--------------+-------------+------------
foo   | 24/8/24 7:15 | 5           | 5
bar   | 24/8/24 8:21 | -3          | 2
baz   | 24/8/24 9:07 | 10          | 12

[Pascal_Gallais-]

Hello,

You could do that with a script and an automation, question being how you want to trigger this automation.

Here is what I tested:

Field "Test" here is just used to trigger the automation:

Script being (you will need to modifiy the table's name on first line, called "Items" in my example):

let table = base.getTable("Items"); // Replace with your table name

let query = await table.selectRecordsAsync();

let records = [...query.records]; // Create a mutable copy of the records array

// Sort records by "Timestamp" field in ascending order (earliest to latest)

records.sort((a, b) => {

    let dateA = new Date(a.getCellValue("Timestamp"));

    let dateB = new Date(b.getCellValue("Timestamp"));

    return dateA - dateB;

});

// Loop through the sorted records to compute the "PointsTotal" field

let cumulativeTotal = 0;

for (let i = 0; i < records.length; i++) {

    let pointsDelta = records[i].getCellValue("PointsDelta");

    let newTotal;

    if (i === 0) {

        // For the first record, PointsTotal is just PointsDelta

        newTotal = pointsDelta;

    } else {

        // For subsequent records, PointsTotal is PointsDelta + cumulativeTotal

        newTotal = pointsDelta + cumulativeTotal;

    }

    // Update the cumulative total

    cumulativeTotal = newTotal;

    // Update the record with the new "PointsTotal" value

    await table.updateRecordAsync(records[i].id, {

        "PointsTotal": newTotal

    });

}

Now, I guess that you want to compute this idependantly for each day. That's also possible, but it implies a more complex script or a different way to run and trigger the automation (with a "Find records action" on date for example)

Regards,

Pascal