Dynamics Community Forum Thread Details

Pulling Data from Trial Balance Feature in D365 Finance and Operations to Custom Table

Hello everyone,

I’m working on an integration project in D365 FO where I need to pull and store trial balance data from the standard Trial Balance feature located under General Ledger > Inquiries and Reports. I want to extract this data into a custom table (NPLLedgerTrialBalance) so I can send it through Data Entity. I am using the LedgerTrialBalanceDP class along with a contract (LedgerTrialBalanceContract) to filter data by fiscal period and fiscal year. Additionally, I am pulling DisplayMainAccountCategory from a parameter field in D365.

Below is a shortened version of my code

public void trialBalance(FiscalCalendarPeriod calendarPeriod)
{
    NPLLedgerTrialBalance _TrialBalance = null;
    LedgerTrialBalanceTmp _ledgerTrialBalanceTmp = null;
    TransDate iniDate = calendarPeriod.StartDate;
    TransDate endDate = calendarPeriod.EndDate;
    List postingLayers;

    // Update dimension focus balance
    DimensionFocusUpdateBalance::updateBalance(DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, "MA + Brand + Location + Dept"));

    // Posting layers
    postingLayers = new List(Types::Enum);
    postingLayers.addEnd(CurrentOperationsTax::Current);

    // Contract setup
    LedgerTrialBalanceContract trialBalanceContract = new LedgerTrialBalanceContract();
    trialBalanceContract.parmFromDate(iniDate);
    trialBalanceContract.parmToDate(endDate);
    trialBalanceContract.parmIncludeOpening(true);
    trialBalanceContract.parmIncludeClosingAdjustments(true);
    trialBalanceContract.parmIncludeClosingTransactions(true);
    trialBalanceContract.parmPostingLayers(postingLayers);
    trialBalanceContract.parmPrimaryDimensionFocus(LedgerParameters::find().NPLDimensionSetName);
    trialBalanceContract.parmDisplayMainAccountCategory(true);

    // Process data
    LedgerTrialBalanceDP trialBalanceDP = new LedgerTrialBalanceDP();
    trialBalanceDP.parmDataContract(trialBalanceContract);
    trialBalanceDP.processReport();

    // Iterate over LedgerTrialBalanceTmp and insert into NPLLedgerTrialBalance
    _ledgerTrialBalanceTmp = trialBalanceDP.getLedgerTrialBalanceTmp();

    while select _ledgerTrialBalanceTmp
    {
        _TrialBalance.clear();
        _TrialBalance.AmountCredit = _ledgerTrialBalanceTmp.AmountCredit;
        _TrialBalance.EndingBalance = _ledgerTrialBalanceTmp.EndingBalance;
        _TrialBalance.NetChange = _ledgerTrialBalanceTmp.NetChange;
        _TrialBalance.insert();
    }
}

The code works but doesn’t seem to pull the exact data displayed in the Trial Balance feature, some periods have the wrong Closing Balances, Debit amount and Credit, so when I check in the Trial Balance report the data looks correct but not in my table. Can someone point me in the right direction or suggest a better approach to make sure I'm retrieving the exact trial balance data?

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL