[D365] Outer join in view with NULL enum column

Sometimes an outer join is necessary in a query to enrich the resulting records with additional data.

A recent requirement to filter records of a Data Entity based on an enum field looked simple (just add ranges) but turned out to be more complex.

Goal: filter records of Data Entity based on an enum field coming from an outer join

Look at the following Query as base for a View:

This Query is used as Data Source for a View.
The View is used as Data Source for a Data Entity.

Problem: can't filter for No AND null

We want to set a range on the field IsSuspended from the DimensionAttrValueLedgerOverride table and only include records where this field has No as value (0/Zero on database).
A simple range would have "No" as value property but won't work in this case because the field can also have NULL since it comes from an outer join and a record must not exists there!

Solution: Computed column on view

To be able to include records with No and NULL, a computed column on the view helps to be able to set the range to this field in the Data Entity later.

Add a new method to your View:

static server str isSuspendedLegalEntity()
    {
        str retStr = SysComputedColumn::returnLiteral(0);
        str mainAccountsViewStr = viewstr(NAMEOFYOURVIEW);
        str dimAttrLedgerOverrideDSStr = identifierStr(DimensionAttrValueLedgerOverride);
        str dimAttrLedgerOverrideSuspendedFieldStr = fieldStr(DimensionAttrValueLedgerOverride, IsSuspended);

        Map comparisonExpressionMap = SysComputedColumn::comparisionExpressionMap();

        comparisonExpressionMap.insert(SysComputedColumn::equalExpression(SysComputedColumn::comparisonField(mainAccountsViewStr, dimAttrLedgerOverrideDSStr, dimAttrLedgerOverrideSuspendedFieldStr), SysComputedColumn::comparisonLiteral(1)), SysComputedColumn::returnLiteral(1));

        return SysComputedColumn::switch('', comparisonExpressionMap, SysComputedColumn::returnLiteral(0));
    }

This code will return 0 if the field IsSuspended is unchecked or NULL, otherwise 1 (for true/Yes).

Add a new field to the View and set the View Method property to the name of your new method. Also set a Name for the field.

After restoring the Data Entity using this View, a range can be added on this new field with just 0 as filter and will work as expected: