Missing company code field in Twinfield GeneralLedgerAccountMappings table

Goal: I aim to establish a connection between the Twinfield.Views.GeneralLedgerAccountMappings@tfd (1) table and the Twinfield.Views.GeneralLedgerAccountMappingAccounts@tfd (2) table from Twinfield.

Problem: The Account Mappings Tables from Twinfield no longer include the company code field, hindering my ability to link the ledger to hierarchies effectively.

Request: can the field for the company code be re-added to tables (1) and (2)?

The company code fields in this table have been removed permanently. Please read the functional reason on:

The use of company code should not be necessary, since it is a Twinfield-subscription-wide data set, relevant for all companies.

After connecting the two account mapping tables, I want to further link the account mapping to the ledger table: Twinfield.Twinfield.FinancialDimensions@tfd. The Twinfield.Views.GeneralLedgerAccountMappingAccounts@tfd contains the linked general ledger account codes per HIERARCHYELEMENTID.

The account code itself is not unique; it is only unique within one administration/company code. Without the company code, the account code is ambiguous, and I cannot exactly determine which general ledger account is being referred to.

Is it possible to provide an example (anonymized screenshot of Twinfield and result in table versus expectation)?

Here’s a sample of the Twinfield.Twinfield.FinancialDimensions@tfd table:

COMPANY_CODE COMPANY_NAME CODE NAME
1 Company 1 81 Car
2 Company 2 81 Lorry

The code of the general ledger account represents a car for one administration and a lorry for the other.

These are the available fields in the Twinfield.Views.GeneralLedgerAccountMappingAccounts@tfd table:

hierarchy_CODE hierarchy_NAME HIERARCHYELEMENTID TYPE CODE BALANCETYPE PARENTID PARENTCODE LEVEL PATH
1 Hierarchy 1 1 BAS 81 Balance 1 1 1 1.1.1

Without the company code, I cannot determine whether the account code in the mapping table refers to the car or lorry general ledger account.

Based upon data, the factor “company” seems irrelevant for the hierarchy and it’s elements. The match between General Ledger Account and Hierarchy is done by matching on equal code. No database relationship can be found.

For instance, a hierarchy tree which includes 14 accounts starting with “020” can be used on a Twinfield company with no or solely one GL account in the “020”-range.

In case of a database relationship the hierarchy tree could not include the accounts which don’t exist in the Twinfield company.

It is recommended to reach out to the Twinfield application administrator for clarity. Please add a link to the Twinfield documentation if necessary in case there is a documented database relationship.

I am following this discussion with great interest, but what I can’t find yet is how to match the lowest level (account) to the first ‘Parent’.

For example:

How to link lowest account (example: 8001) to the first level of the hierarchy?

A test was executed on an environment with a single company. Query 1 selects a random company (of the 1 available) and query 2 selects all companies (of the 1 available).

Both return the same results:

select *
from   GeneralLedgerAccountMappingAccounts

select *
from   GeneralLedgerAccountMappingAccountsTEST

The TEST-view is a temporary addition. For Invantive it is not possible to analyze the working due to lacking documentation and no access to a test environment which displays the behaviour (Twinfield offers no access to partners to the Hierarchy setup).

In case any user has a (test) environment that displays the problem and for which credentials (user name, password, environment code) can be provided we will be happy to look into it.

In GeneralLedgerAccountMappingAccounts we are also missing general ledger accounts from our Twinfield administration. I would expect GeneralLedgerAccountMappingAccounts contains all general ledgers of all administrations? What is the solution for this problem

We experience the exact same issue.

I am working on an environment to reproduce for Invantive.

The problem can not be analyzed nor reproduced due to lack of a representative test environment (not available for Twinfield developers) or production environment.

Awaiting access, the view GeneralLedgerAccountMappingAccounts has been made unavailable.

Temporary the alternatives are:

  • GeneralLedgerAccountMappingAccountsTEST1
  • GeneralLedgerAccountMappingAccountsTEST2

Thanks to @mvk an reproduction scenario was made available. The next release of Invantive Cloud (scheduled for deployment within 24 hours) will include a new version of GeneralLedgerAccountMappingAccounts that addresses the issue for them.

The necessary changes will also be available in release 24.0.126 and newer of Invantive UniversalSQL.

The two test views will no longer be available.