Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

I need help for solving a problem on the script block

Solved
Jump to Solution
912 7
cancel
Showing results for 
Search instead for 
Did you mean: 

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

1 Solution

Accepted Solutions

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.

See Solution in Thread

7 Replies 7

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.

Capture d’écran (2)

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.

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

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.