Sunday, November 4, 2012

Computed columns in Views by example

There is a bug in the standard AX, in the query named AxdPurchaseRequisition. The problem is here:

PurchLineAllVersions datasource is based on a View object, so PurchLineAllVersions.TableId contains table ID of the view itself, and not that of the underlying tables. As a result, no DocuRefTrans records will be found with this quiry.

The issue may be fixed by using computed columns in views - the new feature in AX 2012.

As you may already know, the PurchLineAllVersions view is based on the quiry PurchLineAllVersions, which, in turn, is a union of 2 other views: PurchLineArchivedVersions and PurchLineNotArchivedVersions.

First, add a method to the 2 views.


public static server str purchLineTableId()
    return SysComputedColumn::returnLiteral(tableNum(PurchLine));


public static server str purchLineTableId()
    return SysComputedColumn::returnLiteral(tableNum(PurchLineHistory));

Add a column to the 2 views: right-click on the Fields, then "New -> Int Computed Column". Set the new fields' properties like that:

Then, add new PurchLineTableId fields to the PurchLineAllVersions query datasources and the PurchLineAllVersions view.

Finally, change the faulty link in the AxdPurchaseRequisition quiry, so that it will connect PurchLineAllVersions.PurchLineTableId and DocuRefTrans.RefTableId.

By the way, there is another, better example of how computed columns may be used: take a look at the InventValue* views, which are used by the Inventory Value report.

No comments:

Post a Comment