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();
}

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