I’ve consolidated different companies using Picklist functionality. I also appended the Company name to the GL Account number.
My Management Pack was of Type “D”, now I notice I can no longer drill-down to transaction level. Is it possible to still drill-down after creating a Picklist?
Yes, it is possible by adjusting the Ledger Details.
Method
Typically you would append the Company name to the GL account number, to ensure that each account number is unique for the “TB Synchronisation” portion of the Management Pack.
For example in Pastel, go to the Management Pack container in the Administrator and find the “GL Account Number” Expression.
Change the expression properties as follows:
Expression Source:
“LedgerMaster”.”AccNumber” + ‘-‘ + “LedgerParameters”.”CoName”
Expression Type: SQL Expression
If you run a “Sample Data” you will notice that all your account numbers have the Company Name appended to it.
This process will be the same for other accounting package data sources as well.
i.e.
ACCPAC would consolidate AccountNo and CompanyName expressions
SAP B1 would consolidate AccountNo and CompanyName expressions
Syspro would consolidate AccountNo and CompanyName expressions
Evolution would consolidate MasterSubAccount and CompanyName expressions
If your Management Pack is of the Drill Down type (D) you will have to apply the same logic to the Ledger Details 2-0 container as well.
Go to the Ledger Details 2-0 container in the Administrator and make the following changes.
Go to Source Container (Join) and add
LEFT JOIN “LedgerParameters” ON “LedgerParameters”.”CoName” = “LedgerParameters”.”CoName”
to the bottom of the SQL join string.
Now find the “Account No” expression and change the expression properties as follows:
Expression Source:
“LedgerTransactions”.”AccNumber” + ‘-‘ + “LedgerParameters”.”CoName”
Expression Type: SQL Expression
If you run a “Sample Data” you will notice that all your account numbers have the Company Name appended to it.
This will ensure that your account numbers, used as the lookup comparator, has the exact same layout in the Balance sheet and the GL Transactions. Please ensure the separator/syntax (in above case, ‘-‘) is exactly the same as when appending the Company name to the Account number.
