Products

Parent Previous Next


Products are created on upload or updated if the key field (ProductCode) already exists 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

ProductCode

Product Code

Product Record - Product Details

nvarchar(30)

The key field for this tab. If this code is already in Sage the other fields on the xls are updated

Description

Description

Product Record - Product Details

nvarchar(60)


DefSalesNomCode

Sales Nominal Code

Product Record - Defaults

nvarchar(8)


DefPurNomCode

Purchase Nominal Code

Product Record - Defaults

nvarchar(8)


DefTaxCode

Tax Code

Product Record - Defaults

nvarchar(2)


DefSupplier

Supplier A/C

Product Record - Defaults

nvarchar(8)


UnitOfSale

Unit Of Sale

Product Record - Sales Price

nvarchar(8)


SalesPrice

Price

Product Record - Sales Price

decimal(18, 2)


LastPurchasePrice

Last Cost Price (Standard)

Product Record - Ordering

decimal(18, 2)


Location

Location

Product Record - Product Details

nvarchar(16)


CustomA

Category A

Product Record - Web - Custom Fields

nvarchar(60)


CustomB

Category B

Product Record - Web - Custom Fields

nvarchar(60)


CustomC

Category C

Product Record - Web - Custom Fields

nvarchar(60)


ROL

Re-order Level

Product Record - Status

decimal(18, 2)


ROQ

Re-order Quantity

Product Record - Status

decimal(18, 2)


Barcode

Bar Code

Product Record - Product Details

nvarchar(60)

Sage 50 v2011 onwards only

IntraStatComCode

IntraStat Com. Code

Product Record - Product Details

nvarchar(8)


ComCode

Com. Code Description

Product Record - Product Details

nvarchar(8)


PartNo

Part No.

Product Record - Defaults

nvarchar(16)


Weight

Weight (kg)

Product Record - Product Details

decimal(18, 2)


Category

Category

Product Record - Product Details

nvarchar(2)


ItemType

Item Type

Product Record - Product Details

nvarchar(1)

0= Stock, 1 = Non-stock, 2 = Service  

Dept

Department

Product Record - Product Details

nvarchar(2)


WebDescription

Description

Product Record - Web - Web Defaults

nvarchar(60)


WebDetails

Details

Product Record - Web - Web Defaults

nvarchar(1023)


WebPublish

Publish to Web

Product Record - Web - Web Defaults

nvarchar(3)

Yes/No

WebSpecialOffer

Special Offer

Product Record - Web - Web Defaults

nvarchar(3)

Yes/No

WebImage

Image

Product Record - Web - Image

nvarchar(60)

The file must exist in your Sage company images folder

ComponentCode1-20

Product Code

Product Record - Bill of Materials

nvarchar(30)


ComponentQty1-20

Quantity

Product Record - Bill of Materials

decimal(18, 2)


DiscountALevel1-10Qty

Quantity

Product Record - Discount

decimal(18, 2)


DiscountALevel1-10Rate

Discount %

Product Record - Discount

decimal(18, 2)


DiscountBLevel1-10Qty

Quantity

Product Record - Discount

decimal(18, 2)


DiscountBLevel1-10Rate

Discount %

Product Record - Discount

decimal(18, 2)


DiscountCLevel1-10Qty

Quantity

Product Record - Discount

decimal(18, 2)


DiscountCLevel1-10Rate

Discount %

Product Record - Discount

decimal(18, 2)


DiscountDLevel1-10Qty

Quantity

Product Record - Discount

decimal(18, 2)


DiscountDLevel1-10Rate

Discount %

Product Record - Discount

decimal(18, 2)


DiscountELevel1-10Qty

Quantity

Product Record - Discount

decimal(18, 2)


DiscountELevel1-10Rate

Discount %

Product Record - Discount

