Computed Column to a View in AX 2012

This post describes how you can add a computed column to a view in Microsoft Dynamics AX.
A computed column is the output of a computation that inputs a regular column.

For example, suppose your table has a column that is named AnnualRent. Your view could return a computed column that is named MonthlyRent and which calculates AnnualRent/12.

You can add the computed column to the select list of columns only. If the computed column is itself a select statement with a where clause, you can include the name of a real column in that where clause.

Create a View

In this section you create a view named TestCompColView.

1. Click AOT > Data Dictionary > Views > New View.
2. Right-click the new View1 node, and then click Properties.
3. In the Properties window, change the Name property to TestCompColView.
4. Expand the Views > TestCompColView > Metadata > Data Sources node.
5. Open a second AOT window and drop the node AOT > Data Dictionary > Tables > CustTable onto the TestCompColView > Metadata > Data Sources node in the first AOT window.
6. Expand the TestCompColView > Metadata > Data Sources > CustTable_1 > Fields node.
7. In the other AOT window, expand the TestCompColView > Fields node.
8. From under the TestCompColView > Metadata > Data Sources > CustTable_1 > Fields node, drop the AccountNum field onto the TestCompColView > Fields node of the other AOT. Also drop the SubsegmentId field in the same way.
9. Click TestCompColView > Save.

Add a Static Method to the View

The technique to add a computed column begins with you adding a static method to the view. The method must return a string. The system automatically concatenates the returned string with other strings that the system generates to form an entire T-SQL create view statement.

 

The method that you add to the view typically has at least one call to the DictView.computedColumnString method. The parameters into the computedColumnString method include the name of a data source on the view, and one field name from that data source. The computedColumnString method returns the name of the field after qualifying the name with the alias of the associated table. For example, if the computedColumnString method is given the field name of AccountNum, it returns a string such as A.AccountNum or B.AccountNum.

Steps to add a method to a view

1. Under AOT > Data Dictionary > Views, expand the node for your TestCompColView view that you created in the previous section.
2. Under your view, click Methods > New Method. The method editor window is displayed.
3. Change the method definition to the following:
   private static server str compColSubsegAcctMethod()

Code in the Body of the Method

private static server str compColSubsegAcctMethod()
{
    #define.ViewName(TestCompColView)
    #define.DataSourceName("CustTable_1")
    #define.FieldSubsegmentId("SubsegmentId")
    #define.FieldAccountNum("AccountNum")
    str sReturn,
        sAccountNum,
        sSubsegmentId;
    DictView dictView2;

    // Construct a DictView object for the present view.
    dictView2 = new DictView(tableNum(#ViewName));

    // Get a string that has the target field name
    // propertly qualified with an alias (such
    // as "A." or "B.").
    sAccountNum = dictView2.computedColumnString
        (#DataSourceName,
        #FieldAccountNum,
        FieldNameGenerationMode::FieldList,
        true);

    sSubsegmentId = dictView2.computedColumnString
        (#DataSourceName,
        #FieldSubsegmentId,
        FieldNameGenerationMode::FieldList,
        true);

    sReturn = "substring("
        + sSubsegmentId
        + ",1,1) + ' - ' + "
        + sAccountNum;

    // Helpful confirming or diagnostic information.
    info(sAccountNum);
    info(sSubsegmentId);
    info(sReturn);

    return sReturn;
}

Add a Computed Column to the View 
In this section you add a computed column to the view. 
Right-click the Fields node under your view, and then click String Computed Column. 
For the new field or column, change the Name property to compCol_Subseg_Acct. 
Change the StringSize property to 32. 

Link the Computed Column to the Method 
In this section you relate the computed column to the static method that you added in a previous section. 
For the computed column, you set the ViewMethod property to the name of the compColSubsegAcctMethod method that you wrote in a previous step. 

See the Data in the Computed Column 
 
The data that is generated for the computed column can be seen by using the AOT. Click AOT > Data Dictionary > Views > TestCompColView > Open. 
Sample Data from the View
The following table displays sample data that includes the computed column. 

SubsegmentId 
AccountNum 
CompCol_Subseg_Acct 
Medium 
4000 
M - 4000 
Gross 
4001 
G - 4001