40. Customers

Owned by Alan Gilchrist
Last updated: Aug 15, 2022
5 min read

The people you sell your products to

Template Download

Some files contain example data in them, you should remove this before submitting back to us for loading.

Template / Example File

 

Key Field Information

Further Reading about field types and notes are contained in the expand sections below, expand if you need to know more details.

Field Type Information

Name

Description

BIT

0 is NO / 1 is YES
BIT fields must have an entry and can not be blank

Varchar(50)

Is an alpha numeric field of length 50, this can accept letters, number and symbols

INT

Is a single number field   (no decimals)  e.g. 1,2,3,4,5
Fields marked as INT must have an entry

DECIMAL (8,5)

Is a decimal field which can contain 8 before and 5 after the .dot separator

 

Field Notes

  • Default will be used unless you enter something in a field marked as DEFAULT

  • The CL column indicates the code list that this list can be found in within the document.

  • Mandatory fields are marked on the excel spreadsheets and in RED below

  • The field names in the import document can not be changed as these are used for matching.

Data Field Definition

Name

Type and Limitations

Mandatory

CL

Notes

Account

[varchar](8)

Yes

UNIQUE

 

Customers Account Code

MUST BE UNIQUE

Vat

CodeList

Yes

1

Generally everything will use
S - Standard Rate

Country

CodeList

Yes

2

ISO 2 digit country code e.g. GB, FR, CA, CN

Currency

CodeList

Yes

3

ISO 3 digit currency code e.g. GBP, USD, EUR

PriceListGroup

UserCodeList

 

LU

Provide the price list name (this is for the price list pricing   e.g. RETAIL, TRADE, PRIVATE)
Only complete this is you intend to provide price lists, if not then you have to set customers price lists manually afterwards.

AccountName

[varchar](50)

Yes

 

 

Address1

[varchar](40)

Yes

 

The STREET NAME & Number

Address2

[varchar](50)

 

 

The Building Name / Floor / Department

Address3

[varchar](50)

 

 

Locality

Address4

[varchar](50)

Yes

 

Town

PostCode

[varchar](30)

Conditional

 

Depending on the country

TelephoneNumber

[varchar](30)

YES

 

 

Fax

[varchar](30)

 

 

 

Contact

[varchar](30)

YES

 

 

Email

[varchar](50)

Yes

 

Single email address. 

For multiple contacts use the contact import

EarliestDeliveryTime

[smalldatetime]

 

 

Format is 08:30

LatestDeliveryTime

[smalldatetime]

 

 

Format is 11:30

EarliestDeliveryTime2

[varchar](20)

 

 

2nd Set Format is 08:30

LatestDeliveryTime2

[varchar](20)

 

 

2nd Set Format is 11:30

DeliveryInstructions

[varchar](120)

 

 

 

PickInstructions

[varchar](120)

 

 

 

CustomerStatus

CodeList

 

4

See code list

AccountManager

[varchar]

 YES

 

The information for this field comes from the customer base setup you sent to us. Use the short code from your list

Type

[varchar]

 YES

 9

Use Code List, enter code

Outlet

[varchar]

 YES

 6

Use Code List, enter code

Area

[varchar]

 YES

 7

Use Code List, enter code

Source

[varchar]

 YES

 8

Use Code List, enter code

Division

[varchar]

 YES

 9

Use Code List, enter code

Classification

[varchar]

 YES

 10

Use Code List, enter code

PrimaryBankAccount

[int]

YES

LU

Use the codes you provided us during base setup

SecondaryBankAccount

[int]

 

 

Only if required

Use the codes you provided us during base setup

WebSite

[varchar](50)

 

 

 

VATRegNo

[varchar](50)

 

 

 

ReserveCustomerAccount

[varchar](8)

 

 

If this customer is storing their reserves at the warehouse, this is the code which will be used by the warehouse to identify the stocks, generally this is the same as the main account code

WebLogonEnabled

[bit]

YES

 

0 – No

1 - Yes

WebTradingEnabled

[bit]

YES

 

Allows the ability for a customer with reserves to offer those reserves for resale through you.
Default is 0
0 is No and 1 is YES

CreditLimit

[money]

YES

 

Please provide these with existing limit or set this field to be 0.01

SettlementDiscount

[money]

YES

 

Unless using this set it to 0.00 for import

 

PayDueDaysAndTerms

[varchar](30)

 

 

Only used if we need to setup a finance system account or there is no link

PaymentTermsDays

[int]

YES

 

Default to 30 days

Can always be changed later

 

InvoiceDiscount

Decimal(18.2)

YES

 

Default this to 0.00 unless being used

 

OnHold

[bit]

YES

 

Please set all to 0

0 – NO

