Using a temporary table as a datasource in a form

Dynamics 365 Finance and Operations (F&O) has the ability to utilize temporary tables in code. There are many examples of temporary tables in the standard application. However, sometimes there is a requirement to display data in a temporary table in a form, so that the user can make selections or modify the data in the form before the data is processed.

One example in the standard application is when you post sub ledger documents, e.g. purchase or sales orders. The system presents the user with temporary data before the system posts the data and it becomes final.

Below is a simple example of how to populate a temporary table and display it in a simple form. It simply demonstrates the above-mentioned technique of using a temporary table as a dataSource and does not have any real-world value.

Create a temporary table or use an existing one. Note: The Table Type property of the table must be TempDB. So, if you are using an existing temporary table, make sure the Table Type property is TempDB.

Populate your temporary table with data. Note: The data in your table will be lost when the table buffer goes out of scope.

public class AVTmpTableDataProvider
{
    private AVUpdateDataTmpIdRef tmpIdRef; //added here to keep temp table instance alive while there is an object of this class.

    public AVUpdateDataTmpIdRef populateTmpTableDemo()
    {
        for (int j = 0; j < 100; j++)
        {
            tmpIdRef.Id = j;
            tmpIdRef.Name = guid2Str(newGuid());
            tmpIdRef.insert();
        }

        return tmpIdRef;
    }

}

Create a form and add your temporary table as the dataSource. Set the dataSource property of the grid and add some fields to the grid.

In your form, in the dataSource init method, populate your temporary table and then call the linkPhysicalTableInstance method of the dataSource. Note: You have to call this method after the dataSource has been initialized.

[Form]
public class AVTmpTableDialog extends FormRun
{
    //form class with an instance of our temp table. added here to keep temp table instance alive while the form is open.
    private AVTmpTableDataProvider dataProvider = new AVTmpTableDataProvider();

    [DataSource]
    class AVUpdateDataTmpIdRef
    {
        public void init()
        {
            super();

            //populate temp table in form class and link it to this datasource (after datasource has initialized).
            AVUpdateDataTmpIdRef.linkPhysicalTableInstance(dataProvider.populateTmpTableDemo());
        }

    }
}

Custom service using set based operations

Sometimes you just need a fast way to get specific data from Dynamics 365 Finance (F&O) for real-time processing of F&O data in another application. The data analyst and I were debating the architecture, and exporting the F&O data to a data warehouse was not meeting two of the most important requirements: performance and timeliness (i.e., data that must be up to date and not stale).

The client needs the data from F&O to be up to date and in real time without delays. Since the data output requirement did not involve large datasets, my solution was to use set-based operations to fill a temporary table and to expose the data in a custom service. I am very happy with the performance of this service and definitely recommend using this pattern to everyone out there.

On the highest level, there are basically two methods to insert data into the database: row-based and set-based. Let's briefly discuss both methods so that it's clear why I chose the set-based method.

Set-based operations

With set-based operations, the system creates a single SQL statement and sends it to the SQL Server, even if millions of records are processed in the select part of the statement. This statement selects data from tables and inserts it directly into the target table. It does the selection and insertion at the same time. This is extremely effective, and that makes it so fast.

Microsoft documentation: insert_recordset statement – Copy multiple records directly from one or more tables into another table in one database trip.

Row-based operations

Row-based operations are definitely the most used method in the standard application. They are straightforward and flexible to use, but the drawback is that they're slow. Row-based operations insert each row in a separate database round trip. The system first has to fetch the data, create X++ objects for each row, and then create a separate SQL statement for each X++ object / data row that should be inserted. This whole process adds a lot of overhead and therefore is inherently slow because of its design.

Disclaimer

• The code below is for demonstration purposes only. Use at your own risk.
• Don't be a silly Willy and use custom services for large datasets. Use other methods for that.

Okay, enough of all that, let's dive into the code part.

Temporary table

Create a temporary table with the fields that you will need for collecting the data and that will be exposed by the custom service. Set the TableType property to TempDB.

Service class

The code of the custom service class orchestrates data retrieval and prepares the data to be exposed by the custom service framework. When the data is retrieved, it uses a set-based operation, i.e., insert_recordset, which is very fast.

The different steps in the service class are:

• Retrieve data from tables.
• Populate the temp table.
• Enumerate the temp table (for each row in the temp table).
• Transfer the data from the table to an object.
• Add the populated object to a list.

The code below is the actual code that I created for the client. It is a real-world working example and has not been simplified, because it would have taken too much time and might contain bugs. I did change the prefixes everywhere in the code below for obvious reasons, and I anonymized some of the dimension field names.

