Preparing ETL data

Use this guide with the schema reference when preparing a source database for an Efimis ETL import. The schema reference describes each table and field. This page explains how to fill them so your data needs as little mapping as possible.

How mapping works

During the migration, Efimis lists any value it can’t match to your ETL data, and asks you to map each one to a record that already exists in Efimis. For example, a journal reference that isn’t in your ETL data is mapped to an existing Efimis journal of the same type.

Efimis suggests a record for each value where it can, by matching names or by using the Efimis default, such as the default team or division. Check each suggestion and fill in any that are blank. You can’t continue to validation until every value is mapped.

Your source value	What happens
An ImportId for a record in your ETL data	Efimis matches it automatically.
Any other value, or a blank where a value is required	You choose the existing Efimis record to use.

Each section below explains which values are matched automatically and which need mapping. The fewer values that need mapping, the quicker the migration.

General rules

• Give every row a stable, unique ImportId. Use the identifier from the source system where possible.
• When one ETL row refers to another, use the other row’s ImportId.
• In type and state columns, use only the values listed in the schema reference. A row with any other value isn’t imported, and neither are the rows it contains, such as a journal’s accounts or an invoice’s items. The extraction report names the column and the values it accepts.

Journals and accounts

Add each journal you want to import to Journals, and add its accounts to Accounts. Efimis creates each of these journals with its standard accounts, then adds your accounts to it.

• Journals must be Office or Trust journals, and need an ImportId and a Name.
• Accounts must use the Standard or Bank account type.
• Give each account a unique code within its journal. Resolve duplicate codes before submitting the import. If a code is already used by a standard account or another account in the same journal, Efimis assigns the imported account a new code.

No mapping needed when a journal field contains the ImportId of a journal in Journals. Journal fields include Divisions.OfficeJournalId, Divisions.TrustJournalId, cashbook accounts, purchases and historical transactions.

Mapping needed when a journal field refers to a journal that isn’t in Journals. You map it to an existing Efimis journal.

Adding accounts to an existing Efimis journal

To add accounts to a journal that already exists in Efimis, leave the journal out of Journals and add its accounts to Accounts with a JournalId of your choosing. During the migration you map that journal ID to the existing Efimis journal, and the accounts are added to it.

• Every account with that JournalId must have the same JournalType, either Office or Trust.
• Divisions, purchases and historical transactions that refer to the journal must use it as the same type. For example, a purchase journal must be an Office journal.

If these don’t match, the extraction stops with an error that names the journal.

Cashbook accounts

Cashbook accounts are the Bank accounts in Accounts. Give each one a JournalId. A bank account with no journal stops the extraction with an error, and this can’t be fixed by mapping.

Account references

Several tables refer to an account: purchase items, historical transactions and their splits, and matter trust balances in LedgerBalances.

No mapping needed when the reference contains the ImportId of an account in the same journal’s Accounts.

For matter trust balances in LedgerBalances, use an account from any trust journal. They aren’t tied to one journal.

Mapping needed when the reference is to an account that isn’t in your ETL data. You map it to an account in a journal that already exists in Efimis.

The standard accounts Efimis creates for your imported journals don’t exist yet when you map, so you can’t map to them. If a transaction needs one of these accounts, add the account to Accounts and refer to it by its ImportId instead.

Not imported when a historical transaction, or one of its splits, refers to an account that belongs to a different journal in your ETL data. The transaction is left out and reported, because the account is in the wrong journal rather than missing. Correct the journal or the account in the source data.

For purchase items, only Account-type items need an AccountId. Leave it blank for other item types. If you do fill it in, it follows the same rule.

Historical postings with a single split are balanced automatically. If the posting is in a journal that isn’t in Journals, you’re asked to map the balancing account.

Divisions

Every Divisions row needs an ImportId, Name, OfficeJournalId and TrustJournalId. Use journal ImportId values for the journal fields.

No mapping needed when Matters.DivisionId contains the ImportId of a division in Divisions.

Mapping needed when Matters.DivisionId refers to a division that isn’t in Divisions, or is blank. A blank value is mapped once, to the division to use by default.

Tax codes

No mapping needed when the tax code is one of the destination region’s tax codes below. Letter case doesn’t matter. Use only the row for your destination region.

Region	Tax codes
Australia (AU)	N-T, GST-S, FRE-S, GST-P, GST-CA, FRE-P
United Kingdom (GB) and Scotland (SCO)	N-T, VAT-S, VAT-RS, VAT-ES, Z-S, VAT-Z-EC, VAT-P, VAT-RP, VAT-EP, Z-P, VAT-P-RC, VAT-P-EC, VAT-P-EZC
Ireland (IE)	N-T, VAT-S, VAT-RS, VAT-SRS, VAT-ES, Z-S, VAT-Z-EC, VAT-P, VAT-RP, VAT-SRP, VAT-EP, Z-P, VAT-P-RC, VAT-P-EC, VAT-P-EZC

Mapping needed when the tax code is any other value, such as a tax-code ID from your source system. Map each distinct value to an Efimis tax code.

• Tax codes on clients, suppliers and accounts are optional. Leave them blank if they don’t apply.
• On invoice items, purchase items, disbursements and historical transaction splits, a blank tax code is mapped to a default tax code.
• Blank sales and purchase tax codes are mapped separately, so each can have its own default.
• For historical transaction splits, TaxType selects the sales or purchase default. Splits with no tax type use a separate general default.
• Tax on work items isn’t imported. Record it on the invoice item that bills the work.

Employees, roles and teams

Teams

No mapping needed when Employees.TeamId contains the ImportId of a team in Teams.

Mapping needed when Employees.TeamId refers to a team that isn’t in Teams, or is blank. A blank value is mapped once, to the team to use by default.

Roles

Employees.RoleIds is a comma-separated list of role IDs, for example 6,13.

Use the standard role IDs below directly; they don’t need mapping.

ID	Role
2	Accountant
3	Cashier
4	Junior Cashier
5	Manager
6	Partner
7	Lawyer
8	Paralegal
9	Access All Matters
10	Approve All Requisitions
11	Approve All Invoices
12	Team Lead
13	Biller

Role ID 1 is ignored. An employee whose only role ID is 1 is treated as having no roles.

Mapping needed when you use your own source role IDs. Map each one to an Efimis role. If an employee’s RoleIds is blank, you’re asked for one role to give those employees by default.

CMA payment methods

CMA receipts and payments are imported with the General payment method. You don’t need to supply or map a payment method.

Before submitting an import

1. Run the schema script against the source SQL Server database.
2. Check that each row has the required fields described in this guide.
3. Check that every cross-table reference uses the other row’s ImportId, and that every account reference points to an account in the same journal.
4. Check that account codes are unique within each journal.
5. Use the destination region’s tax codes wherever you can.
6. Submit the connection string using the import and promote workflow.