Customers
This is used to upload new Customer records to Sage, however, if this customer already exists in Sage the record will be updated with the other fields in the table. The key field is AccountRef which corresponds to the A/C customer account reference field in Sage.
Field Mapping (mandatory fields in RED)
|
E2S Field |
SAGE Field |
Location within Sage |
Data Type |
Notes |
|
ID |
N/A |
N/A |
int |
Used internally and not uploaded to Sage |
|
AccountRef |
A/C |
Customer Record - Account Details |
nvarchar(8) |
The key field for this tab. This will be the A/C or account ref for the customer after it's uploaded. If it already exists in Sage the customer will be updated. |
|
Name |
Company Name |
Customer Record - Account Details |
nvarchar(60) |
|
|
Add1 |
Street1 |
Customer Record - Registered Address |
nvarchar(60) |
|
|
Add2 |
Street2 |
Customer Record - Registered Address |
nvarchar(60) |
|
|
Add3 |
Town |
Customer Record - Registered Address |
nvarchar(60) |
|
|
Add4 |
County |
Customer Record - Registered Address |
nvarchar(60) |
|
|
Add5 |
Postcode |
Customer Record - Registered Address |
nvarchar(60) |
|
|
ContactName |
Contact Name |
Customer Record - Contact Information |
nvarchar(30) |
|
|
TradeContact |
Trade Contact |
Customer Record - Contact Information |
nvarchar(30) |
|
|
|
Email 1 |
Customer Record - Contact Information |
nvarchar(255) |
|
|
Email2 |
Email 2 |
Customer Record - Contact Information |
nvarchar(255) |
|
|
Email3 |
Email 3 |
Customer Record - Contact Information |
nvarchar(255) |
|
|
Website |
Website |
Customer Record - Contact Information |
nvarchar(255) |
|
|
Telephone |
Telephone |
Customer Record - Contact Information |
nvarchar(30) |
|
|
Telephone2 |
Telephone 2 |
Customer Record - Contact Information |
nvarchar(30) |
|
|
Fax |
Fax |
Customer Record - Contact Information |
nvarchar(30) |
|
|
Analysis1 |
Analysis1 |
Defaults Tab - Custom Fields |
nvarchar(30) |
|
|
Analysis2 |
Analysis2 |
Defaults Tab - Custom Fields |
nvarchar(30) |
|
|
Analysis3 |
Analysis3 |
Defaults Tab - Custom Fields |
nvarchar(30) |
|
|
Terms |
Trading Terms Text |
Credit Control Tab - Terms |
nvarchar(30) |
|
|
DefNomCode |
Default Nominal Code |
Defaults Tab - Misc Defaults |
nvarchar(8) |
|
|
DefTaxCode |
Default Tax Code |
Defaults Tab - Misc Defaults |
nvarchar(2) |
|
|
Discount% |
Discount % |
Defaults Tab - Pricing and Discounting |
decimal(18, 2) |
|
|
CreditLimit |
Credit Limit |
Credit Control Tab - Terms |
decimal(18, 2) |
|
|
DUNS |
DUNS Number |
Credit Control Tab - Terms |
nvarchar(9) |
|
|
IBAN |
IBAN |
Bank Tab - Payment Details |
nvarchar(60) |
|
|
VATRegNo |
VAT Number |
Customer Record - Registered Address |
nvarchar(8) |
|
|
CountryCode |
Country |
Customer Record - Registered Address |
nvarchar(2) |
|
|
SendInvEmail |
Email Settings - Option 1 |
Customer Record - Email Settings |
nvarchar(3) |
'Yes' or 'No' |
|
SendLettersEmail |
Email Settings - Option 2 |
Customer Record - Email Settings |
nvarchar(3) |
'Yes' or 'No' |
|
BankName |
Bank Name |
Customer Record - Bank |
nvarchar(60) |
|
|
BankAccountName |
Account Name |
Customer Record - Bank |
nvarchar(60) |
|
|
BankAdd1 |
Street1 |
Customer Record - Bank |
nvarchar(60) |
|
|
BankAdd2 |
Street2 |
Customer Record - Bank |
nvarchar(60) |
|
|
BankAdd3 |
Town |
Customer Record - Bank |
nvarchar(60) |
|
|
BankAdd4 |
County |
Customer Record - Bank |
nvarchar(60) |
|
|
BankAdd5 |
:Post Code |
Customer Record - Bank |
nvarchar(60) |
|
|
BankSortCode |
Sort Code |
Customer Record - Bank |
nvarchar(10) |
|
|
BankAccountNo |
Account Number |
Customer Record - Bank |
nvarchar(60) |
|
|
Memo |
Memo |
Customer Record - Memo |
nvarchar(MAX) |
|
|
Currency |
Currency |
Defaults Tab - Misc Defaults |
nvarchar(3) |
Only used for new customers in Foreign Trader Sage companies |
|
DiscountType |
Additional Discount |
Defaults Tab - Pricing and Discounting |
int |
|
|
AccountStatus |
Account Status |
Credit Control Tab - Restrictions |
int |
Numeric status value (Account On Hold also set where appropriate) |
|
Dept |
Department |
Defaults Tab - Misc Defaults |
nvarchar(1) |
|
|
Status |
N/A |
N/A |
nvarchar(8) |
Used internally and not uploaded to Sage. Set to 'New', 'Error' or 'Sent' normally |
Script to create the table...
CREATE TABLE [dbo].[Customers](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccountRef] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Add1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Add2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Add3] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Add4] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Add5] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContactName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TradeContact] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Website] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Telephone] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Telephone2] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Analysis1] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Analysis2] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Analysis3] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Terms] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DefNomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DefTaxCode] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Discount%] [decimal](18, 2) NULL,
[CreditLimit] [decimal](18, 2) NULL,
[DUNS] [nvarchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IBAN] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VATRegNo] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountryCode] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SendInvEmail] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SendLettersEmail] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BankName] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BankAccountName] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BankAdd1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BankAdd2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BankAdd3] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BankAdd4] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BankAdd5] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BankSortCode] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BankAccountNo] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Memo] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Currency] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DiscountType] [int] NULL,
[AccountStatus] [int] NULL,
[Dept] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Customers_Status] DEFAULT (N'New'),
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]