I need help for solving a problem on the script block

[Guillaume_MIEMB]

Hello,

Is it possible for a good soul to fly to my rescue please?

I wish to apply the following script on my base with the constraint that the calcul must be reset each time I pass on a new groupfield.

Being a novice I don’t see how to modify my script to apply this constraint.

The script I use is:

let table = base.getTable(‘Data’);
let view = table.getView(‘Grid view’);
let result = await view.selectRecordsAsync({
sorts: [
{field: ‘Identifiant Fournisseur’},
]
});
let runningTotal = 0;
for (let record of result.records) {
runningTotal += record.getCellValue(‘Quantité Collectée’);
await table.updateRecordAsync(record, {
‘Test’: runningTotal,
});
}

[Bill_French]

Guillaume_MIEMBA:

I wish to apply the following script on my base with the constraint that the calcul must be reset each time I pass on a new groupfield.

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,
    });

}

[Guillaume_MIEMB]

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.

[kuovonne]

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.

[Guillaume_MIEMB]

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.

[kuovonne · Accepted solution]

Thank you very much for the screen capture. It looks like “Identifiant Fournisseur” is a linked record field or a lookup field, which is the source of your problem.

Bill’s script was written assuming that “groupName” was a single line text field. However, for a linked record field, getCellValue() returns an array instead of a text string, and comparing arrays works differently from comparing text strings. The same problem exists for lookup fields.

How to proceed depends on whether “Identifiant Fournisseur” is a linked record or a lookup field, and whether or not it will always have exactly one linked record.

If it is a linked record that will always have exactly one linked record, you can try replacing

record.getCellValue("Identifiant Fournisseur")

with

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

in both places where it appears.

The [0].name code pulls out the name from the first record in the array, which is the text string that is displayed.

[Guillaume_MIEMB]

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

[Bill_French]

kuovonne:

Bill’s script was written assuming that “groupName” was a single line text field. However, for a linked record field, getCellValue() returns an array instead of a text string, and comparing arrays works differently from comparing text strings.

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.