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.