Skip to main content

Notifications

Dynamics 365 Community / Forums / Finance forum / Computed Column Switch...
Finance forum
Answered

Computed Column Switch Statement

Posted on by 70

Dear Dynamics folk,

I could use your assistance, if you would, to clean up this switch statement that I apparently seem unable to arrange successfully.

Up until line 24 was in place prior and is working as expected.  My added code, line 26 and down, is causing me much grief.

We have a custom Inquiry page that is populated by all these computed columns and what I'm attempting to do is:

If the transportation mode of a contract is RAIL then I need to check the Equipment Owner.  The Equipment Owner will have a value of 0 through 3 and it is based upon the value which I wish I select an additional string to addend to the column data.

private static server str GetPriceAndUom()
    {
        var contractLineView = tableStr(BRKContractLineView);
        var space = SysComputedColumn::returnLiteral(" ");
        List priceAndUom = new List(Types::String);
        var price = 
            SysComputedColumn::cast(
                SysComputedColumn::cast(
                        SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine),fieldStr(BRKContractLine, Price)),
                            "decimal(10, 4)"), 
                                "nvarchar(30)");
        priceAndUom.addEnd(price);
        priceAndUom.addEnd(space);
        priceAndUom.addEnd(SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine), fieldStr(BRKContractLine, PriceUnitOfMeasure)));
        priceAndUom.addEnd(SysComputedColumn::returnLiteral("\n"));
        priceAndUom.addEnd(SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine), fieldStr(BRKContractLine, FreeOnBoard)));
        priceAndUom.addEnd(space);
        priceAndUom.addEnd(SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine), fieldStr(BRKContractLine, Mode)));
        priceAndUom.addEnd(space);
        
        var priceType = SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine), fieldStr(BRKContractLine, PriceType));
        var estimated = SysComputedColumn::returnLiteral("EST");
        var firm = SysComputedColumn::returnLiteral("FIRM");
        priceAndUom.addEnd(SysComputedColumn::if(SysComputedColumn::equalExpression(priceType, SysComputedColumn::returnLiteral("ESTIMATED")), estimated, firm));
        
        priceAndUom.addEnd(SysComputedColumn::returnLiteral("\n"));
        var mode = SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine), fieldStr(BRKContractLine, Mode));
        var equipmentOwner = SysComputedColumn::cast(SysComputedColumn::returnField(contractLineView, identifierStr(BRKShippingScenario), fieldStr(BRKShippingScenario, EquipmentOwner)), "nvarchar(30)");
        var prv = SysComputedColumn::returnLiteral("PRV");
        var rr = SysComputedColumn::returnLiteral("RR");
        var shp = SysComputedColumn::returnLiteral("SHP");
        var vnd = SysComputedColumn::returnLiteral("VND");

        Map valueMap = new Map(Types::String,Types::String);
        valueMap.insert("0", prv);
        valueMap.insert("1", rr);
        valueMap.insert("2", shp);
        valueMap.insert("3", vnd);
        var tackOnEquipmentOwner = 
            SysComputedColumn::switch(
            equipmentOwner,
            valueMap,
            "NO MATCH");
        priceAndUom.addEnd(SysComputedColumn::if(SysComputedColumn::equalExpression(mode, SysComputedColumn::returnLiteral("RAIL")), tackOnEquipmentOwner, "MODE NOT RAIL"));

        return SysComputedColumn::addList(priceAndUom);
    }

