Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Rollup with HAVING clause

Topic Labels: Base design Data Formulas Views
550 1
cancel
Showing results for 
Search instead for 
Did you mean: 
pastaclub
4 - Data Explorer
4 - Data Explorer

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

1 Reply 1

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:

Pascal_Gallais_0-1724687420291.png

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

Pascal_Gallais_1-1724687478232.png

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 (=== 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