Exact Online Connector - AP/AR subledgers at year end?

Questions
,
1

I am using the Exact Online connector to extract data from my Exact Online accounting package with the goal of building a standard data model.

The problem I am confronted with:

  • I am able to successfully retrieve all ledger transactions.
  • However, I am unable to reconstruct the Accounts Payable (AP) and Accounts Receivable (AR) subledgers at year-end.

Sample / Situation

From the connector I have access to the following tables (see attached screenshot):

  • GLAccounts
  • HrmDivisions
  • ItemGroups
  • Items
  • Journals
  • PayablesList
  • Payments
  • PurchaseEntries
  • PurchaseEntryLines
  • Receivables
  • SalesEntries
  • SalesEntryLines
  • TransactionLines
  • Users

Even though I can pull general ledger data, I cannot find a clear way to create a reliable AP and AR subledger position at year closing based on these available tables.

What I tried

I have tried the following:

  • Joining TransactionLines with PayablesList and Receivables.
  • Exploring whether PurchaseEntryLines and SalesEntryLines can be aggregated to reconstruct subledger balances.
  • Reviewing the available tables for year-end markers, but without success.

Question

Is it possible to construct the AP and AR subledgers at year-end using these Exact Online connector tables? If so, could you provide guidance or an example of how to achieve this?

2

The year or period ultimo figures can easily be retrieved using either

  • pre-defined views such as AROutstandingItemsPerFinancialPeriodUltimo (best used with a filter for the relevant year to improve performance)
  • or directly accessing the table function AROutstandingItemsEx (with Ex standing for “extended”)

Find more information in these topics:

As a sidenote: for reporting / replication purposes, it is recommended to use the *Incremental tables for Items, ...Entries, ...EntryLines and TransactionLines. They are up to 1.000 times faster.

1 like