1 – Yes

 

Memo

[varchar](2048)

 

 

 

LineDiscount

[decimal](18 3) 
DEFAULT ((0))

 

 

Default is 0.00
Any figures to two decimal places

Duty

[varchar](10)

 

LU

Your deferment code at the warehouse

If not provided we will fill in from your setup data

CCT

[varchar](10)

 

LU

Your deferment code at the warehouse

If not provided we will fill in from your setup data

TradeCustomerReserve

[bit] 

YES

 

Allows the ability for a customer with reserves to offer those reserves for resale through you.

0 is No and 1 is YES

DateCreated

[datetime]

 

 

Enter the date if you know it otherwise it will be the import date

InSage

[bit]

YES

 

Ignore the name, indicate whether this account already exists in your finance system.

IsEmailEnable

[bit]

YES

 

Send Invoices via EMAIL

Default is 1  -
Please ensure you supply an email address in ExportOptionEmail
0 is No and 1 is YES

 

See Note 3

IsPrintEnable

[bit]

YES

 

Printing Customers Invoices

Default is 0
0 is No and 1 is YES

UseContactList

[bit]

YES

 

Printing Customers Invoices

Uses the Contact List email instead of the “export option email” address

PLEASE SET TO 0

You can change this later

 

See Note 3

ExportOptionEmail

[varchar](50)

 

Yes

Email Address where to send the invoice
Single email address only
If you want multiple use the Contact list import and “Use Contact List” option

 

See Note 3

ReserveWarehouse

CodeList

 

LU

If this customer holds stock which warehouse code is this stock held in

CommissionOnProfit

[bit] 

YES

 

For Trading Reserves

Default is 0
0 is No and 1 is YES

CommissionOnProfitSalesValue

[bit] 

YES

 

For Trading Reserves

Default is 0
0 is No and 1 is YES

CommisionOnSalesValue

[bit]

YES

 

For Trading Reserves

Default is 0
0 is No and 1 is YES

Commission

[decimal](18 2)

YES

 

For Trading Reserves

Default is 0.00
Any figures to two decimal places

AdminChargePerSalesOrder

[decimal](18 2)

YES

 

For Trading Reserves

Default is 0.00
Any figures to two decimal places

StorageCharged

[bit]

YES

 

Storage of Customer Reserves

Default is 0
0 is No and 1 is YES

ChargePerBottlePerMonth

[decimal](18 2) 

YES

 

Storage of Customer Reserves

Default is 0.00
Any figures to two decimal places

MinChargePerRotation

[decimal](18 2) 

YES

 

Storage of Customer Reserves

Default is 0.00
Any figures to two decimal places

CreditAllowed

[bit]

YES

 

Storage of Customer Reserves

Will we credit if they remove part way through a charge year ?

Default is 0
0 is No and 1 is YES

IsExcludedFromReports

[bit]

YES

 

If this is an internal or staff accounts.

Keep this away from margin reports

Default is 0
0 is No and 1 is YES

FKDeliveryService

CodeList

 

LU

Alternative Delivery Service

Setting the default

Unless you use this then leave it empty

DefaultSOAck

[bit]

YES

 

Whether by default we send a SO Acknowledgement after each order, you can always switch this on if needed at order time

Default is 0
0 is No and 1 is YES

APICustomerID

[bigint]

 

 

WEB API

Where the web api is using this code not the customer code for inbound orders.

CustomerAlternativeTrader

[int]

 

 

Optional Module –

Unless you use this then leave it empty

LegalType

[int]

YES

 

1 – Natural Person

2 – Business

PaymentMethod

[int]

YES

LU

How will your customer normally pay you.

List 20

PaymentTermsBasis

[int]

YES

LU

List

 

 

 

 

 

Notes

 

 

Notes

1

Group Account – if you need to setup a customer with a group finance account this needs to take place after the system is handed over only then are you able to add the value to the field.

2

On Hold – has to be set manually by the customer after we go live.

3

Invoices by Email Options

On the customer account import there are three fields you need to set if you want to send email invoices out to your customer

  • ISEmailEnabled = 1

  • UseContactList = 0

  • ExportOptionEmail = the single contact email address

 

If you want to use the contact list OR you want to send to multiple customers then please change the following field in the customer contact

  • UseContactList = 1

And within the customer contact you need to set the following field

  • SOPDOCs = 1    for each contact who will receive a copy of the SOP document (e.g. Invoice, acknowledgement etc.)

 

In theory if you set the customer to UseContactList = 1 you would not normally have to provide an entry in the ExportOptionEmail however we prefer you to send this anyway

Copyright Ontech Solutions 2017-2024. All rights reserved, no part may be replicated or distributed without the express permission of the owner.