I need help for solving a problem on the script block

I think you’re looking for this general approach -

let table = base.getTable(‘Data’);
let view = table.getView(‘Grid view’);
let result = await view.selectRecordsAsync({
    sorts: [
        {field: ‘Identifiant Fournisseur’},
    ]
});

var runningTotal = 0;
var currentGroup = "";
for (let record of result.records) {

    // did we just bump into a new group?
    if (currentGroup != record.getCellValue("groupName")) {
        // new group; reset the total and put a pin in that group name
        runningTotal = 0;
        currentGroup = record.getCellValue("groupName")
    }
    
    // aggregate the running total
    runningTotal += record.getCellValue(‘Quantité Collectée’);

    // update the running total in the table
    // (BTW, this need only be run once for each group)
    await table.updateRecordAsync(record, {
        ‘Test’: runningTotal,
    });

}

Hi Bill.French

Thank you for taking the time to respond to my bottle thrown to the sea.

I tested your approach but it doesn’t work properly because the reset is not made at each new group field sorted as I want to do but at each new record. I have two errors on the script (on the 14th and 17th line) and I think these errors do not make the script work properly but I don’t know how to fix them.

I can share an access to the Table if you need to see what I’m trying to do and how the script works.

A screen shot would be very helpful in this situation.

Are you grouping by the field “Identifiant Fournisseur”? Did you replace “groupName” in Bil’s script with “Identifiant Fournisseur”?

Do you want a running total of “Quantité Collectée” for each record within each group of “Identifiant Fournisseur”?

Does it matter how the records within a group of “Identifiant Fournisseur” are sorted? Usually, when doing a running total, the sort order of the records being summed matters.

Hi Kuovonne

Thank you for your contribution to my post.

You can see bellow how I customize Bill’s script:

let table = base.getTable(‘Data’);

let view = table.getView(‘Grid view’);

let result = await view.selectRecordsAsync({

sorts: [

    {field: 'Identifiant Fournisseur'},

]

});

var runningTotal = 0;

var currentGroup = “”;

for (let record of result.records) {

// did we just bump into a new group?

if (currentGroup != record.getCellValue("Identifiant Fournisseur")) {

    // new group; reset the total and put a pin in that group name

    runningTotal = 0;

    currentGroup = record.getCellValue("Identifiant Fournisseur")

}



// aggregate the running total

runningTotal += record.getCellValue('Quantité Collectée');

// update the running total in the table

// (BTW, this need only be run once for each group)

await table.updateRecordAsync(record, {

    'Test': runningTotal,

});

}

About your questions:

• Do you want a running total of “Quantité Collectée” for each record within each group of “Identifiant Fournisseur”?
  —> Yes, I want a running total of “Quantity Collected” for each record because at certain thresholds, this triggers different formulas in other fields.

• Does it matter how the records within a group of “Identifiant Fournisseur” are sorted? Usually, when doing a running total, the sort order of the records being summed matters.
  —> As you can see on the screen shot, in “Identifiant fournisseur” group, records are sorted by their entrie dates

I also add a screen shot of my table as requested.

Thank you to both. the script is now running perfectly as I was looking for.

record.getCellValue("Identifiant Fournisseur")

record.getCellValue("Identifiant Fournisseur")[0].name

A perfect example that when asking for help, make sure you share all pertinent information to avoid a lot of missteps. :slightly_smiling_face:

I’m glad you sorted it out.