Addicted2AX / Addicted2SQL

A very useful new feature in Dynamics AX 2012 are Computed Comumns in views.

Computed Columns is one of the small and understated but highly usefull new features in Dynamics AX 2012.
Computed Columns in Views is an old but well established and useful feature in SQL Server since many versions. But this feature was available in Dynamics AX since version 2012.

Well, here is an example how I used Computed Columns in a View: In CustTrans-table there are two columns of interest. First “AmountCur” which represents the Amount which has to be payed. “SettledAmountCur” is the amount which already was paid. Now I am interesed in the difference between “AmountCur” and “SettledAmountCur”. Unfortunately there is no column in “CustTrans”-table which holds just this specific information. So I had to create a view with Computed Columns.

First I created a View “InvoiceDiscountDeclined”. Data source is “CustTrans”-table with the name ”CustTrans_1”.
Then I created an Method direct at the View with the name “DeclinedDiscount” (and also another one with name “DateDifference”, but this computed column is not in the scope of this article). Then I wrote the method below:



Important: The value returned by the method has to be string (str) and is computed as static method on the server.

For the calculation I used the “SysComputedColumn”-class of Dynamics AX 2012 which has lot of methods for various mathematical operations. I my example I used “subtract”-method which needs two table fields.

“SysComputedColumn”-class needs three different information:
1. Name of the view (red)
2. Name of the data source (blue). In my example the name is “CustTrans_1” and not just “CustTrans”!
3. The fieldname (together with the table-name; now we use “TransTable”)

In contrast to display-methods you just cannot drop the method into the fields list. You have to create a new computed column-field by right-clicking fields-note. Name the field. Set the “ViewMethod”-property to the name of the method (as in my example “DeclinedDiscount”).



When you press “Save”-button Dynamics AX 2012 creates a “Create View”-SQL-statement which creates a view in the underlying SQL Server-database. While synchronizing the view to the database, the method is called. After synchronization the method is not called any more, until your resynchronize the view to the database.

Well, that’s sounds good and indeed it is most time. But creating a view with computed columns could be tricky. My client crashed several times when I tried to change a just created view with computed columns.

But in the end the creation of the view with the computed columns had a happy end:



Some words why I have created this view and why I used computed columns to archive the goal:
In Dynamics AX document management I created a word-template which represents a collection letter send to clients if there are unsettled amounts open. (There are some special scenarios which cannot be handled by using “CollectionLetter”-SSRS-Report.)

In Dynamics AX 2009 you had to calculate the amounts with Word-Formulas and Bookmarks which was kind of tricky. Using a Computed column and just passing the calculated value to the Word-Document is much easier.