Menu

Customer Record

Save time updating

Sage 50

Stock/Product Creation
Customer/Supplier Prices
Bill of Material
Project Creation
Location Bins

Sage 200

Simply extract, edit and import

Import from Excel

50 Trans

Nominal Journals
Quotes/Invoices/Orders
Remote Orders
Product/Stock Adjustments
Sales Receipts
Quick OrderPad

200 Trans

Transaction from Manual Input

Multiple Transactions from CSV file

Solve the Puzzle?

Sage 50

Help on every type of import
Demo Video of all functionality
Loads of Examples included
Online Training
We can help too…

Sage 200

Great Customer Satisfaction

Customer Comments

Full Trial with Demo Data
Low cost Subscription
Cancel anytime
From a £1 a day
Saving you hours

Simply extract, edit and import

Power of Excel

Sage 50

Quick Template Creation
Customise with:
    Formula
    Macros
    VBA

Sage 200

To reach your goals quickly

Sage 200 Bins

Sage 200 Warehouse & Bins

Create Sage 200 Bins
Stock Take Bins
Move Bin Stock

Fast, cost effective, easy…

Manufacturing

200 Manufact.

Create BOM Stock Easily
Create Bill of Materials
Labour, Machine, Tools

Complex tasks quicker...

50 BOM

Project Accounting

200 Projects

Create Project Structure
Update Budgets
Simple Project Entry

Make life easier...

50 Projects

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
 
 

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.