web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
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.

  • delarosaau Profile Picture
    delarosaau 70 on at

    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

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 240,799 Most Valuable Professional on at

    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

    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);
    }

  • Martin Dráb Profile Picture
    Martin Dráb 240,799 Most Valuable Professional on at

    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.

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

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 475 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 399 Super User 2026 Season 2

#3
CU10121822-0 Profile Picture

CU10121822-0 374

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP
License usage reporting (in-app and PPAC) with Entra dynamic groups

Product updates

Dynamics 365 release plans