Summary: the improved UniversalSQL driver for Twinfield makes it possible to also reliably and smoothly process companies with between 500,000 and 10 million financial transactions in Power BI and data warehouses without any modifications to existing logic.
Error message itgensop090 and/or itgentfr091 with large Twinfield companies
When retrieving all transaction data such as general ledger entries from Twinfield companies with a relatively large amount of data (e.g. 500,000 entries or more in the entire company), an error message itgensop090, itgentfr091 or an HTTP error 524 frequently occurs. A repeated attempt often, but not always, leads to successful retrieval of the financial transactions.
An improvement of the Twinfield SQL-driver makes it possible to process the complete transaction data in one go even for such relatively large Twinfield companies.
This approach will not always work for extremely large Twinfield companies with more than 25,000 journal entries per day and, for example, 10 million or more entries in total within a single company. In the standard UniversalSQL driver for Twinfield features were already present to process such volumes as well as volumes up to well beyond 200 million financial transactions. However, we recommend seeking professional support from Invantive for the right configuration and processing approach such volumes.
Algorithm and Configuration.
The enhancement in the Twinfield SQL driver splits the transaction data retrieval across multiple so-called “pages.” One API call is executed for each page. By “paging” the transaction data across multiple pages the error messages will never occur again, except for (extremely rare) extremely large Twinfield companies with the aforementioned 25,000 entries or more per day in 1 company.
The desired size of the number of transactions in a “page” can be controlled via the driver attribute transaction-tables-page-size. The default size of a page is set to 50,000 rows based on empirical data.
Tables with Improved Scalability by Pagination.
The following Twinfield tables will exhibit improved scalability due to this modification to the SQL driver:
ActiveAnalysesAnnualReportTotalsAnnualReportTotalsMulticurrencyAnnualReportYtdAssetTransactionsBankLinkDetailsBankLinkDetailsV2BankTransactionsCashTransactionsCommercialAnnualReportTotalsCommercialAnnualReportTotalsMulticurrencyCommercialAnnualReportYtdCostCentersCreditManagementCustomersCustomersV2CustomersV3CustomerTransactionsDocketInvoicesEconomicAnnualReportTotalsEconomicAnnualReportTotalsMulticurrencyEconomicAnnualReportYtdFiscalAnnualReportTotalsFiscalAnnualReportTotalsMulticurrencyFiscalAnnualReportYtdFixedAssetsGeneralLedgerDetailsGeneralLedgerDetailsV2GeneralLedgerDetailsV3GeneralLedgerIntercompanyGeneralLedgerIntercompanyV2GeneralLedgerTransactionsHierarchyAccessProjectTransactionsSuppliersSuppliersV2SupplierTransactionsTimeAndExpensesBillingDetailsTimeAndExpensesBillingPerPeriodTimeAndExpensesBillingPerWeekTimeAndExpensesDetailsTimeAndExpensesMulticurrencyTimeAndExpensesTotalsTimeAndExpensesTotalsPerPeriodTimeAndExpensesTotalsPerWeekTransactionListTransactionsToBeMatchedVatReturnStatus
Full documentation of these Twinfield tables is available at SQL-stuurprogramma voor Twinfield API - Twinfield API Data Model. An overview of the most commonly used Twinfield tables can be found at Meest populaire Twinfield-tabellen voor 2024 (Dutch).
Availability
This enhancement is available starting release 24.0.135 and newer. This feature is expected to be available on Invantive Cloud from April 15, 2024.