Wednesday, March 6, 2019

Computed column for union values from multiple outer joined data sources in view

Let's say you have a multiple outer joined data sources with similar fields, which can be merged by union to one target field.

PSAActualEntity  data entity (in D365) can be a good example, if you add ProjCostTrans, ProjItemTrans, and ProjEmplTrans to its root data source table ProjTransPosting, which has appropriate relations to each of them.



All aforementioned have CategoryId field, which can be present in one table only at a time.


 Let's place a new computed string field with the following static method with a nested if.


private static server str transCategoryId()
    {
        str         sRet;
        tableName   viewName                = identifierStr(avrPSAActualEntity);
        str         cCategoryProjCostTrans  = SysComputedColumn::comparisonField(viewName,
                                                                                    identifierStr(ProjCostTrans),
                                                                                    fieldStr(ProjCostTrans, CategoryId));
        str         cCategoryProjItemTrans  = SysComputedColumn::comparisonField(viewName,
                                                                                    identifierStr(ProjItemTrans),
                                                                                    fieldStr(ProjItemTrans, CategoryId));
        str         cCategoryProjEmplTrans  = SysComputedColumn::comparisonField(viewName,
                                                                                    identifierStr(ProjEmplTrans),
                                                                                    fieldStr(ProjEmplTrans, CategoryId));
        str         sCategoryProjCostTrans  = SysComputedColumn::returnField(viewName,
                                                                                    identifierStr(ProjCostTrans),
                                                                                    fieldStr(ProjCostTrans, CategoryId));
        str         sCategoryProjItemTrans  = SysComputedColumn::returnField(viewName,
                                                                                    identifierStr(ProjItemTrans),
                                                                                    fieldStr(ProjItemTrans, CategoryId));
        str         sCategoryProjEmplTrans  = SysComputedColumn::returnField(viewName,
                                                                                    identifierStr(ProjEmplTrans),
                                                                                    fieldStr(ProjEmplTrans, CategoryId));
        sRet =
            SysComputedColumn::if(SysComputedColumn::isNotNullExpression(cCategoryProjCostTrans),
                                    cCategoryProjCostTrans, 
                                    SysComputedColumn::if(SysComputedColumn::isNotNullExpression(cCategoryProjItemTrans),
                                                            sCategoryProjItemTrans,
                                                            SysComputedColumn::if(SysComputedColumn::isNotNullExpression(cCategoryProjEmplTrans),
                                                                                sCategoryProjEmplTrans,
                                                                                SysComputedColumn::returnLiteral('')
                                                                                )
                                                        )
                                );

        return sRet;
    }



No comments: