Microsoft dynamics Ax : Retreive Name using AOT View syscomputedColumns

Recently I was working requirement where i need to fetch the ‘Requisitioner’ name that actually request the Purchase requisition, which is basically a display method on the PurchReqLine table. Any one who already worked on display method know about it. But if we are dealing with Views we don’t have the leverage of using display method on Views.

Talking about AOT views; these are the straight SQL queries that retrieves the data from multiple tables and at times we have a requirement of calculating values based one the fields in the view. Calculation of fields are done through compute-columns functionality in view. A computed column is the output of a computation that inputs a regular column

For AOT View : http://msdn.microsoft.com/en-us/library/bb314551.aspx

For ComputedColumn: http://msdn.microsoft.com/en-us/library/gg845841.aspx

So using the same approach, i created a PurchReqSummary view and added a CompPurchReqName computed column.

PurchReq

 

SysComputedColumn is the system class that we used in order to calculate/retrieve the fields. You can explorer the class. https://msdn.microsoft.com/en-us/library/syscomputedcolumn.aspx

There are some rules if we want to write a compute -columns.  You can find a nice example here as well

http://community.dynamics.com/ax/b/dynamicsaxconcoction/archive/2015/07/16/ax2012-r3-use-of-syscomputedcolumns-to-build-smart-ax-views

Below is the code of retriving Requistioner’s name

public static server str compPurchReqName()
{
str expression;

expression = “select name from dbo.DIRPARTYTABLE Join HCMWORKER ON HCMWORKER.PERSON = DIRPARTYTABLE.RECID AND HCMWORKER.RECID = ” +
SysComputedColumn::returnField(tableStr(AMC_PurchReqSummary), identifierStr(PurchReqLine_1), fieldStr(PurchReqLine,Requisitioner));

return expression;
}

Note: There are several other ways, for achieving the above requirement.

 

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s