Filter voucher transactions by cost center in x++

 

Purpose:

The purpose of this blog post is to demonstrate how can we filter voucher transactions by cost center financial dimension for each user and the technical challenges behind it.

Product:

Dynamics 365 Finance and Operations

Problem:

Business requirement is that users should only view voucher transactions for their cost centers. Although we can define the owner for a cost center financial dimension but we cannot assign cost centers for each user in the application. Further, another business requirement is a user can be linked to as many as 500 cost centers!

Solution:

I designed a solution to achieve this business requirement by adding new table MAUserOperatingUnitTable to store user-cost center relationship.

Then the challenge was how to apply filter to the voucher transactions inquiry form so that it works for a user having access to as many as 500 cost centers. One option is to apply range to the DimensionAttributeValueCombination form datasource of LedgerTransVoucher form. However, since a user can have more than 100 cost centers, adding a range for each of these cost centers will result in the following error message:

Stack trace: Binding operation failed to allocate buffer space Object server azure

The second option is go for an exists join query. The challenge with an exists join query is that if you see DimensionAttributeValueCombination table in AOT, it stores financial dimensions for voucher transactions in DisplayValue field which is a combination of financial dimensions and the main account as per the account structure and advanced rule structure configurations in the general ledger.

So we cannot do an exists join on DisplayValue field for a cost center value since parent table field must match with child table field for a successful exists join. Although DimensionAttributeValueCombination table in AOT does not show fields for individual financial dimensions, it does actually contain fields for these financial dimensions in the SQL table.

To reference COSTCENTERVALUE dynamic field in X++ for using it in exists join we can use the following method: DimensionAttributeValueCombination::getDimensionValueFieldId Please find the code snippet below.

/// <summary>
/// This method adds a range to the form datasource to
/// filter voucher transactions per user cost center.
/// Developed by Muhammad Anas Khan.
/// </summary>
/// <param name="sender">Form datasource</param>
/// <param name="e">Form datasource event args</param>
/// <remarks>
/// Developed by Muhammad Anas Khan.
/// </remarks>
[FormDataSourceEventHandler(formDataSourceStr(LedgerTransVoucher, GeneralJournalEntry), FormDataSourceEventType::QueryExecuting)]
public static void GeneralJournalEntry_OnQueryExecuting(FormDataSource sender, FormDataSourceEventArgs e)
{
    const Name dimensionCostCenter = "COSTCENTER";
    FieldId costCenterValueFieldId;
    FormRun formRun = sender.formRun();
    FormDataSource dimensionAttributeValueCombination_ds = formRun.dataSource(formDataSourceStr(LedgerTransVoucher, DimensionAttributeValueCombination));
    QueryBuildRange qbr;
    QueryBuildDataSource qbds;
    str qbrValue;

    costCenterValueFieldId = DimensionAttributeValueCombination::getDimensionValueFieldId(dimensionCostCenter);
    qbds = sender.query().dataSourceName(dimensionAttributeValueCombination_ds.name()).addDataSource(tableNum(MAUserOperatingUnitTable));
    qbds.joinMode(JoinMode::ExistsJoin);
    qbds.addLink(fieldNum(MAUserOperatingUnitTable, OMOperatingUnitNumber), costCenterValueFieldId);
    qbr = qbds.addRange(fieldNum(MAUserOperatingUnitTable, User));
    qbr.value(curUserId());
}