Skip to main content

Rollup with HAVING clause

  • August 26, 2024
  • 1 reply
  • 23 views
P
Forum|alt.badge.img+2

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

Pascal_Gallais-
Forum|alt.badge.img+21

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
P
Terms & ConditionsAccessibility statement