View computed column fields

One truth about software development is that it is constantly changing. So much so, it is easy to miss or forget information. It does have the advantage of being able to celebrate the inclusion of exciting new features a second time. In Dynamics AX 2012, Microsoft introduced computed column fields on views. I just recently relearned of their existence and I heard a celestial choir descend from the heavens (a second time).

A view in Dynamics AX (and Dynamics 365) is essentially a query that persists the underlying database that a developer can access as a read-only table. Before Dynamics AX 2012, fields on a view had to be fields of an underlying table. That is no longer a restriction albeit knowledge of SQL is necessary to take advantage.

One of the scenarios in which a developer can use a computed column field eliminates one of the most painful aspects of display methods: it is not possible to filter or sort on them. It is possible to express most display methods using aggregate functions such as “count” or “sum.” Since the computed column field appears as a regular view field, the user can sort or filter on it. Other uses include calculating and displaying a monthly payment amount from a yearly fee.

The following steps outline the process to create a view computed column field. This walkthrough assumes that the developer already has a view that will get the field.

Create a static method on the view

The developer defines the SQL code for the computed column in a static method on the view. The method does not take any arguments and returns a string which is a snippet of SQL code that performs the computation. The following code is an example of one such static method in Dynamics 365.

    /// Example view computed column static method
    /// This example creates a column that prefixes the 
    /// numeric value of the SalesType field to the 
    /// Sales order number.
    private static str computedColumn1()
    {
        const str datasourceName = 'SalesTable';
        const str salesIdField = 'SalesId';
        const str salesTypeField = 'SalesType';

        // Instantiate a DictView object for the view
        DictView viewView1 = new DictView(tableNum(View1));

        // Obtain the fully qualified names of the fields used
        // in the computation of the computed column. Since
        // we are writing SQL code, we need their names as
        // they are in the database.
        str salesIdName = viewView1.computedColumnString(datasourceName, salesIdField,
            FieldNameGenerationMode::FieldList, true);

        str salesTypeName = viewView1.computedColumnString(datasourceName, salesTypeField, 
            FieldNameGenerationMode::FieldList, true);

        // create the SQL code snippet that computes the column
        str ret = strfmt('cast(%1 as NVARCHAR(3)) + \' - \' + %2', salesTypeField, salesIdField);

        return ret;
    }

The next step is to define the computed column field on the view.

Create the field and link to the method

Under the Fields node of view, the developer adds a Computed Column of the appropriate data type.

After giving the new field a name, the developer sets the appropriate properties. For a computed column field, the important property is View Method that the developer sets to the static method.

Synchronize the database

The final step is to build the solution (compile and generate CIL in Dynamics AX 2012) and synchronize the database. If the SQL code snippet returned by the static method is not syntactically correct, there will be an error that needs to be fixed. If the synchronize was successful, the developer can inspect the results using the Table Browser.

References

• Walkthrough: Add a Computed Column to a View [AX 2012]

Advertisement