Form Running Totals, Ax 2009

Question

Is there an example anywhere of a form that performs running totals in a column located within a grid. The user ordering and filtering of the grid would affect the running totals column.

I can easily perform the above if it was ordering only by transaction date, but including the user ordering and filtering I presume that we would have to use the datasource range() and rangecount() functions (see SysQuery::mergeRanges() for an example) then iterate over these to apply the filtering, then include the dynalinks. The same for the ordering, albeit this is now more complicated.

Any suggestions appreciated. Any appreciations suggested (as in: vote the question up!).

Answer 1

You could implement it as a form datasource display method using this strategy:

1. Copy the form's datasource query (no need for SysQuery::mergeRanges):

   QueryRun qr = new QueryRun(ledgerTrans_qr.query());

2. Iterate and sum over your records using qr, stop after the current record:

   while (qr.next()) { lt = qr.getNo(1); total += lt.AmountMST; if (lt.RecId == _lt.RecId) break; }

   This could be made more performant if the sorting order was fixed (using sum(AmountMST) and adding a where constraint).

3. Return the total

This is of cause very inefficient (subquadratic time, O(n^2)).

Caching the results (in a map) may make it usable if there are not too many records.

Update: a working example.

Answer 2

Any observations or criticisms to the code below most welcome. Jan's observation about the method being slow is still valid. As you can see, it's a modification of his original answer.

//BP Deviation Documented
display AmountMST XXX_runningBalanceMST(LedgerTrans _trans)
{
    LedgerTrans localLedgerTrans;
    AmountMST   amountMST;
    ;
    localLedgerTrans    = this.getFirst();
    while (localLedgerTrans)
    {
        amountMST           += localLedgerTrans.AmountMST;
        if (localLedgerTrans.RecId == _trans.RecId)
        {
            break;
        }
        localLedgerTrans    = this.getNext();
    }
    return amountMST;
}