Sage 200 Supplier Import/Update

Sage 200 : Extract/Amend/Import Supplier and related Records

The Supplier record in Sage 200 can be created/edited using PostTrans Supplier template, denoted by tag Sage200supp in cell A1. A minimum of Account Reference, and Name are required, to create a Supplier, as defaults values are filled in automatically. When a record is updated, unmapped fields are left un touched.

Supplier Import in Sage 50

The user can simply edit the data in the template, aided by “In Cell Searching”, for codes where needed. Then when ready press [Import] to update changed data into Sage 200.

In addition to giving access to all Supplier fields on the Sage 200 Supplier record, it also allows access to associated data, such as Multiple Phone/Fax/Mobile/Web/e-mail, Contacts and Roles.

To read/write the additional data we add additional Tags, in the case of Multiple Phone Numbers, we can access up to 5 records. Listed in columns:

When extracted the additional records populate the columns. Any changes, or deletions are detected on import, and PostTrans makes the necessary changes to reflect the data in the PostTrans template.

Filter

The Supplier data can be extracted, using the Extract button, and optionally filtered. see How to filter Sage Line 200 extracted data.

Search

The user can search for Nominal, Price List, Department codes by entering the start of the code. Or any part of name, see Code Search for more information.

Demonstration Video

Sage Line 50 - Import Customer Data, also Extract and Edit
	0:00	Video Introduction
	1:10	PostTrans Template Introduction
	3:44	Extract Sage Line 50 Customers
	6:30	Add New Sage Customers
	6:42	Add additional fields to allow Price list and Nominal edit
	10:12	Filter Extracted Sage Line 50 Customer Data
	12:00	How to find more information and Training

Download Supplier Extract/Import Template

To use the template, with Sage 200, install PostTrans. Download
Trial with Sage demo company. Purchase a month subscription for Live Sage Company. Price
Watch the On-line Training course on how to use and customise the templates.

Supplier Import/Update Template

This PostTrans function allows the user to easily extract, amend, and import Sage 200 Supplier records.

The cell A1 has a tag “Sage200supp” to designate the type of import. It uses a series of tags, placed in row 1, to identify the data to be processed.

These tags, listed in table below, can be easily added to a spreadsheet using the Tags button. See Training for an explanation of this.

Supplier Required fields

To Import/Extract/Amend Supplier Data, the following Tags/Fields must be specified:

Sage200supp, SuCRC, SuRef and SuName

Cell A1 must equal Sage200supp

SuRef is used to detect end of spreadsheet. PostTrans will stop Importing/Amending, from row 4 on-wards, when this field is empty.

Restrictions

Unavailable fields - Reporting Password

Tags for Supplier Records

The following PostTrans tags can be used to extract/import Supplier Records into Sage 200 .

See Online Training for Static templates for a full explanation of tags, and how PostTrans reads them to define the Extract/Import template.

