Sage 50:Extract/Amend/Import Suppliers

PostTrans imports Suppliers, which could be manually entered with the assistance of in-cell searching, using copy and paste, imported from CSV file or extracted from Sage 50 using the Extract button.

Import Supplier Features in Sage 50

Simply add new lines to the spreadsheet from row 30 on-wards. Then press Import button, on the PostTrans Toolbar, to import single or multiple Suppliers (Examples shown below).

Advantages

Highlighted Validation of codes and data
Search for Account, Stock, Project, Nominal, using our In-Cell Searching
Add formula, vba or macros to manipulate the imported data
Press Extract to populate the sheet with existing data, amend and Import
Easy importation from CSV file

Importing Sage 50 Suppliers

Supplier template for importing into Sage 50

Demonstration Video

Sage 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 50, 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.

Template Structure for Static Data

The template must have tag ‘Sage50supp’ in cell A1 to instruct PostTrans that we are interested in Sage 50 Suppliers, and that the sheet is a PostTrans Template. Pressing Import or Extract on a blank sheet will either open an existing example or create basic template for you to customise.

Row 1 contains a series of tags, which denote to PostTrans the data to be read/written in each column. These tags, and data columns can be rearranged to suit your needs, and the tags are added using the Tags button. See on-line Training Deleting the Tag name, in the cell, from this row removes the Sage 50 mapping. Table of Suppliers Tags

Hovering over these tags, in row 1, will show a comment which explains the tags purpose, data type and any other useful details.

Common Features

In-Cell Searching

Any Tag ending with ^ with have a in-cell search facility to look up associated values. Use SPACE + TAB to see all values, or SPACE and TEXT to perform a text search on description. These are returned to the cell as "CODE, DESCRIPTION". PostTrans only uses the CODE and ignores the comma and anything after it, thus data from a field would only need the CODE part of the data if from an external imported file.

In-cell Searching can be turned off by removing the ^ from the end of the tag name, or name in first line of comment (Header cells) to disable in-cell searching for a single column, or header cell. All in-cell searching can be turned off in Setup. In-cell searching.

Extracting the Data

The data extracted using the Extract button can be filtered, by entering criteria in row 2. Hover over cell A2 for instructions. Extracting Data

Amending Suppliers Data

Having extracted data, the user can now simply edit the Suppliers in Microsoft Excel, before re-importing it back into Sage 50.

PostTrans uses the tagged column SuCRC to detect changes in rows. Therefore, only records/rows which have changed since extraction are updated. If you add or delete a tagged column, PostTrans will treat ALL rows as having changed, and thus ALL records will be updated on next Import.

Importing the Data

Simply press the Import button to validate and import the data into Sage 50. PostTrans will read the Header cells and then process the rows of data until SuCode column contains no data. The Sage Company imported into can be set in the Setup window.

PostTrans ONLY edits those fields listed on the sheet when importing, thus any existing values, on a record, are unaffected. New records may have default values for certain fields.

File Import Button

This can be enabled in Setup to easily import cvs, or fixed length files, into sheet for importation. Formula can also be added, or macros/VBA to manipulate this data before importation. Import from CSV file.

Validation

Any values and associated codes will be invalid during import. The cursor will be moved to highlight any problems in data.

Evaluation and License

This functionality will work with Sage Demo data without a license. A license is required to import into any other Sage Company. Pricing

What else can I do?

Other Sage 50 Extract/Import types

Frequently Asked Questions?

Got another question? It may well be covered on our Frequently Asked Questions page.

Tags which map to Sage 50

Not all these tags/columns are needed. The table below lists the required and recommended:

Tag Name	Data Type	Description
Important Fields:
Sage50supp	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	This cell is very important, as it allows PostTrans to let you know of any errors or warnings while importing. If data is extracted out of the system, then it will contain a number. This number is calculated so PostTrans can work out if you have changed any of the data in the row, and thus only update changes on next import.
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 SupplierCode
SuCode	Text 8 EOF Req XML	Account Reference
Account Details:
SuName	Text 60 XML	Account Name
SuBalance	Currency Read	Balance
SuInactive	Yes/No- XML	Inactive Flag - yes/No Sage V21 2015 onwards
Registered Address:
SuAdd1	Text 60 XML	Account Address Line 1
SuAdd2	Text 60 XML	Account Address Line 2
SuAdd3	Text 60 XML	Account Address Line 3
SuAdd4	Text 60 XML	Account Address Line 4
SuPostcode^	Text 60 XML	Account Postcode. If Postcode Search is enabled in ‘Setup >> Lookup >> Address Lookup’ then enter Space,Space + Postcode to search for an address. Postcode Address Lookup In Cell Searching values in table SimplyPostcode
SuCountryCode^	Text 2 XML	Country Code In Cell Searching values in table CountryCode
SuVATRegNo	Text 20 XML	VAT Registration Number
Contact Information:
SuContactName	Text 30 XML	Contact Name
SuTradeContact	Text 30 XML	Name of Trade Contact
SuPhone	Text 30 XML	Telephone Number
SuPhone2	Text 30 XML	Second Telephone Number
SuFax	Text 30 XML	Fax Number
SuWWW	Text 255 XML	WWW Address
e-mail Settings:
Sue-mail1	Text 255 XML	EMail Address
Sue-mail2	Text 255 XML	EMail Address 2 Sage V19 2013 onwards
Sue-mail3	Text 255 XML	EMail Address 3 Sage V19 2013 onwards
Sue-mail4	Text 255 XML	e-mail4 Sage V23 2017 onwards
Sue-mail5	Text 255 XML	e-mail5 Sage V23 2017 onwards
Sue-mail6	Text 255 XML	e-mail6 Sage V23 2017 onwards
SuSendInvEmail	Yes/No- XML	Send Invoices To This Account via Email
SuSendLettersEmail	Yes/No- XML	Send Letters To This Account via Email
Defaults - Custom Fields:
SuAnal1	Text 30 XML	Analysis 1
SuAnal2	Text 30 XML	Analysis 2
SuAnal3	Text 30 XML	Analysis 3
SuAnal4	Text 30 XML	Anal4 Sage V23 2017 onwards
SuAnal5	Text 30 XML	Anal5 Sage V23 2017 onwards
SuAnal6	Text 30 XML	Anal6 Sage V23 2017 onwards
Defaults - Pricing and Discounting:
SuDiscRate	Double XML	Discount Rate %
SuDiscType^	Integer XML	Discount Type In Cell Searching values:Discount Type 0, None 1, Invoice Value 2, Band A 3, Band B 4, Band C 5, Band D 6, Band E
SuPriceList^	Text 8 XML	Price List Reference In Cell Searching values in table PriceList
Defaults - Misc Defaults:
SuNomCode^	Text 8 XML	Default Nominal Code In Cell Searching values in table NomCode
SuOverideProductNom	Yes/No- XML	Override Product Nominal Code in Invoicing
SuTaxCode^	Integer XML	Default Tax Code In Cell Searching values in table TaxCode
SuOverideProductTax	Yes/No- XML	Override the products own Tax Code.
SuCurrency^	Integer XML	Currency In Cell Searching values in table CurrencyCode
SuDepartment^	Text 2 XML	Department Number In Cell Searching values in table DeptCode
Credit Control - Terms:
SuCreditLimit	Currency XML	Credit Limit
SuSettDays	Integer XML	Settlement Due Days
SuSettDiscount	Double XML	Settlement Discount Rate
SuPaymentDueDays	Integer XML	Payment Due Days
SuTermsTxt	Text 30 XML	Terms
SuCreditRef	Text 60 XML	Credit Bureau Reference
SuBureau^	Integer XML	Credit Bureau Used In Cell Searching values in table BureauCode
SuPrioritySupp	Yes/No- XML	Priority Supplier Sage V18 2012 onwards
SuCreditPosition^	Integer XML	Credit Position In Cell Searching values in table CreditPosition
SuStatus^	Integer XML	Account Status In Cell Searching values in table StatusCode
SuDonFund^	Text 9 XML	Donation Fund Number In Cell Searching values in table CharityCode
Credit Control - Restrictions:
SuApplyCharges	Yes/No- XML	Can Charge Credit
SuTermsAgreed	Yes/No- XML	Terms Agreed Flag
SuHoldMail	Yes/No- XML	Restrict Mail
SuOnHold	Yes/No- XML	Account On Hold
Credit Control - Credit Review:
cuDateOpened	Date XML	Date Account Opened
SuLastCreditReview	Date XML	Date Last Credit Review
SuNextCreditReview	Date XML	Date Next Credit Review
SuApplicationDate	Yes/No- XML	Date Credit Applied For
SuCreditAppRecived	Date XML	Date Credit Application Recieved
SuMemo	Memo XML	Customer Notes
Sales:
SuFirstINV	Date Read	First Invoice Date
SuLastInvDate	Date Read	Last Invoice Date
SuLastReceipt	Date Read	Last Payment Date
SuYTD	Currency Read	Turnover Year Date
SuLYTD	Currency Read	Prior YTD
Bank:
SuBankName	Text 60 XML	Bank Name
SuBankAdd1	Text 60 XML	Bank Address Line 1
SuBankAdd2	Text 60 XML	Bank Address Line 2
SuBankAdd3	Text 60 XML	Bank Address Line 3
SuBankAdd4	Text 60 XML	Bank Address Line 4
SuBankPostcode^	Text 60 XML	Bank Postcode. If Postcode Search is enabled in ‘Setup >> Lookup >> Address Lookup’ then enter Space,Space + Postcode to search for an address. Postcode Address Lookup In Cell Searching values in table SimplyPostcode
SuBankAccName	Text 60 XML	Bank Account Name
SuBankSort	Text 10 XML	Bank Sort Code
SuBankNumber	Text 60 XML	Bank Account Number
Payment Details.:
SuBacRef	Text 60 XML	Reference for BACS Transfer
SuBankIBAN	Text 60 XML	Bank IBAN Number
SuBankSwift	Text 60 XML	Bank BIC/Swift
SuBankRollNo	Text 60 XML	Bank Roll Number
SuBankAddRef1	Text 60 XML	Bank Additional Ref 1
SuBankAddRef2	Text 60 XML	Bank Additional Ref 2
SuBankAddRef3	Text 60 XML	Bank Additional Ref 3
SuPaymentMethod^	Text 2 XML	Payment Type. If BACS then sets CuOnlineReceipts=YES, otherwise need to set manually Sage V21 2015 onwards In Cell Searching values:Payment Method 0, None 1, BACS/SEPA 2, Cash 3, Cheque 4, Credit Card 5, DD/SO 6, Other 7, User1 8, User2 9, User3 10, User4 11, User5
SuOnlineReceipts	Yes/No- XML	BACS Online Receipts Flag. If CuPaymentMethod=BACS set otherwise needs setting manually
Payment Details:
SuManager	Text 60 XML	Name of Account Manager
SuCompReg	Text 16	CompReg Sage V23 2017 onwards
SuEORIno	Text 16	EORI no Sage V23 2017 onwards
Building Society:
SuBankBuildSocRef	Text 60 XML	Reference for Building Society Transfer
SuBankUseBuildingSoc	Yes/No- XML	Account uses Building Society
Credit Card:
SuCreditAppRecieved	Yes/No- XML	True if Credit Application Received
SuCCNumber	Text 30 XML	Credit Card Number
Donor:
SuDonorDecFrom	Date XML	Donor Declaration Valid From
SuDonorForname	Text 35 XML	Donor Forename Sage V20 2014 onwards
SuDonorSurname	Text 35 XML	Donor Surname Sage V20 2014 onwards
SuDonorTitle	Text 4 XML	Donor Title Sage V20 2014 onwards
SuGiftAid	Yes/No- XML	Gift Aid is enabled
Membership:
SuMemberReminderDays	Integer XML	Member Reminder Date (days)
SuMemberReminderDate	Date XML	Member Renewal Date
SuMemberSince	Date XML	Member Since
Other:
SuSalesRep	Text 60 XML	Name of Sales Representative
SuEcCode	Text 3 Read XML	EC Member Code
SuDUNSno	Text 9	DUNS Number

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

XML = Un-attended PostTrans XML API for Sage 200

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.

Related Example Sheets

You can easily access the example sheets by pressing Help button, on the PostTrans Toolbar to access these related examples, or downloading them from the links below.