View computed column to get OR for two flags

Nothing new, just to reiterate the basics.

Say, we need to add a computed column as a result of OR function over two other columns of Boolean type:myPPOFirmDisableForSalesLine and myPPOFirmDisableForTransferLine.

Create a new field myPPOFirmDisable as Enum Computed field

and point it to the following method.

private static server str myPPOFirmDisable(int _tableNum)
{
    DictView    ctView      = new DictView(tableNum(myPeggedRefTransOnHoldUnionView));
    str         sTransType1 = SysComputedColumn::comparisonField(identifierStr(myPeggedRefTransOnHoldUnionView), identifierStr(mySalesTableOnHoldView),  fieldStr(mySalesTableOnHoldView, myPPOFirmDisableForSalesLine));
    str         sTransType2 = SysComputedColumn::comparisonField(identifierStr(myPeggedRefTransOnHoldUnionView), identifierStr(mySalesTableOnHoldView),  fieldStr(mySalesTableOnHoldView, myPPOFirmDisableForTransferLine));
    str     sRet        = '';

    sRet =
            SysComputedColumn::or2
            (
                SysComputedColumn::equalExpression(sTransType1, SysComputedColumn::returnLiteral(NoYes::Yes)),
                SysComputedColumn::equalExpression(sTransType2, SysComputedColumn::returnLiteral(NoYes::Yes))
            );
    sRet =
        SysComputedColumn::if(sRet, SysComputedColumn::returnLiteral(NoYes::Yes), SysComputedColumn::returnLiteral(NoYes::No));

    return sRet;
}

The whole point is to create a string with an SQL statement as a result in static method. There are a lot of standard functions in SysComputedColumn class aiming to help with syntax.

As you can see in this example it is not enough to get a logical comparison result at the first sRet assigning. The latter must be converted explicitly to an SQL string as it goes at the second step.

Finally we get the following string as a part of the whole SQL statement.

CASE WHEN (T1.MYPPOFIRMDISABLEFORSALESLINE = 1) OR (T1.MYPPOFIRMDISABLEFORTRANSFERLINE = 1) THEN 1 ELSE 0 END

bugs in SysLookupMultiSelectGrid class and form

You can find a lot of example about how to support multiple selection lookups. AX 2012/D365 provide us with SysLookupMultiSelectGrid class and form to implement such scenarios.

There are two bugs however still existing in the standard code.

SysLookupMultiSelectGrid class, method lookup() must be as follows in order to refresh Query in case QueryRun is given.

// the standard method does not update the query in case if queryrun is given
public static void wblLookup(Query _query, FormStringControl _ctrlIds, FormStringControl _ctrlStrs, container _selectField, queryRun _queryRun = null)
{
    SysLookupMultiSelectGrid    lookupMS = new SysLookupMultiSelectGrid();

    lookupMS.parmCallingControlId(_ctrlIds);
    lookupMS.parmCallingControlStr(_ctrlStrs);
    lookupMS.parmQuery(_query);
    lookupMS.parmQueryRun(_queryRun);

    if(_queryRun)
    // Begin: Alexey Voytsekhovskiy
    {
        lookupMS.parmQuery(_queryRun.query());
    }
    // End: Alexey Voytsekhovskiy
    lookupMS.parmSelectField(_selectField);
    lookupMS.run();
}

SysLookupMultiSelectGrid form, method executeQuery() on common data source must be as follows in order not to consider referenced data sources, which may come with a given query.

public void executeQuery()
{
    QueryRun qr;
    Query lookupMultiSelectQueryCopy = new Query(lookupMS.parmQuery());
    FormRun formRun;
    FormDataSource formDataSource;
    int dsCount, i = 1;
    Common formDataSourceCursor, queryRunCursor;

    // Always use the query defined on the SysLookupMultiSelectGrid class. Note that a copy is used
    // so that any modifications made to the Query by the Form at runtime aren't fed back through
    // the next time the lookup is presented to the user. (The Query is used to define which fields
    // are present on the Form during lookup construction. Therefore, if any fields are added at runtime
    // by the Forms engine, duplicated or non-original-Query defined fields may be presented on the
    // 2nd or later presentations of the lookup if a copy isn't used.)
    this.query(lookupMultiSelectQueryCopy);

    // check if user has set any queryrun. If yes, that means the cursors are set by user explicitly,
    // usually the case where query contains tmp tables and needs to be populated.
    qr = lookupMS.parmQueryRun();
    if(qr)
    {
        formRun = this.formRun();
        dsCount = formRun.dataSourceCount();

        // get data source from query run, get the cursor and set it on the form data source cursor.
        for(i = 1; i<=dsCount; i++)
        {
            formDataSource = formRun.dataSource(i);
            if(formDataSource
                            // Begin: Alexey Voytsekhovskiy
                            // we don't need a reference data source here!
                            && !formDataSource.isReferenceDataSource()
                            // End: Alexey Voytsekhovskiy
                            )
            {
                // get form data source cursor and set the queryrun cursor as data on it.
                formDataSourceCursor = formDataSource.cursor();
                queryRunCursor = qr.get(formDataSourceCursor.TableId);
                if(queryRunCursor)
                {
                    if(queryRunCursor.isTempDb() || queryRunCursor.isTmp())
                    {
                        formDataSourceCursor.setTmpData(queryRunCursor);
                    }
                    else
                    {
                        formDataSourceCursor.data(queryRunCursor);
                    }

                }
            }
        }
    }

    super();
}

Date time range

Just for my records

 (dayRange(-5, 0))

 Taken from here

Thank you Martin!