Skip to main content

Aggregating across rows with Power Scripts

A common Power Script task is totaling one Field across every record in a result set, for example, a grand total of order amounts. The trick is that a Power Script runs once per record, so a plain variable would reset on every row. Instead you use the reserved $local variable, which persists across records.

The three steps​

The pattern has three parts in the Query's flow:

  1. Initialize and accumulate in a Power Script Flow Step, keeping the running total on $local.
  2. Flush with a Flush Flow Step, so the first Power Script finishes for every record before anything downstream runs.
  3. Use the finalized total in a second Power Script Flow Step.

Step 1: Initialize and increment​

In the first Power Script Flow Step, start the total at zero once, then add each record's value to it:

if (!$local['runningTotal']) {
    $local['runningTotal'] = 0; // initialize the running total once
}

$local['runningTotal'] += $record['someNumericField']; // accumulate this row's value

Because the same script runs for every record, storing the total in a normal variable would reset it to zero on each row. $local keeps it alive across rows.

Step 2: Flush​

After the first Power Script, add a Flush Flow Step. It forces every prior Power Script to run against all records before the flow continues, so by the time anything after the Flush runs, $local['runningTotal'] holds the complete sum.

Step 3: Use the total​

In a second Power Script Flow Step (after the Flush), read the finalized total and write it to a new Field:

$record['grandTotal'] = $local['runningTotal'];

Every row now carries the grand total, ready to display as its own column.