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

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)


DefTaxCode

Default Tax Code

Defaults Tab - Misc Defaults

nvarchar(2)


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)


PaymentDaysDue

Payment Due

Credit Control Tab - Terms

nvarchar(2)


PaymentDaysFrom

Payment Due From

Credit Control Tab - Terms

nvarchar(2)


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,

       [DefTaxCode] [nvarchar](2) 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,

       [PaymentDaysDue] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [PaymentDaysFrom] [nvarchar](2) 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]