Recurrence Export of Trial Balance

Anitha SantoshFinance & Operations4 days ago23 Views

Exporting the TB monthly and also having a snapshot of the earlier data helps in analysing and generating the report.

I had one such requirement where the TB should be exported for each month and sent to another system.

In D365FO 10.0.39, trial balance snapshots are added to the Trial balance list page. A trial balance snapshot calculates the balances of the selected financial dimensions.

How to set up the snapshot:

Go to System Administration -> Set up -> Process automation

Click on ‘Create new process automation’ and select the ‘Schedule type’ as ‘Trial balance snapshot update’

The company will be automatically populated based on the current company.

The next screen is presented with the information to pick the transactions based on the Start date and End date.

Recurrence can be set based on the business requirement . If an email or alerts are to be configured, make the necessary set ups here.

Other information like dimension set to be used for generating the TB, layer and the fiscal year can be set here. These are captured in the Header table.

This creates a new record in the snapshot grid . It is also possible to disable the occurence . If you want to view the recurrence, click ‘View recurrence’

How to verify this data?

Now the set up is ready, the results of this can be viewed in General Ledger module.

When the occurence is completed, data can be viewed by clicking ‘View in Excel’. It is disabled if the process is yet to start. The Status will be displayed as ‘Not started’.

You can wait for the job to start in the scheduled time. But to see the results immediately, click on ‘Run snapshot once’ which will add the batch job to the queue.

The status will be changed to ‘Processing’ when the batch is doing the actual job.

The status will be set to Completed when the batch ends successfully and ‘Open in Excel’ will be enabled.

The data will be inserted in two tables

LedgerTrialBalanceSnapshotHeader

LedgerTrialBalanceSnapshotData

How to integrate?

The data can be exported using ‘Open in Excel’.

Another way is to create a DIXF Export project . Given below the set ups

Go to the entity ‘Transaction table for trial balance snapshot data’

2. If you configure DIXF export , it exports all the data in the table always. This is not needed for integration. If you want to export the recent data, enable ‘Change Tracking’ in the entity.

Create a new DIXF Export project and select the above entity .Make sure you set this as ‘incremental push’ which triggers only the recent data.

The above DIXF export job can be called be Logic apps or other middleware to send the recent data.

Advantages:

Data can be exported to Excel using data entity.

Each trial balance snapshot has an external tracking ID that can be used to make the snapshot available to external systems for reporting purposes.

It is also possible to create the snapshot from the TB form .

In this case, there is no recurrence set . You can generate using ‘Run snapshot once’. Note that the system differenciates the records created from Process automation .