Tag Name	Data Type	Description
Important Fields:
Sage200supp	Text Req	Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row
SuCRC	Text Req	Contains CRC number to help PostTrans detect changes in current row.
SuDelete	Text 1	Delete record if "X"
SuSearch^	Text 8	Search for record by entering code or pasting list, at top of column, and pressing `Extract` button In Cell Searching values in table Supp
Account:
SuRef	Text 8 EOF Req	Reference. If auto Allocate numbers is ON then set to "X" to import new records using Sage 200 allocatee numbers.
SuName	Text 60 Req	Company Name
SuShortName	Text 8	Short Name
SuCurrency^	Text 3	Currency, GBP,EUR,USD… In Cell Searching values in table Cur
SuCreditLimit	Double	Credit Limit
SuBalance	Double Read	Balance
CuHidden	Yes/NoTF Read	Hidden Account Sage V2017 onwards
Main Address:
SuMainAdd1	Text 60	Address1
SuMainAdd2	Text 60	Address2
SuMainAdd3	Text 60	Address3
SuMainAdd4	Text 60	Address4
SuMainCity	Text 60	City, Only used if Segmented address is ON
SuMainCounty	Text 60	County, Only used if Segmented address is ON
SuMainPostcode	Text 10	Postcode
SuMainCountry^	Text 60	Country In Cell Searching values in table CountryCode
Main Phone/Fax:
SuMPhCo	Text 5	Main Phone Country Code
SuMPhAr	Text 20	Main Phone Area code
SuMPhNo	Text 200	Main Phone Number
SuMFaxCo	Text 5	Main Fax Country Code
SuMFaxAr	Text 20	Main Fax Area code
SuMFaxNo	Text 200	Main Fax Number
SuMWeb	Text 200	Main Web
Bank Details:
SuAccSortCode	Text 6	Sort code. Must be a number
SuAccNo	Text 8	Account Number. Must be a number
SuAccName	Text 60	Account Name
SuAccPayRef	Text 60	Bank Payment Referacne
Additional Bank Details:
SuIBANNumber	Text 34	IBAN
SuBICNumber	Text 11	BIC/Swift Number
SuRollNumber	Text 30	Roll Number
SuBACSRef	Text 60	BASC referance
SuAddRef	Text 60	Additional referance
SuNonUKSortCode	Text 60	Non UK sort code
Main Contact:
SuSalutation^	Text 20	Salutation In Cell Searching values in table CustomerSalutation
SuFirstName	Text 60	First Name
SuMiddleName	Text 60	Middle Name
SuLastName	Text 60	Last Name
Default Contact Details:
SuPhCo1	Text 5 Rel	Country Code Phone 1
SuPhAr1	Text 20 Rel	Area code Phone 1
SuPhone1	Text 200 Rel	Phone Number 1
SuPhCo2	Text 5 Rel	Country Code Phone 2
SuPhAr2	Text 20 Rel	Area code Phone 2
SuPhone2	Text 200 Rel	Phone Number 2
SuPhCo3	Text 5 Rel	Country Code Phone 3
SuPhAr3	Text 20 Rel	Area code Phone 3
SuPhone3	Text 200 Rel	Phone Number 3
SuPhCo4	Text 5 Rel	Country Code Phone 4
SuPhAr4	Text 20 Rel	Area code Phone 4
SuPhone4	Text 200 Rel	Phone Number 4
SuFaxCo1	Text 5 Rel	Fax Country Code 1
SuFaxAr1	Text 20 Rel	Fax Area Code 1
SuFaxNo1	Text 200 Rel	Fax Number 1
SuFaxCo2	Text 5 Rel	Fax Country Code 2
SuFaxAr2	Text 20 Rel	Fax Area Code 2
SuFaxNo2	Text 200 Rel	Fax Number 2
SuFaxCo3	Text 5 Rel	Fax Country Code 3
SuFaxAr3	Text 20 Rel	Fax Area Code 3
SuFaxNo3	Text 200 Rel	Fax Number 3
SuFaxCo4	Text 5 Rel	Fax Country Code 4
SuFaxAr4	Text 20 Rel	Fax Area Code 4
SuFaxNo4	Text 200 Rel	Fax Number 4
SuMobCo1	Text 5 Rel	Country Code Mobile 1
SuMobAr1	Text 20 Rel	Mobile Area Code 1
SuMobNo1	Text 200 Rel	Mobile Number 1
SuMobCo2	Text 5 Rel	Country Code Mobile 2
SuMobAr2	Text 20 Rel	Mobile Area Code 2
SuMobNo2	Text 200 Rel	Mobile Number 2
SuMobCo3	Text 5 Rel	Country Code Mobile 3
SuMobAr3	Text 20 Rel	Mobile Area Code 3
SuMobNo3	Text 200 Rel	Mobile Number 3
SuMobCo4	Text 5 Rel	Country Code Mobile 4
SuMobAr4	Text 20 Rel	Mobile Area Code 4
SuMobNo4	Text 200 Rel	Mobile Number 4
SuEmail1	Text 200 Rel	Email1
SuEmail2	Text 200 Rel	Email2
SuEmail3	Text 200 Rel	Email3
SuEmail4	Text 200 Rel	Email4
SuWeb1	Text 200 Rel	Web1
SuWeb2	Text 200 Rel	Web2
SuWeb3	Text 200 Rel	Web3
SuWeb4	Text 200 Rel	Web4
Main Contact - Roles:
SuRole1^	Text 10 Rel	Role1, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole2^	Text 10 Rel	Role2, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole3^	Text 10 Rel	Role3, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole4^	Text 10 Rel	Role4, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole5^	Text 10 Rel	Role5, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole6^	Text 10 Rel	Role6, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole7^	Text 10 Rel	Role7, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole8^	Text 10 Rel	Role8, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole9^	Text 10 Rel	Role9, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole10^	Text 10 Rel	Role10, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole11^	Text 10 Rel	Role11, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole12^	Text 10 Rel	Role12, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole13^	Text 10 Rel	Role13, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole14^	Text 10 Rel	Role14, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole15^	Text 10 Rel	Role15, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole16^	Text 10 Rel	Role16, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole17^	Text 10 Rel	Role17, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole18^	Text 10 Rel	Role18, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole19^	Text 10 Rel	Role19, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
SuRole20^	Text 10 Rel	Role20, "Y","P", or "" In Cell Searching values:Role YES, Assigned PREF, Preferred
Trading:
SuCountryCode^	Text 2	Country Code GB,US.. In Cell Searching values in table CountryCode
SuDefTaxCode^	Text 2	Default Tax Code, 0==Exempt,1==Std,2==Zero In Cell Searching values in table Tax
SuTaxRegCode	Text 30	Tax Registration Code
SuMon2Keep	Integer	Months To Keep Transactions
SuOrderPri	Text 1	Order Priority, A-Z
Default Nominal Codes:
SuDefaultNom^	Text 15	Default Nom How Nom/CC/Dep are specified In Cell Searching values in table NOM
SuDefaultCC^	Text 3	Default CC. Only use for setting with formula How Nom/CC/Dep are specified In Cell Searching values in table CC
SuDefaultDep^	Text 3	Default Dep. Only use for setting with formula How Nom/CC/Dep are specified In Cell Searching values in table DEP
Analysis Codes:
SuAnal1^	Text 60	Analyisis code 1 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp01
SuAnal2^	Text 60	Analyisis code 2 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp02
SuAnal3^	Text 60	Analyisis code 3 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp03
SuAnal4^	Text 60	Analyisis code 4 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp04
SuAnal5^	Text 60	Analyisis code 5 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp05
SuAnal6^	Text 60	Analyisis code 6 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp06
SuAnal7^	Text 60	Analyisis code 7 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp07
SuAnal8^	Text 60	Analyisis code 8 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp08
SuAnal9^	Text 60	Analyisis code 9 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp09
SuAnal10^	Text 60	Analyisis code 10 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp10
SuAnal11^	Text 60	Analyisis code 11 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp11
SuAnal12^	Text 60	Analyisis code 12 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp12
SuAnal13^	Text 60	Analyisis code 13 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp13
SuAnal14^	Text 60	Analyisis code 14 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp14
SuAnal15^	Text 60	Analyisis code 15 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp15
SuAnal16^	Text 60	Analyisis code 16 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp16
SuAnal17^	Text 60	Analyisis code 17 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp17
SuAnal18^	Text 60	Analyisis code 18 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp18
SuAnal19^	Text 60	Analyisis code 19 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp19
SuAnal20^	Text 60	Analyisis code 20 - `Setup` View >> Analaysis code menu option will list mapping numbers and values Sage V2015 onwards In Cell Searching values in table AnalSupp20
Payment:
SuAccType^	Integer	Account Type In Cell Searching values:Account Type 0, Balance Forward 1, Open Item 2, Auto Allocation
SuSetDiscPerc	Double	Early Settlement Discount Percent
SuSetDiscDays	Integer	Early Settlement Discount Days
SuTermsDays	Integer	Payment Terms Days
SuTermsBasis^	Integer	Payment Terms Basis In Cell Searching values:Payment Terms Basis 0, Due From Calendar Month 1, Due From Start Of Month 2, Payment Due From End Of Month 3, Payment Due From Document Date
SuPayGroup^	Text 40	Payment Group In Cell Searching values in table PayGroup
Factor Houses:
SuFactHouse^	Text 40	Factor House In Cell Searching values in table FactorHouse
Credit:
SuAccOpened	DateBlank	Account Opened Sage V2015 onwards
SuLastCredRev	DateBlank	Last Credit Review
SuNextCredRev	DateBlank	Next Credit Review
SuAppDate	DateBlank	Application Date
SuDateReceived	DateBlank	Date Received
SuTradingTerms	Text 30	Trading Terms
SuCreditRef	Text 60	Credit Reference
SuCreditBureau^	Text 40	Credit Bureau Name In Cell Searching values in table Bureau
SuCreditPosition^	Text 40	Credit Position Name In Cell Searching values in table CreditPositions
SuTermsAgreed	Yes/NoTF	Terms Agreed
SuCreditMemo	Memo	Credit Memo
Other:
Spare:
SuSpareBit1	Yes/NoTF	SpareBit1
SuSpareBit2	Yes/NoTF	SpareBit2
SuSpareBit3	Yes/NoTF	SpareBit3
SuSpareDate1	DateBlank	SpareDate1
SuSpareDate2	DateBlank	SpareDate2
SuSpareDate3	DateBlank	SpareDate3
SuSpareNumber1	Double	SpareNumber1
SuSpareNumber2	Double	SpareNumber2
SuSpareNumber3	Double	SpareNumber3
SuSpareText1	Text 100	SpareText1
SuSpareText2	Text 100	SpareText2
SuSpareText3	Text 100	SpareText3
ObjectStore Mappings:
SuUser1	Text 30	10 user defined fields which can be mapped to Sage ObjectStore Builder customisations How to map Sage ObjectStore Builder customisations
SuUser2	Text 30	User2
SuUser3	Text 30	User3
SuUser4	Text 30	User4
SuUser5	Text 30	User5
SuUser6	Text 30	User6
SuUser7	Text 30	User7
SuUser8	Text 30	User8
SuUser9	Text 30	User9
SuUser10	Text 30	User10

Req = Field is required EOF = End of file, Stop when no more values

With data type Yes/No fields, if cell starts with 'y' or 'Y' is taken as TRUE

Where ^ denotes a In Cell Code Search, delete ^ at end of tag, to turn off code search

All Text fields are trimmed (spaces before) by default. Add ' to the end of the tag name to stop this.