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

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

Customer Import in Sage 50
Customer Import via XML API for Sage 200

In addition, related customer data can be edited/imported on each line, such as:

- Contacts, including Phone, e-mails address and web addresses
- Contact Roles
- Delivery Addresses

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 Customer fields on the Sage 200 Customer 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.

Contacts

Default Contacts can be extracted as part of the Customer Import/Export by adding an of the contact tags.

Tags of interest:

Tag	Description
xxPhCo1-5, xxPhAr1-5, xxPhone1-5	Phone number fields for Country, Area and Number. Column 1 is the default Phone No.
xxFaxCo1-5, xxFaxAr1-5, xxFaxNo1-5	Fax number fields for Country, Area and Number. Column 1 is the default Fax No.
xxMobCo1-5, xxMobAr1-5, xxMobNo1-5	Mobile number fields for Country, Area and Number. Column 1 is the default Mobile No.
xxEmail1-5	Email address 1-5. Column 1 is the default e-mail.
xxWeb1-5	Web address 1-5. Column 1 is the default web.

where xx is Cu (for customer), Cc (Customer Contacts), Su (for supplier), Sc (Supplier Contacts)

The Roles can also be edited by simply adding Role tags Role1-20. When first extracted PostTrans will assign the "Role" to the column automatically.

Delete Contact

You cannot delete a contact if:
It is the Default Contact, so you must assign a different contact first
If the contact is assigned as Role such as "Account", "SendStatementTo" or "SendRemittanceTo". Thus assign these roles to another contact first

Contact Roles

Tags CuRole1-20 can be specified, to extract/amend or import customer contact role information. When extracted tag CuRole1-20 are assigned Role headings. To delete a UOM from the list, simply delete the code in SgUnits1-5 cell, and press [Import].

Tags of interest:

Tag

Description

CuRole1-20

If starts with "Y" then assigned Role.

If starts with "P" then assigned Preferred Role.

Analysis Codes

CuAnal1-20 gives access to the 20 user fields. After extraction these will be named in the "related" row 3.

Filter

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

In Cell Search

The user can search for Nominal, Price List, Department codes etc. 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 Customer 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.

Customer Import/Update Template

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

The cell A1 has a tag “Sage200cust” 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 Online Training for an explanation of this.

Customer Required Fields

To Import/Extract/Amend Sage 200 Customer Data, the following Tags/Fields must be specified:

Sage200cust, CuCRC, CuRef and CuName

Cell A1 must equal Sage200cust

CuCode 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 Customer Delivery Addresses

The following PostTrans tags can be used to extract/import Customer 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:
Sage200custDel	Text Req	Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row
CdCRC	Text Req	Contains CRC number to help PostTrans detect changes in current row.
CdID	Integer Req Read	Delivery Address ID used to update record.
CdDelete	Text 1	Delete record if "X"
CdSearch^	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 Cust
Account:
CdAccRef^	Text 8 EOF Req	Account Ref In Cell Searching values in table Cust
CdDesc	Text 255 Req	Desc
CdPostalName	Text 60 Req	PostalName
Address:
CdDefault	Yes/NoTF	Default address
CdAdd1	Text 60	Address1
CdAdd2	Text 60	Address2
CdAdd3	Text 60	Address3
CdAdd4	Text 60	Address4
CdPostcode	Text 10	Postcode
Segmented Address:
CdCity	Text 60	City, Only used if Segmented address is ON
CdCounty	Text 60	County, Only used if Segmented address is ON
CdCountry^	Text 60	Country, Only used if Segmented address is ON In Cell Searching values in table CountryCode
Main Contact:
CdContact	Text 235 Req	Contact
CdPhone	Text 30	Phone
CdFax	Text 30	Fax
Cdemail	Text 255	email
VAT Defaults:
CdCountryCode^	Text 2	Country Code GB,US.. In Cell Searching values in table CountryCode
CdDefTaxCode^	Text 2	Default Tax Code, 0==Exempt,1==Std,2==Zero (1 if not specified) In Cell Searching values in table Tax
CdTaxRegCode	Text 30	Tax Registration Code

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.