You’re offline. This is a read only version of the page.
Announcements
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.
Share
Reportprivate 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.
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.
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? :)
All responses (
Answers (
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);
}
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; }
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,186
Super User 2024 Season 2
#2
Martin Dráb
227,996
Super User 2024 Season 2
#3
nmaenpaa
101,148