AX2012 R3 : Use of SysComputedColumns to build smart AX views

Hi Friends,
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

Browse the view just to make sure the values coming in the table browser.

Now let's create our first simple string computed column and return the field custGroup,just  to get a feel.

Create a new method in the view as shown below:


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

We are all set to see the glimpse of our first and very simple computed column which returns the custGroup.  

Now we will change the textValue method to explore other methods available in SyscomputedColumn class:

ADD : This method is used to add 2 string values. It accepts two string parameters. Modify the method to add the CustGroup and PaymtermId fields as below:


Let's open the view and check the way the strings have been added below:










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

Lets modify our method to Switch on the basis of CustGroup and populate a map based on the case condition.






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'));
 
    return SysComputedColumn::switch(
        SysComputedColumn::returnField(viewName, identifierStr(CustGroup_1) , fieldStr(CustGroup,CustGroup)),
        valueMap,
        SysComputedColumn::returnLiteral('BadCase')
        );
}

CAST : This method is used for conversion between data types. Below is the syntax of the method:

 

Let's modify the method to return the Boolean value of the field PriceIncludeSalesTax as a string with the help of cast method. The code and the result set is shown below

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.

More interesting references on the using SysComputedColumns :
1. MSDN
2. Joris de Gruyter's blog
3. Martin Drab's Blog
4. Krishna Reddy's Blog