decimal(18, 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].[Products](

       [ID] [int] IDENTITY(1,1) NOT NULL,

       [ProductCode] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [Description] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [DefSalesNomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [DefPurNomCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

       [DefSupplier] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [UnitOfSale] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [SalesPrice] [decimal](18, 2) NULL,

       [LastPurchasePrice] [decimal](18, 2) NULL,

       [Location] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [CustomA] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [CustomB] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [CustomC] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ROL] [decimal](18, 2) NULL,

       [ROQ] [decimal](18, 2) NULL,

       [Barcode] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [IntraStatComCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComCode] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [PartNo] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [Weight] [decimal](18, 2) NULL,

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

       [ItemType] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

       [WebDescription] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [WebDetails] [nvarchar](1023) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [WebPublish] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [WebSpecialOffer] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [WebImage] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

)         [ComponentCode1] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty1] [decimal](18, 2) NULL,

       [ComponentCode2] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty2] [decimal](18, 2) NULL,

       [ComponentCode3] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty3] [decimal](18, 2) NULL,

       [ComponentCode4] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty4] [decimal](18, 2) NULL,

       [ComponentCode5] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty5] [decimal](18, 2) NULL,

       [ComponentCode6] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty6] [decimal](18, 2) NULL,

       [ComponentCode7] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty7] [decimal](18, 2) NULL,

       [ComponentCode8] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty8] [decimal](18, 2) NULL,

       [ComponentCode9] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty9] [decimal](18, 2) NULL,

       [ComponentCode10] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty10] [decimal](18, 2) NULL,

       [ComponentCode11] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty11] [decimal](18, 2) NULL,

       [ComponentCode12] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty12] [decimal](18, 2) NULL,

       [ComponentCode13] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty13] [decimal](18, 2) NULL,

       [ComponentCode14] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty14] [decimal](18, 2) NULL,

       [ComponentCode15] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty15] [decimal](18, 2) NULL,

       [ComponentCode16] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty16] [decimal](18, 2) NULL,

       [ComponentCode17] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty17] [decimal](18, 2) NULL,

       [ComponentCode18] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty18] [decimal](18, 2) NULL,

       [ComponentCode19] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty19] [decimal](18, 2) NULL,

       [ComponentCode20] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

       [ComponentQty20] [decimal](18, 2) NULL,

       [DiscountALevel1Qty] [decimal](18, 2) NULL,

       [DiscountALevel1Rate] [decimal](18, 2) NULL,

       [DiscountALevel2Qty] [decimal](18, 2) NULL,

       [DiscountALevel2Rate] [decimal](18, 2) NULL,

       [DiscountALevel3Qty] [decimal](18, 2) NULL,

       [DiscountALevel3Rate] [decimal](18, 2) NULL,

       [DiscountALevel4Qty] [decimal](18, 2) NULL,

       [DiscountALevel4Rate] [decimal](18, 2) NULL,

       [DiscountALevel5Qty] [decimal](18, 2) NULL,

       [DiscountALevel6Rate] [decimal](18, 2) NULL,

       [DiscountALevel7Qty] [decimal](18, 2) NULL,

       [DiscountALevel7Rate] [decimal](18, 2) NULL,

       [DiscountALevel8Qty] [decimal](18, 2) NULL,

       [DiscountALevel8Rate] [decimal](18, 2) NULL,

       [DiscountALevel9Qty] [decimal](18, 2) NULL,

       [DiscountALevel9Rate] [decimal](18, 2) NULL,

       [DiscountALevel10Qty] [decimal](18, 2) NULL,

       [DiscountALevel10Rate] [decimal](18, 2) NULL,

       [DiscountBLevel1Qty] [decimal](18, 2) NULL,

       [DiscountBLevel1Rate] [decimal](18, 2) NULL,

       [DiscountBLevel2Qty] [decimal](18, 2) NULL,

       [DiscountBLevel2Rate] [decimal](18, 2) NULL,

       [DiscountBLevel3Qty] [decimal](18, 2) NULL,

       [DiscountBLevel3Rate] [decimal](18, 2) NULL,

       [DiscountBLevel4Qty] [decimal](18, 2) NULL,

       [DiscountBLevel4Rate] [decimal](18, 2) NULL,

       [DiscountBLevel5Qty] [decimal](18, 2) NULL,

       [DiscountBLevel6Rate] [decimal](18, 2) NULL,

       [DiscountBLevel7Qty] [decimal](18, 2) NULL,

       [DiscountBLevel7Rate] [decimal](18, 2) NULL,

       [DiscountBLevel8Qty] [decimal](18, 2) NULL,

       [DiscountBLevel8Rate] [decimal](18, 2) NULL,

       [DiscountBLevel9Qty] [decimal](18, 2) NULL,

       [DiscountBLevel9Rate] [decimal](18, 2) NULL,

       [DiscountBLevel10Qty] [decimal](18, 2) NULL,

       [DiscountBLevel10Rate] [decimal](18, 2) NULL,

       [DiscountCLevel1Qty] [decimal](18, 2) NULL,

       [DiscountCLevel1Rate] [decimal](18, 2) NULL,

       [DiscountCLevel2Qty] [decimal](18, 2) NULL,

       [DiscountCLevel2Rate] [decimal](18, 2) NULL,

       [DiscountCLevel3Qty] [decimal](18, 2) NULL,

       [DiscountCLevel3Rate] [decimal](18, 2) NULL,

       [DiscountCLevel4Qty] [decimal](18, 2) NULL,

       [DiscountCLevel4Rate] [decimal](18, 2) NULL,

       [DiscountCLevel5Qty] [decimal](18, 2) NULL,

       [DiscountCLevel6Rate] [decimal](18, 2) NULL,

       [DiscountCLevel7Qty] [decimal](18, 2) NULL,

       [DiscountCLevel7Rate] [decimal](18, 2) NULL,

       [DiscountCLevel8Qty] [decimal](18, 2) NULL,

       [DiscountCLevel8Rate] [decimal](18, 2) NULL,

       [DiscountCLevel9Qty] [decimal](18, 2) NULL,

       [DiscountCLevel9Rate] [decimal](18, 2) NULL,

       [DiscountCLevel10Qty] [decimal](18, 2) NULL,

       [DiscountCLevel10Rate] [decimal](18, 2) NULL,

       [DiscountDLevel1Qty] [decimal](18, 2) NULL,

       [DiscountDLevel1Rate] [decimal](18, 2) NULL,

       [DiscountDLevel2Qty] [decimal](18, 2) NULL,

       [DiscountDLevel2Rate] [decimal](18, 2) NULL,

       [DiscountDLevel3Qty] [decimal](18, 2) NULL,

       [DiscountDLevel3Rate] [decimal](18, 2) NULL,

       [DiscountDLevel4Qty] [decimal](18, 2) NULL,

       [DiscountDLevel4Rate] [decimal](18, 2) NULL,

       [DiscountDLevel5Qty] [decimal](18, 2) NULL,

       [DiscountDLevel6Rate] [decimal](18, 2) NULL,

       [DiscountDLevel7Qty] [decimal](18, 2) NULL,

       [DiscountDLevel7Rate] [decimal](18, 2) NULL,

       [DiscountDLevel8Qty] [decimal](18, 2) NULL,

       [DiscountDLevel8Rate] [decimal](18, 2) NULL,

       [DiscountDLevel9Qty] [decimal](18, 2) NULL,

       [DiscountDLevel9Rate] [decimal](18, 2) NULL,

       [DiscountDLevel10Qty] [decimal](18, 2) NULL,

       [DiscountDLevel10Rate] [decimal](18, 2) NULL,

       [DiscountELevel1Qty] [decimal](18, 2) NULL,

       [DiscountELevel1Rate] [decimal](18, 2) NULL,

       [DiscountELevel2Qty] [decimal](18, 2) NULL,

       [DiscountELevel2Rate] [decimal](18, 2) NULL,

       [DiscountELevel3Qty] [decimal](18, 2) NULL,

       [DiscountELevel3Rate] [decimal](18, 2) NULL,

       [DiscountELevel4Qty] [decimal](18, 2) NULL,

       [DiscountELevel4Rate] [decimal](18, 2) NULL,

       [DiscountELevel5Qty] [decimal](18, 2) NULL,

       [DiscountELevel6Rate] [decimal](18, 2) NULL,

       [DiscountELevel7Qty] [decimal](18, 2) NULL,

       [DiscountELevel7Rate] [decimal](18, 2) NULL,

       [DiscountELevel8Qty] [decimal](18, 2) NULL,

       [DiscountELevel8Rate] [decimal](18, 2) NULL,

       [DiscountELevel9Qty] [decimal](18, 2) NULL,

       [DiscountELevel9Rate] [decimal](18, 2) NULL,

       [DiscountELevel10Qty] [decimal](18, 2) NULL,

       [DiscountELevel10Rate] [decimal](18, 2) NULL,

       [Status] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Products_Status]  DEFAULT (N'New'),

 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 

(

       [ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]