Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Computed Column Switch...
Finance | Project Operations, Human Resources, ...
Answered

Computed Column Switch Statement

(0) ShareShare
ReportReport
Posted on by delarosaau 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 230,605 Most Valuable Professional 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 230,605 Most Valuable Professional 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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,904 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,605 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans