AX2012 R3: Mashup - Computed columns + table inheritance in views

Hi Friends,
Dynamics AX has amazing technical offerings. The real benefit of such good features is leveraged when they are combined together to create an amazing output.

In this post, let me walk-through a scenario where we create a computed column whose logic is based on a bunch of inherited tables. A basic understanding of Table inheritance, Queries, Joins, Views, Computed columns and Product attributes will make this post easy to understand.

In standard AX we can create/define product attributes applicable on a product. Technically the attributes data is stored in a set of tables which use table inheritance. EcoResValue is the parent table of these tables as shown below:

All the attribute values of string types are stored in EcoResTextValue,  Integer values are stored in EcoResIntValue table and so on. Each child table stores it's relevant data in there relevant fields.

Goal : Create a view with a computed column to show the attribute values of different data types in a single text column.

There is a standard AX display method [\Data Dictionary\Tables\EcoResValue\Methods\getValueAsText()] which fetches the value from the respective child table. We will actually be simulating the same in our computed column code. In this method, switch is on the basis of InstanceRelationType and case on tableId. Value is returned from that respective table:

To do this we need to first model a query having all the above dataSources linked to parent table. So the root table will be EcoResValue and then all the other tables with the link type of outer join.

Once the query is created let's create a new view and add the above query as data-source.

Next we create a new method for the computed column field. In that we have to:

• Define the switch based on field Instance relation type from EcoResValue
• Cases will be on the tableID of each child table
• Return the value from the respective field of the child table.
• In case there are more then one field storing the values in the child table, then we concatenate those fields.
• We need to typecast the field values to string values
• Where units are used, we need to concatenate the unit text from the respective table.

In one of my previous post, I showed how to write Switch, Add and Cast methods using a SysComputedColumn class. It can be referred here.

Each value in the map is inserted for a particular tableID. Sample code to return that value from it's corresponding table is shown below:

We define the switch on InstanceRelationType field as shown below:

When all the switch cases are combined we get the below method:

Now lets add a computed string column field in the view and check out the computed values.

EcoResIntValue record value:

EcoResFloatValue record value :

EcoResDateTimeValue  record value: (Here I have not applied any time zone offset. Hence the time is different.)

EcoResCurrencyValue record value :

EcoResBooleanValue record:

We see that we have retrieved values from various tables of different data types, all coming together, in one column using the computed column concept. Now we can use this view throughout the system. Some possibilities:

• Effective use in reports. No need to write heavy joins in data provider class.
• Can be used to set-up document types.
• Can be used as a query and then extended outside AX using Dynamics AX Office AddIns.
• Can be used as display methods on forms where we want to show attribute values.

There are many more possible solutions where such views can be extended and can deliver awesomeness to customers using Dynamics AX. 

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