Hoping it's something simple, and probably looking right at me, but I've been messing with this since Friday and about to pull my hair out!  Many thanks.

  • Martin Dráb Profile Picture
    Martin Dráb 227,996 Super User 2024 Season 2 on at
    RE: Computed Column Switch Statement

    You can't write everything in SQL, because something you need to refer to are table aliases that you definitely shouldn't hard-code (e.g. T4).

    You should also avoid hard-coding any object names (tables, fields etc.). If you use intrinsic functions such as fieldName(), the compiler will check if such objects exist and it'll include them in cross-references. And there are cases where names in application differ from names in database (such as Level field in EcoResCategory table, which is called LEVEL_ in database) and the application offers methods translating the names (e.g. SysDictTable::fieldnameSQL()).

    But I agree that doing certain things through SysDictColumn methods feels wrong. It would result in code that is very difficult to read while providing no benefit.

  • delarosaau Profile Picture
    delarosaau 70 on at
    RE: Computed Column Switch Statement

    For the record, here's my updated code that actually compiles (line 26 and down), using Martin's code above (THANK YOU SIR!).  

    Translating everything into T-SQL... begs the question, why can't we just write SQL? :)

    private static server str GetPriceAndUom()
        {
            var contractLineView = tableStr(BRKContractLineView);
            var space = SysComputedColumn::returnLiteral(" ");
            List priceAndUom = new List(Types::String);
            var price = 
                SysComputedColumn::cast(
                    SysComputedColumn::cast(
                            SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine),fieldStr(BRKContractLine, Price)),
                                "decimal(10, 4)"), 
                                    "nvarchar(30)");
            priceAndUom.addEnd(price);
            priceAndUom.addEnd(space);
            priceAndUom.addEnd(SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine), fieldStr(BRKContractLine, PriceUnitOfMeasure)));
            priceAndUom.addEnd(SysComputedColumn::returnLiteral("\n"));
            priceAndUom.addEnd(SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine), fieldStr(BRKContractLine, FreeOnBoard)));
            priceAndUom.addEnd(space);
            priceAndUom.addEnd(SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine), fieldStr(BRKContractLine, Mode)));
            priceAndUom.addEnd(space);
            
            var priceType = SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine), fieldStr(BRKContractLine, PriceType));
            var estimated = SysComputedColumn::returnLiteral("EST");
            var firm = SysComputedColumn::returnLiteral("FIRM");
            priceAndUom.addEnd(SysComputedColumn::if(SysComputedColumn::equalExpression(priceType, SysComputedColumn::returnLiteral("ESTIMATED")), estimated, firm));
            
            priceAndUom.addEnd(SysComputedColumn::returnLiteral("\n"));
            var mode = SysComputedColumn::cast(SysComputedColumn::returnField(contractLineView, identifierStr(BRKContractLine), fieldStr(BRKContractLine, Mode)),
                                    "nvarchar(30)");
            var equipmentOwner = SysComputedColumn::returnField(contractLineView, identifierStr(BRKShippingScenario), fieldStr(BRKShippingScenario, EquipmentOwner));
    
            List containerList = new List(Types::Container);
            container privateFleet = [0, "'PRV'"];
            container railroad = [1, "'RR'"];
            container shipper = [2, "'SHP'"];
            container vendor = [3, "'VND'"];
            containerList.addEnd(privateFleet);
            containerList.addEnd(railroad);
            containerList.addEnd(shipper);
            containerList.addEnd(vendor);
    
            var tackOnEquipmentOwner = BRKContractLineView::mySwitchOrdered(equipmentOwner, containerList, "'NO MATCH'");
    
            priceAndUom.addEnd(SysComputedColumn::if(SysComputedColumn::equalExpression(mode, SysComputedColumn::returnLiteral("RAIL")), tackOnEquipmentOwner, SysComputedColumn::returnLiteral("")));
    
            return SysComputedColumn::addList(priceAndUom);
        }

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 227,996 Super User 2024 Season 2 on at
    RE: Computed Column Switch Statement

    I've also run into the problem that SysComputedColumn::switch() doesn't respect the order. I solved it by implementing my own method:

    /// 
    /// Creates a searched case expression with the given order of expressions.
    /// 
    /// A string value that contains the control expression.
    /// An instance of the List class that contains containers,
    /// each containing a comparison expression and a return expression.
    /// A string value that contains a default expression.
    /// A string expression formatted as a searched case expression.
    public static str mySwitchOrdered(str _controlExpression, List _comparisonExpressionList, str _defaultExpression)
    {
    	ListEnumerator enumerator;
    	str caseExpression = '';
    	str condition, action;
    
    	caseExpression ='CASE '   _controlExpression;
    	enumerator = _comparisonExpressionList.getEnumerator();
    	while (enumerator.moveNext())
    	{
    		[condition, action] = enumerator.current();
    		
    		caseExpression  = ' WHEN '   condition   ' THEN '   action;
    	}
    	caseExpression  = ' ELSE '   _defaultExpression;
    	caseExpression  = ' END';
    
    	return caseExpression;
    }

  • delarosaau Profile Picture
    delarosaau 70 on at
    RE: Computed Column Switch Statement

    A picture is a thousand words.  Here's the screen where I'm attempting to tack on that bit of messaging for the user to view from the Inquiry screen.
    2772.pastedimage1603710659464v1.png

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 9th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Dynamics 365 Community Newsletter - August 2024

Catch up on the latest D365 Community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,186 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 227,996 Super User 2024 Season 2

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans