Dynamics AX 2012 supports computed columns in AOT views. That allows you to convert a value of a field in some way, to actually compute a value based on several fields and so on, and it’s all defined directly in SQL Server as any other view. Indeed, the definition of a computed column just generates T-SQL string to be inserted to the definition of the view (cast to the type you chose when created the computed field).

For example, this is a trivial definition of a computed column:

public static server str test()
{
    return '42';
}

If you define the computed field as int, the following T-SQL will be generated:

CAST(42 AS INT) AS YOURFIELDNAME

If you use the same method, but with a field of type string, you’ll get something like this:

CAST(42 AS NVARCHAR(10)) AS YOURFIELDNAME

This is all quite straightforward. Because we actually write direct T-SQL code, we can use use SQL Server’s functions, control structures and all such things. For example, you could assign a number to each record using the ROW_NUMBER() function:

public static server str test()
{
    return 'ROW_NUMBER() OVER (ORDER BY RECID)';
}

And, of course, you can even write subqueries there – let’s look at a complete example.

Let’s say we want to make a list of users and add a computed column with number of users’ roles. The first step is to create a view, called UserInfoView, for instance. Then drag UserInfo table to datasources, which creates UserInfo_1 datasource. Drag some fields (id and name, for example) to Fields and the query is ready. Now we just need to add the column with a number of roles.

Add a new method to the view, declare it as public static server str and call it numOfRoles.

At the end, we want a query looking somehow like this:

SELECT COUNT(*) FROM SecurityUserRole WHERE USER = id

but things are slightly more complicated. First of all, SecurityUserRole table in SQL database doesn’t contain any field called ‘User’. The problem is probably in that USER is a reserved keyword in T-SQL, therefore AX must use another field name in the database. The field is actually called USER_, but that’s nothing you should care about – you just have to ask AX for the right variant of the name. I used SysDictTable.fieldName() method for this purpose – notice the argument value DbBackend::Sql.

SysDictTable roleDictTable = new SysDictTable(tableNum(SecurityUserRole));
roleDictTable.fieldName(fieldNum(SecurityUserRole, User), DbBackend::Sql);

Another problem occurs if we want to identify the table to which id field belongs (it’s not strictly necessary here but let’s show it too). We can’t use UserInfo.id, because the view assigned an alias to UserInfo table (T1, at least on my machine). The solution is to use computedColumnString() method:

DictView dv = new DictView(tableNum(UserInfoView));
dv.computedColumnString('UserInfo_1', fieldStr(UserIfo, id), FieldNameGenerationMode::WhereClase);

The code returns the full identification of the field, i.e. T1.ID in this case. We don’t have such a problem with SecurityUserRole table, because we have the name under our control (we left it without any alias, but we could use one if we wanted).

Also note that you don’t have to use the trick with DbBackend::Sql here – computedColumnString() provides the right name automatically.

This is the full code of my method:

public static server str numOfRoles()
{
    //return 'select count(*) from SECURITYUSERROLE where USER_ = T1.ID';
 
    DictView dv = new DictView(tableNum(UserInfoView));
    SysDictTable roleDictTable = new SysDictTable(tableNum(SecurityUserRole));
    str idFieldName = dv.computedColumnString(
        'UserInfo_1',
        fieldStr(UserInfo, id),
        FieldNameGenerationMode::WhereClause);
 
    return strFmt('select count(*) from %1 where %2 = %3',
        roleDictTable.name(DbBackend::Sql),
        roleDictTable.fieldName(fieldNum(SecurityUserRole, User), DbBackend::Sql),
        idFieldName);
}

It’s not really a pretty and readable code, but at least it works correctly. I could improve it a little by creating some helper methods, but this is better for demonstration purposes. Also note that you can use (and create) helper methods in SysComputedColumn class.

When you use the method in a computed field in the view, the synchronization creates the following (or similar) database view. It’s very simple, but you can create complex queries in the same way.

SELECT NAME, ID, RECID,
    CAST ((SELECT COUNT(*) AS Expr1
            FROM  dbo.SECURITYUSERROLE
	    WHERE (USER_ = T1.ID)
        ) AS INT
    ) AS NUMOFROLES
FROM dbo.USERINFO AS T1

Computed columns in Dynamics AX 2012 are very powerful, without doubt. It’s a way how to fully use abilities of SQL Server – subqueries are just one small example. The disadvantage is that their usage is not all completely straightforward and you must be careful about things like field names or security implications.