The service has two methods, one to retrieve the balances of ledger accounts for a specified period. The second to retrieve all the transactions for a specified main account. The code below is just the first part because I don't want to make the post longer than needed. The two other helper methods demontrate how to use the set-based operations in X++.

public class AVGenJourAccEntrySvc
{
    [AifCollectionTypeAttribute('return', Types::Class, classStr(AVGenJourAccEntryBalances))]
    public List getBalances(DataAreaId _dataAreaId, AccountingDate _accountingDateFrom, AccountingDate _accountingDateTo)
    {
        AVGenJourAccEntrySvcContract    contract;               //data class for retrieving data.
        AVGenJourAccEntryAggTmp         genJourAccEntryAggTmp;  //temp table where our data is stored.
        AVGenJourAccEntrySvcDP          genJourAccEntrySvcDP;   //logic to retrieve data and populate temp table.
        List                            list = new List(Types::Class); //list of data contracts.

        if (_dataAreaId && _accountingDateFrom && _accountingDateTo)
        {
            contract = new AVGenJourAccEntrySvcContract();

            contract.parmAccountingDateFrom(_accountingDateFrom);
            contract.parmAccountingDateTo(_accountingDateTo);
            contract.parmDataAreaId(_dataAreaId);

            changecompany(contract.parmDataAreaId())
            {
                genJourAccEntrySvcDP = AVGenJourAccEntrySvcDP::construct();
                genJourAccEntryAggTmp = genJourAccEntrySvcDP.populateDataBalances(contract); //retrieve data and populate temp table.

                //get data from temp table
                while select genJourAccEntryAggTmp
                    order by genJourAccEntryAggTmp.MainAccountId,
                             genJourAccEntryAggTmp.MainAccountName,
                             genJourAccEntryAggTmp.Dim1CostCenter,
                             genJourAccEntryAggTmp.Dim2Dept,
                             genJourAccEntryAggTmp.Dim3,
                             genJourAccEntryAggTmp.TransactionCurrencyCode
                {
                    
                    AVGenJourAccEntryBalances balances = AVGenJourAccEntryBalancesenJourAccEntryBalances::construct();
                    balances.initFromAVGenJourAccEntryAggTmp(genJourAccEntryAggTmp); //transfer data from temp table to data contract.

                    list.addEnd(balances); //add data object to list.
                }
            }
        }

        return list;
    }
}

The populateDataBalances method uses two other methods that also use set-based operations. All data collected with this class is done purely with set-based operations, making it extremely fast.

public AVGenJourAccEntryAggTmp populateDataBalances(AVGenJourAccEntrySvcContract _contract)
{
    //1. insert general journal account entry data joining with flattened dimension data.
    this.populateBalanceSheetIncomeStatementMovementsTmp(_contract);

    //2. insert general journal account entry data joining with flattened dimension data filtered by main account and dim3.
    this.populateBalanceSheetClosingsTmp(_contract);

    //3. combine and aggregate result sets.
    insert_recordset genJourAccEntryAggTmp
        (
        Company,
        AccountingCurrencyAmount,
        TransactionCurrencyAmount,
        TransactionCurrencyCode,
        MainAccountId,
        MainAccountName,
        Dim1CostCenter,
        Dim2Dept,
        Dim3
        )
        select
            Company,
            sum(AccountingCurrencyAmount),
            sum(TransactionCurrencyAmount),
            TransactionCurrencyCode,
            MainAccountId,
            MainAccountName,
            Dim1CostCenter,
            Dim2Dept,
            Dim3
        from genJourAccEntryAggTmpOpenBal
        group by
            Company,
            TransactionCurrencyCode,
            MainAccountId,
            MainAccountName,
            Dim1CostCenter,
            Dim2Dept,
            Dim3;

    insert_recordset genJourAccEntryAggTmp
        (
        Company,
        AccountingCurrencyAmount,
        TransactionCurrencyAmount,
        TransactionCurrencyCode,
        MainAccountId,
        MainAccountName,
        Dim1CostCenter,
        Dim2Dept,
        Dim3
        )
        select
            Company,
            sum(AccountingCurrencyAmount),
            sum(TransactionCurrencyAmount),
            TransactionCurrencyCode,
            MainAccountId,
            MainAccountName,
            Dim1CostCenter,
            Dim2Dept,
            Dim3
        from genJourAccEntryAggTmpAccDim
        group by
            Company,
            TransactionCurrencyCode,
            MainAccountId,
            MainAccountName,
            Dim1CostCenter,
            Dim2Dept,
            Dim3;

    //4. delete zero amount transactions.
    delete_from genJourAccEntryAggTmp
        where genJourAccEntryAggTmp.AccountingCurrencyAmount == 0;

    return genJourAccEntryAggTmp;
}

