Microsoft Dynamics Ax 2012 Custom financial dimensions Lookup

There was a requirement to create a new field on a table that should be populated from a custom financial dimension values and need to show a lookup on the data entry form for this field that will populates the data from your custom financial dimension.

First need to identify the relevant tables that are needed to fulfill this requirement:

  • DimensionFinancialTag
  • DimensionAttribute
  • DimensionAttributeDirCategory
  • FinancialTagCategory

The list of values for custom dimension are stored in the table DimensionFinancialTag. But this table stores custom values for all custom financial dimensions. To determine which of the records to filter out and show we will have to apply a filter on the field FinancialTagCategory. How do we figure out the FinancialTagCategory value? We do that by traversing from the DimensionAttribute table (hold a records for your financial dimension).

The table DimensionAttributeDirCategory holds a link between DimensionAttribute and FinancialTagCategory, this table is used to retrieve the financialtagcategory and filter the records in the table DimensionFinancialTag.

Following is the Sample Code:

static void MyCustomFinancialDimensionLookup(Args _args)
{
SysTableLookup                      sysTableLookup;
Query                               query;
QueryBuildDataSource                qbdsDimensionFinancialTag;
QueryBuildRange                     qbrFinancialTagCategory;

#define.MyCustomFinancialDimension(‘MyCustomFinancialDimension’)

query = new Query();
qbdsDimensionFinancialTag = query.addDataSource(tableNum(DimensionFinancialTag));
qbrFinancialTagCategory = qbdsDimensionFinancialTag.addRange(fieldNum(DimensionFinancialTag, FinancialTagCategory));
qbrFinancialTagCategory.value(strFmt(‘%1’, DimensionAttribute::findByName(MyCustomFinancialDimension, false).financialTagCategory()));

sysTableLookup = sysTableLookup::newParameters(tableNum(DimensionFinancialTag), this);
sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag, Value), true);
sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag, Description));
sysTableLookup.addSelectionField(fieldNum(DimensionFinancialTag, FinancialTagCategory));
sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();
}