View is a powerful feature in AX which can benefit development and business modelling. Once created, you can reuse it without need for writing/modelling complex queries and joins again and again. Adding to it\’s beauty is the ability to create computed columns using X++ code. Now that\’s a deadly combo!!
Code for computed column depicts a SQL view query definition. Hence the way of writing X++ code inside a computed column method is bit different. To help us tackle this challenge and model the code effectively, Microsoft provides us with an out of the box class: SysComputedColumn. In this post, lets take a look at some of the basic operations which we can perform using this class.
Thumb rule is, the methods written for computed column computation should be declared as static and in order to access a field we need to use SysComputedColumn::returnField() method.
For example if we have to access the value of field CustGroup from CustGroup table, then a normal X++ code is custGroup.custGroup. But inside a computed column method we need to use the SysComputedColumn::returnField() method to access this field. This is shown later in this post.
Below is the returnField method definition. Note, it takes three parameters:
So lets play with CustGroup table and create a simple view out of it:
- Right click on AOT >> DataDictionary >> Views and create a new view
- Add datasource \”CustGroup\”
- Add 2 fields, CustGroup and PaymTermId
Now let\’s create our first simple string computed column and return the field custGroup,just to get a feel.
Now we add a new string computed column field in the view and link it to this method
To do this, Right click on the fields >> New >> String computed column
In the properties for this new field >> View method >> Select the method we created above
Now we will change the textValue method to explore other methods available in SyscomputedColumn class:
IF: This method accepts three parameters as shown below and returns the equivalent SQL view design script
Let\’s create a condition: if the CustGroup is 10 then return the value of CustGroup , else return the value as 0. The modified method looks as below:
Lets open the view, notice that the value is shown only in the record having custGroup as \”10\” as per the condition:
SWITCH: Below is the syntax of the function. It accepts a Case expression , Map (for storing the values) and the default expression
Below is the code template:
public static server str textValue()
tableName viewName = tableStr(CustGroupView);
Map valueMap = new Map(Types::String , Types::String);
valueMap.insert(\”10\”, SysComputedColumn::returnLiteral(\’Value 10\’));
valueMap.insert(\”20\”, SysComputedColumn::returnLiteral(\’Value 20\’));
valueMap.insert(\”30\”, SysComputedColumn::returnLiteral(\’Value 30\’));
SysComputedColumn::returnField(viewName, identifierStr(CustGroup_1) , fieldStr(CustGroup,CustGroup)),
CAST : This method is used for conversion between data types. Below is the syntax of the method:
This is just a sneak peek into the methods available in SysComputedClass.There are more methods in this class using which you can make powerful SQL views using X++ and deliver amazing customer experiences.