//1. insert general journal account entry data joining with flattened dimension data.
private void populateBalanceSheetIncomeStatementMovementsTmp(AVGenJourAccEntrySvcContract _contract)
{
    FiscalCalendarPeriod                        fiscalCalendarPeriod;
    MainAccount                                 mainAccount;
    GeneralJournalEntry                         generalJournalEntry;
    GeneralJournalAccountEntry                  generalJournalAccountEntry;
    DimensionAttributeValueGroup                dimensionAttributeValueGroup;
    DimensionAttributeLevelValue                dimensionAttributeLevelValue;
    DimensionCombinationEntity                  dimensionCombinationEntity;

    insert_recordset genJourAccEntryAggTmpOpenBal
        (
        Company,
        AccountingCurrencyAmount,
        TransactionCurrencyAmount,
        TransactionCurrencyCode,
        MainAccountId,
        MainAccountName,
        Dim1CostCenter,
        Dim2Dept,
        Dim3
        )

        select SubledgerVoucherDataAreaId from generalJournalEntry

        join AccountingCurrencyAmount, TransactionCurrencyAmount, TransactionCurrencyCode from generalJournalAccountEntry
            where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId

        join MainAccountId, Name from mainAccount
            where generalJournalAccountEntry.MainAccount == mainAccount.RecId
                    
        join Dim1CostCenter, Dim2Dept, Dim3
            from dimensionCombinationEntity
                where generalJournalAccountEntry.LedgerDimension == dimensionCombinationEntity.RecordId
            
        exists join fiscalCalendarPeriod
            where generalJournalEntry.FiscalCalendarPeriod == fiscalCalendarPeriod.RecId &&
            
            generalJournalEntry.Ledger         == Ledger::current()                    &&
            generalJournalEntry.AccountingDate >= _contract.parmAccountingDateFrom()   &&
            generalJournalEntry.AccountingDate <= _contract.parmAccountingDateTo()     &&
            fiscalCalendarPeriod.Type          == FiscalPeriodType::Operating          &&
          ((mainAccount.MainAccountId < #FirstIncomeStatmentMainAccount && dimensionCombinationEntity.Dim3 != '') || mainAccount.MainAccountId >= #FirstIncomeStatmentMainAccount); //balance sheet transactions with movements or income statement transactions.
}

//2. insert general journal account entry data joining with flattened dimension data filtered by main account and dim3.
private void populateBalanceSheetClosingsTmp(AVGenJourAccEntrySvcContract _contract)
{
    DimensionValue                              closingMovementCode = '777';
    MainAccount                                 mainAccount;
    GeneralJournalEntry                         generalJournalEntry;
    GeneralJournalAccountEntry                  generalJournalAccountEntry;

    DimensionAttributeValueGroup                dimensionAttributeValueGroup;
    DimensionAttributeLevelValue                dimensionAttributeLevelValue;
    DimensionCombinationEntity                  dimensionCombinationEntity;

    insert_recordset genJourAccEntryAggTmpAccDim
        (
        Company,
        AccountingCurrencyAmount,
        TransactionCurrencyAmount,
        TransactionCurrencyCode,
        MainAccountId,
        MainAccountName,
        Dim1CostCenter,
        Dim2Dept,
        Dim3
        )

        select SubledgerVoucherDataAreaId from generalJournalEntry

        join AccountingCurrencyAmount, TransactionCurrencyAmount, TransactionCurrencyCode from generalJournalAccountEntry
            where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId

        join MainAccountId, Name from mainAccount
            where generalJournalAccountEntry.MainAccount == mainAccount.RecId

        join Dim1CostCenter, Dim2Dept, closingMovementCode
            from dimensionCombinationEntity
                where generalJournalAccountEntry.LedgerDimension == dimensionCombinationEntity.RecordId &&

            generalJournalEntry.Ledger          == Ledger::current()                    &&
            generalJournalEntry.AccountingDate	>= _contract.parmAccountingDateFrom()   &&
            generalJournalEntry.AccountingDate  <= _contract.parmAccountingDateTo()     &&
            mainAccount.MainAccountId           < #FirstIncomeStatmentMainAccount; //only balance sheet transactions.
}

Service

In order to expose your custom service, you need to create a service and specify your service class and methods. Give the service an appropriate name.

Service group

Create a custom service group and add your service class to the group.