Table-Group-All pattern filtering in forms

When we need to support business scenarios with different types of relations for the same table, it comes to using enums like Table-Group-All. You can find the biggest example, I believe, in PriceDiscTable.

This post is to explain the same approach in a simpler way.

Say, we have a table with two fields defining different possible values for two other relation value fields.

The whole idea is in adding a range with an expression to the form query.

The challenge here is to support correct filtering on the form and combine it with the user filters, after it is open for a particular record. In our case it is a customer that can have a value in Sales commission group (or not) and some matching ZIP code in its primary business address.

void  reSelect()
{
    str filter;

    element.cleanDSQuery();
    filter = this.buildViewAllCustomerFilter();
    blockCustomerGroupRelation.value(filter);

    mySalesGroupAssignation_DS.executeQuery();
    mySalesGroupAssignation_DS.queryRun().saveUserSetup(false);
    mySalesGroupAssignation_DS.refresh();
}

This method is supposed to be triggered from linkActive() of the data source. First we clear the original query from possible dynamic links and ranges and create our new range for an expression. You can make it visible to see the final expression for debugging.

private void cleanDSQuery()
{
    mySalesGroupAssignation_DS.query().dataSourceTable(tableNum(mySalesGroupAssignation)).clearDynalinks();

    mySalesGroupAssignation_DS.query().dataSourceTable(tableNum(mySalesGroupAssignation)).clearRanges();
    blockCustomerGroupRelation  = mySalesGroupAssignation_ds.query().dataSourceTable(tableNum(mySalesGroupAssignation)).addRange(fieldNum(mySalesGroupAssignation, myCustomerTableGroupAll));
    blockCustomerGroupRelation.status(RangeStatus::Hidden);
}

Then we create a complex range expression for two fields in two methods.

private str buildViewAllCustomerFilter()
{
    str viewAllAgreementFilter;


    viewAllAgreementFilter = '((';
    viewAllAgreementFilter += element.buildFilterCustomer();
    viewAllAgreementFilter += ') && (';
    viewAllAgreementFilter += element.buildFilterZipCode();
    viewAllAgreementFilter += '))';

    return viewAllAgreementFilter;
}

We add Group based condition only if Sales commission value is set up for a given customer.

private str buildFilterCustomer()
{
    str                 filter;
    // (
    filter = '(';
    // (myCustomerTableGroupAll = Table and myCustomerGroupRelation = account code)
    // OR
    // (myCustomerTableGroupAll = All)
    // AND
    //

    filter += strFmt('((%1.%2==%5) && (%1.%3=="%4")) || (%1.%2==%6)',
                        mySalesGroupAssignation_DS.queryRun().query().dataSourceTable(tableNum(mySalesGroupAssignation)).name(),  // 1
                        fieldStr(mySalesGroupAssignation, myCustomerTableGroupAll),                                               // 2
                        fieldStr(mySalesGroupAssignation, myCustomerGroupRelation),                                               // 3
                        queryValue(custTableFrom.AccountNum),                                                                       // 4
                        any2int(TableGroupAll::Table),                                                                              // 5
                        any2int(TableGroupAll::All)                                                                                 // 6
                        );

    if(custTableFrom.CommissionGroup)
    {
        // OR
        // (myCustomerTableGroupAll = Group and myCustomerGroupRelation = sales commission group)

        filter += strFmt(' || ((%1.%2==%5) && (%1.%3=="%4"))',
                        mySalesGroupAssignation_DS.queryRun().query().dataSourceTable(tableNum(mySalesGroupAssignation)).name(),  // 1
                        fieldStr(mySalesGroupAssignation, myCustomerTableGroupAll),                                               // 2
                        fieldStr(mySalesGroupAssignation, myCustomerGroupRelation),                                               // 3
                        queryValue(custTableFrom.CommissionGroup),                                                                  //4
                        any2int(TableGroupAll::GroupId)                                                                              // 5
                        );
    }

    filter += ')';
    return filter;

}

private str buildFilterZipCode()
{
    str                 filter;
    // (
    // (myZipCodeTableGroupAll = GroupId and myZipCodeGroupRelation = myBusinessAddressZipCode)
    // OR
    // (myZipCodeTableGroupAll = All)

    filter = strFmt('(((%1.%2==%5) && (%1.%3=="%4")) || (%1.%2==%6))',
                        mySalesGroupAssignation_DS.queryRun().query().dataSourceTable(tableNum(mySalesGroupAssignation)).name(),  // 1
                        fieldStr(mySalesGroupAssignation, myZipCodeTableGroupAll),                                               // 2
                        fieldStr(mySalesGroupAssignation, myZipCodeGroupRelation),                                               // 3
                        queryValue(custTableFrom.myBusinessAddressZipCode().myZipGroupId),                                       // 4
                        any2int(myGroupAll::GroupId),                                                                              // 5
                        any2int(myGroupAll::All)                                                                                 // 6
                        );                                                                               // 6

    return filter;

}

You can easily adapt this code to your own scenario. Just be meticulous with the syntax of the extended range expression.