Implementation > Import Clients & Debtors
- Nicky Harvey
- Kat Grinter
- Kye Hush
When first setting up your WorkBook system, a starting set of data will be imported, including your client and debtor information. To import data successfully it is important to understand the data structures and the format required in the import data sheet.
Things to note:
Data is imported once, so it is important to ensure accuracy and consistency prior to uploading.
Not all fields are required at import, optional fields can be added to the system manually at a later date when/as required.
IMPORT CLIENTS & DEBTORS
Download the clients and debtors import file here.
Clients
Clients are global.
A client record includes all customer related information such as resources, addresses, phone numbers, documents and most importantly links jobs for reporting purposes.
There is total freedom in defining the customer level and customer names.
WorkBook includes default non-billable clients to set-up internal jobs against, i.e. Internal, New Business, Leave etc.
Debtors
Debtors are unique per company.
A debtor is defined as the “paying customer” or “the invoice address customer”.
Accounts Receivable are always tracked against the Debtor and not the Client.
All invoicing must have a Debtor assigned to it before it can be approved.
COLUMN HEADER | FIELD DESCRIPTION | FORMAT | REQUIRED |
|---|---|---|---|
CompId | Company ID: The company ID that the client belongs to. | Number and/or text If there is only one company in your WorkBook setup, simply use '1' in this field | Yes |
CustNum | Customer number: A unique code used to identify each client. This code is referenced in other imports and also becomes the Debtor/AR account number. | Number and/or text E.g. 101, 102, 103 etc | Yes |
CustName | Customer name: Client name as you would like it to appear on price quotes, sales invoices, reports and statements. | Text | Yes |
CustTypeId | Customer type: Defines real clients opposed to internal clients. Internal clients are used for holiday, illness & absence, and administration and do not need to be included in the import file. Please use '1' for all real customers. | Number 1 = Customer | Yes |
AdrLine1 | Address Line 1: Not required, but it is strongly suggested to include client address details in import file as they are used across client facing outputs, e.g. price quotes and invoices. Details can be added manually to each client record post import if preferred. | Text | No, but recommended |
AdrLine2 | Address Line 2 | Text | No, but recommended |
AdrLine3 | Address Line 3 | Text | No, but recommended |
Postcode | Postcode | Number and/or text | No, but recommended |
City | City | Text | No, but recommended |
CountryCode | Country Code: WorkBook assigns a two-letter code to each country. Check with your WorkBook consultant if your country code is not referenced here. | Text E.g. AU, DE, DK, HK, NZ, SG, UK, US | No |
Tel | Telephone number: Head office or main business contact number. | Number | No |
Fax | Fax number: Head office or main business fax number. | Number | No |
AccContact | Accounts contact: Name of your accounts receivable / debtor contact person. | Text | No |
AccContactTel | Accounts contact telephone: Number of your accounts receivable / debtor contact person. | Number and/or text | No |
Accounts contact email: Email address of your accounts receivable / debtor contact person. | Text | No | |
wwwAdr | Website: Client’s URL for reference. | Text | No |
Industry | Industry: The industry the client belongs to, for internal reference only. | Text | No |
LCID | Language: Each client profile is assigned a default language. WorkBook assigns a four-digit code to available languages. Check with your WorkBook consultant if your desired language is not referenced here. | Number 1030 = Danish | Yes |
PayTermCd | Payment terms: The default number of days from invoice date that payment is due. Value must reference a payment term that already exists in your system. Payment terms are defined in Settings > Global system settings > Payment terms. | Number and text 10D = 10 days | Yes |
ArpAccGrpCd | AR posting group: Defines the posting group and associated debtor account for each client. Value must reference a posting group that already exists in your system. Posting groups are defined in Settings > Finance > AR/AP Groups. | Text DEB EXT = External debtors | Yes |
CurrCd | Currency code: The debtor’s default currency code. Value must reference a currency that already exists in your system. Currencies are defined in Settings > Global system settings > Currency codes and rates > Currency setup. | Text E.g. AUD, DKK, EUR, GBP, HKD, NZD, USD, etc. | Yes |
VatCd | Tax code: The client’s default tax code (GST/VAT) Value must reference a code that already exists in your system. Tax codes are defined in Settings > Finance > GST/VAT settings > AR/AP VAT codes. | Text E.g. | Yes |
VatNo | Tax number: The debtor’s registered tax (GST/VAT) number. | Number and/or text | No |
EAN | EAN or GLN: The debtor’s EAN or GLN number. | Number and/or text | No |
PublicRegNo | Public registration number: The debtor’s public registration number. | Number and/or text | No |
Pricelist | Price list: Defines the default price list/rate card for each client. To add multiple price lists to a client, include names separated with a comma, noting that the first price list will be the default when creating a job. Value must reference a price list that already exists in your system. Price lists are created and managed via Settings > Price lists & activities > Price lists. | Text | No, but recommended |
RespEmpId | Responsible employee: Include the import ID of the employee who is responsible for the client, they will be assigned as the Account Manager for the client. (Check your employee import spreadsheet for EmpNum code). | Number E.g. 101
| No, but recommended |
EmpId | Employee ID: |
|
|
PrintStatement | Print statement: Defines if the debtor must be included in statements. | Number 0 = No | Yes |
CreditMaxCheck | Credit maximum check method: Select a method for calculating the credit amount for the client. | Number 0 = Use system standard | No |
CreditMaxAmt | Credit maximum: What is the maximum credit limit amount for the client. Calculated according to the CreditMaxCheck method selected above.
| Number E.g. 20000 | No |
IsBlocked | Block debtor: Sets the debtor to blocked once imported. | Number 0 = No | Yes |
CountryStateCd | Client’s State: Used for US debtors only. | Text E.g. CA, FL, NY, NJ etc | No |
CountryCountyCd | Client’s County: Used for US debtors only. | Text E.g. Benton, Carlton, Dorchester etc | No |
SalesTaxCodeCd | Sales tax code: Used for US and Canadian debtors only. Value must reference a code that already exists in your system. Managed via Settings > Tax > Sales tax setup. | Text | No |
ExtNo | External number: Field can be used to record an external number or code against the client profile. | Number and/or text | No |
QuoteVatValue | Tax rate: The default tax (GST/VAT) % rate to include on price quotes for this client. Can also be updated manually on jobs as required. | Decimal E.g. 0.10 = 10%, 0.15 = 15%, 0.25 = 25% | Yes |
QuoteVatShow | Include tax on price quotes: Defines if tax (GST/VAT) should be added to price quotes by default. | Number 0 = No | Yes |
ReportingGroup | Reporting group: Used in certain finance reports. For example, US reporting that supports the 1099 form. Value must reference a reporting group that already exists in your system. Reporting groups are created and managed via Settings > Finance settings > AR/AP Groups. | Text | No |
CreateCustomer | Create customer: Defines if the record should be imported as a client. | Number 0 = No | Yes |
CreateDebtor | Create debtor: Defines if the record should be imported as a debtor. | Number 0 = No | Yes |
CustomerUpdate | Update customer: Defines if a record exists that needs to be updated | Number 0 = No | No |
DebtorUpdate | Update debtor: Defines if a record exists that needs to be updated | Number 0 = No | No |
Related articles
© Tangram 2022. All rights reserved.