This is used to upload new Supplier records to Sage, however, if this supplier 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 supplier 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 |
Supplier Record - Account Details |
8 |
The key field for this tab. This will be the A/C or account ref for the supplier after it's uploaded. If it already exists in Sage the supplier will be updated. |
Name |
Company Name |
Supplier Record - Account Details |
nvarchar(60) |
|
Add1 |
Street1 |
Supplier Record - Registered Address |
nvarchar(60) |
|
Add2 |
Street2 |
Supplier Record - Registered Address |
nvarchar(60) |
|
Add3 |
Town |
Supplier Record - Registered Address |
nvarchar(60) |
|
Add4 |
County |
Supplier Record - Registered Address |
nvarchar(60) |
|
Add5 |
Postcode |
Supplier Record - Registered Address |
nvarchar(60) |
|
ContactName |
Contact Name |
Supplier Record - Contact Information |
nvarchar(30) |
|
TradeContact |
Trade Contact |
Supplier Record - Contact Information |
nvarchar(30) |
|
|
Email 1 |
Supplier Record - Contact Information |
nvarchar(255) |
|
Email2 |
Email 2 |
Supplier Record - Contact Information |
nvarchar(255) |
|
Email3 |
Email 3 |
Supplier Record - Contact Information |
nvarchar(255) |
|
Website |
Website |
Supplier Record - Contact Information |
nvarchar(255) |
|
Telephone |
Telephone |
Supplier Record - Contact Information |
nvarchar(30) |
|
Telephone2 |
telephone 2 |
Supplier Record - Contact Information |
nvarchar(30) |
|
Fax |
Fax |
Supplier 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) |
|
VATRegNo |
VAT Number |
Supplier Record - Registered Address |
nvarchar(8) |
|
CountryCode |
Country |
Supplier Record - Registered Address |
nvarchar(2) |
|
SendRemEmail |
Email Settings - Option 1 |
Supplier Record - Email Settings |
nvarchar(3) |
'Yes' or 'No' |
SendLettersEmail |
Email Settings - Option 2 |
Supplier Record - Email Settings |
nvarchar(3) |
'Yes' or 'No' |
Memo |
Memo |
Supplier Record - Memo |
nvarchar(MAX) |
|
Currency |
Currency |
Defaults Tab - Misc Defaults |
nvarchar(3) |
Only used for new customers in Foreign Trader Sage companies |
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].[Suppliers](
[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,
[VATRegNo] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountryCode] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SendRemEmail] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SendLettersEmail] [nvarchar](3) 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,
[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_Suppliers_Status] DEFAULT (N'New'),
CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]