martes, 13 de septiembre de 2011

Obtaining datasource totals, filtered

We would like to see some sub-totals at the top of a form, whose data grid contains all of the data that is unfiltered.

Firstly we have the following method available, but it's not clear to me if it returns to us only the rows loaded in the cache, or if it's the total number of rows over the whole datasource:
Datasource_ds.numberOfRowsLoaded()
The suggestion therefore is to use the SysQuery::countTotal(...) method which has absolutely no documentation whatsoever associated with it. (Furthermore the SysQuery::countLoops(...) contains exactly the same code in my version of Ax 2009. Confusing.)

Next up is applying the query filters for each of the sub-totals to be shown. We need to create a handle to the QueryBuildRange and in my example below apply a filter on the EVE_MembershipStatus enum field.

Now here's my final problem. My form could be 'stand alone' or it could be called from another form and have the datasource automatically filtered, in my case by ProjId - from the Projects table. I've still not worked out how to apply the activeLinking dynamically but for now here is the science:
public void executeQuery()
{
    QueryBuildDataSource    queryBuildDataSource;
    QueryBuildRange         memStatus, callerRng;
    int                     actTot, pendTot, susTot;
    Common                  callerRecord = element.args().record();  
    FieldId                 fIdProjId = fieldnum(ProjTable, ProjId); 
    ;

    queryBuildDataSource    = EVE_ProjCustTable_ds.query().dataSourceTable(tablenum(EVE_ProjCustTable));

    // Create range to filter on
    memStatus   = SysQuery::findOrCreateRange(queryBuildDataSource, fieldnum(EVE_ProjCustTable, EVE_MembershipStatus));
    memStatus.enabled(true);
    if (callerRecord.RecId && callerRecord.TableId == tablenum(ProjTable))
    {   // Called from a related form (ProjTable).
        callerRng           = SysQuery::findOrCreateRange(queryBuildDataSource, fieldnum(EVE_ProjCustTable, ProjId));
        callerRng.value     (queryValue(callerRecord.(fIdProjId)));
        callerRng.enabled(true);
    }
    
    // Calculate our totals - This is NOT a 'select count(*)' 
    //   but will iterate over all rows from the server to the client!
    memStatus.value(queryValue(EVE_RegistrationStatus::Active));
    actTot  = SysQuery::countTotal(new QueryRun(EVE_ProjCustTable_ds.query()));

    memStatus.value(queryValue(EVE_RegistrationStatus::Waiting));
    pendTot = SysQuery::countTotal(new QueryRun(EVE_ProjCustTable_ds.query()));
    memStatus.value(queryValue(EVE_RegistrationStatus::Terminate));
    susTot  = SysQuery::countTotal(new QueryRun(EVE_ProjCustTable_ds.query()));
    
    // Present our work
    TotalActTxt.text(strFmt("%1", actTot));
    TotalPenTxt.text(strFmt("%1", pendTot));
    TotalSusTxt.text(strFmt("%1", susTot));

    // Remove previously applied filters
    memStatus.enabled(false);
    if (callerRecord.RecId > 0) callerRng.enabled(false);
    
    super();
}
Put quite simply my form is a little slower now, so I'd use a different approach (select count(RecId)...) for bigger record sets! Finally the example code above is executed within the executeQuery() method and so the sub-totals won't get updated when new rows are added or existing ones modified (hint: overwrite the x_ds.delete() and x_ds.write() methods).

Edit: SysQuery::countTotal doesn't work for temporary datasources.  See here.

No hay comentarios:

Publicar un comentario