Menu

Customer

Releated Topics.

Filter Extracted Data
Training on Static Data
Sales Quote/Order/Invoice
Price Lists
In Cell Searching

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

Xero

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

Image Alt TextImage Alt Text

Multiple Transactions from CSV file

Xero

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

Image Alt Text

Xero

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

Sage 200 Bins

Sage 200 Warehouse & Bins

Create Sage 200 Bins
Stock Take Bins
Move Bin Stock

Fast, cost effective, easy…

Image Alt TextImage Alt Text

Manufacturing

200 Manufact.

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

Image Alt Text

Complex tasks quicker...

50 BOM

Project Accounting

200 Projects

Create Project Structure
Update Budgets
Simple Project Entry

Make life easier...

Image Alt Text50 Projects

Sage 50:Extract/Amend/Import Customers

PostTrans imports Customers, 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.

Page Contents

Automated Customer Import via XML API for Sage 50

Related Pages

Import Customer 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 Customers (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

Examples of importing Sage 50 Customers

Customer template for importing into Sage 50

Customer 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
 
 

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 ‘Sage50cust’ in cell A1 to instruct PostTrans that we are interested in Sage 50 Customers, 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 Customers 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 SetupIn-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 Customers Data

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

PostTrans uses the tagged column CuCRC 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 CuCode 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.

Other Useful Topics

Clear Transaction and copy down formula

PostTrans 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:

Sage50cust

Text Req

Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row

CuCRC

Text Req

Contains CRC number to help PostTrans detect changes in current row.

CuDelete

Text 1

Delete record if "X"

CuSearch^

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 CustCode

CuCode

Text 8 EOF Req XML

Account Reference

Account Details:

CuName

Text 60 XML

Account Name

CuBalance

Currency Read

Balance

CuInactive

Yes/No- XML

Inactive Flag - yes/No
    Sage V21 2015 onwards

Registered Address:

CuAdd1

Text 60 XML

Account Address Line 1

CuAdd2

Text 60 XML

Account Address Line 2

CuAdd3

Text 60 XML

Account Address Line 3

CuAdd4

Text 60 XML

Account Address Line 4

CuPostcode^

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

CuCountryCode^

Text 2 XML

Country Code.
    In Cell Searching values in table CountryCode

CuVATRegNo

Text 20 XML

VAT Registration Number

Contact Information:

CuContactName

Text 30 XML

Contact Name

CuTradeContact

Text 30 XML

Name of Trade Contact

CuPhone

Text 30 XML

Telephone Number

CuPhone2

Text 30 XML

Second Telephone Number

CuFax

Text 30 XML

Fax Number

CuWWW

Text 255 XML

WWW Address

e-mail Settings:

Cue-mail1

Text 255 XML

EMail Address

Cue-mail2

Text 255 XML

EMail Address 2
    Sage V19 2013 onwards

Cue-mail3

Text 255 XML

EMail Address 3
    Sage V19 2013 onwards

CuSendInvEmail

Yes/No- XML

Send Invoices To This Account via Email

CuSendLettersEmail

Yes/No- XML

Send Letters To This Account via Email

Defaults - Custom Fields:

CuAnal1

Text 30 XML

Analysis 1

CuAnal2

Text 30 XML

Analysis 2

CuAnal3

Text 30 XML

Analysis 3

Defaults - Pricing and Discounting:

CuDiscRate

Double XML

Discount Rate %

CuDiscType^

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

CuPriceList^

Text 8 XML

Price List Reference
    In Cell Searching values in table PriceListCode

Defaults - Misc Defaults:

CuNomCode^

Text 8 XML

Default Nominal Code
    In Cell Searching values in table NomCode

CuOverideProductNom

Yes/No- XML

Override Product Nominal Code in Invoicing

CuTaxCode^

Integer XML

Default Tax Code
    In Cell Searching values in table TaxCode

CuOverideProductTax

Yes/No- XML

Override the products own Tax Code.

CuCurrency^

Integer XML

Currency
    In Cell Searching values in table CurrencyCode

CuDepartment^

Text 2 XML

Department Number
    In Cell Searching values in table DeptCode

Credit Control - Terms:

CuCreditLimit

Currency XML

Credit Limit

CuSettDays

Integer XML

Settlement Due Days

CuSettDiscount

Double XML

Settlement Discount Rate

CuPaymentDueDays

Integer XML

Payment Due Days

CuAvgPayDays

Integer XML

Average Time To Pay

CuTermsTxt

Text 30 XML

Terms

CuCreditRef

Text 60 XML

Credit Bureau Reference

CuBureau^

Integer XML

Credit Bureau Used
    In Cell Searching values in table BureauCode

CuPriorityCust

Yes/No- XML

Priority Customer
    Sage V18 2012 onwards

CuCreditPosition^

Integer XML

Credit Position
    In Cell Searching values in table CreditPosition

CuStatus^

Integer XML

Account Status
    In Cell Searching values in table StatusCode

Credit Control - Restrictions:

CuApplyCharges

Yes/No- XML

Can Charge Credit

CuTermsAgreed

Yes/No- XML

Terms Agreed Flag

CuHoldMail

Yes/No- XML

Restrict Mail

CuOnHold

Yes/No- XML

Account On Hold

Credit Control - Credit Review:

cuDateOpened

Date XML

Date Account Opened

CuLastCreditReview

Date XML

Date Last Credit Review

CuNextCreditReview

Date XML

Date Next Credit Review

CuApplicationDate

Date XML

Date Credit Applied For

CuCreditAppRecived

Date XML

Date Credit Application Recieved

CuMemo

Memo XML

Customer Notes

Sales:

CuFirstINV

Date Read XML

First Invoice Date

CuLastInvDate

Date Read XML

Last Invoice Date

CuLastReceipt

Date Read XML

Last Payment Date

CuYTD

Currency Read XML

Turnover Year Date

CuLYTD

Currency Read XML

Prior YTD

Bank:

CuBankName

Text 60 XML

Bank Name

CuBankAdd1

Text 60 XML

Bank Address Line 1

CuBankAdd2

Text 60 XML

Bank Address Line 2

CuBankAdd3

Text 60 XML

Bank Address Line 3

CuBankAdd4

Text 60 XML

Bank Address Line 4

CuBankPostcode^

Text 60 XML

Bank Address 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

CuBankAccName

Text 60 XML

Bank Account Name

CuBankSort

Text 10 XML

Bank Sort Code

CuBankNumber

Text 60 XML

Bank Account Number

Payment Details.:

CuBacRef

Text 60 XML

Reference for BACS Transfer

CuBankIBAN

Text 60 XML

Bank IBAN Number

CuBankSwift

Text 60 XML

Bank BIC/Swift

CuBankRollNo

Text 60 XML

Bank Roll Number

CuBankAddRef1

Text 60 XML

Bank Additional Ref 1

CuBankAddRef2

Text 60 XML

Bank Additional Ref 2

CuBankAddRef3

Text 60 XML

Bank Additional Ref 3

CuPaymentMethod^

Text 2 XML

Payment Type. If BACS then sets CuOnlineReceipts=YES, other wise 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

CuOnlineReceipts

Yes/No- XML

BACS Online Receipts Flag. If CuPaymentMethod=BACS set other wise needs setting manuyally

Payment Details:

CuManager

Text 60 XML

Name of Account Manager

CuType^

Integer XML

Account Type
        In Cell Searching values:Account Type
                  0, Normal

Delivery Address:

CuDelName

Text 60 XML

Delivery Name

CuDelAdd1

Text 60 XML

Delivery Address Line 1

CuDelAdd2

Text 60 XML

Delivery Address Line 2

CuDelAdd3

Text 60 XML

Delivery Address Line 3

CuDelAdd4

Text 60 XML

Delivery Address Line 4

CuDelPostcode^

Text 60 XML

Delivery Address Postcode
Postcode Address Lookup
    In Cell Searching values in table SimplyPostcode

CuDelContactName

Text 30 XML

Delivery Contact Name

CuDelFax

Text 30 XML

Delivery Fax Number

CuDelPhone

Text 30 XML

Delivery Telephone Number

Building Society:

CuBankBuildSocRef

Text 60 XML

Reference for Building Society Transfer

CuBankUseBuildingSoc

Yes/No- XML

Account uses Building Society

Credit Card:

CuCreditAppRecieved

Yes/No- XML

True if Credit Application Received

CuCCNumber

Text 30 XML

Credit Card Number

Charity Donor:

CuDonTitle

Text 4 XML

Charity Donor Title
    Sage V20 2014 onwards

CuDonForname

Text 35 XML

Charity Donor Forename
    Sage V20 2014 onwards

CuDonSurname

Text 35 XML

Charity Donor Surname
    Sage V20 2014 onwards

CuDonFund^

Integer XML

Charity Donation Fund Number
    In Cell Searching values in table CharityCode

CuDonGiftAid

Yes/No- XML

Charity Donor Gift Aid is enabled

CuDonDecFrom

Date XML

Donor Declaration Valid From

CuDonTyDonor

Yes/No- XML

Charity Donor Type Donor Flag
    Sage V18 2012 onwards

CuDonTyMember

Yes/No- XML

Charity Donor Type Member Flag
    Sage V18 2012 onwards

CuDonTyCust

Yes/No- XML

Charity Donor Type Customer Flag
    Sage V18 2012 onwards

Charity Membership Details:

CuDonMemberSince

Date XML

Member Since

CuDonReminderDate

Date XML

Member Renewal Date

CuDonReminderDays

Integer XML

Member Reminder Date (days)

Other:

CuSalesRep

Text 60 XML

Name of Sales Representative

CuEcCode

Text 3 Read XML

EC Member Code


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.

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.

 

Test DB Sheet

Sheet:''
DO NOT DELETE - Used for testing, or shen setup is pressed without PostTrans Sheet
   Help article for Test DB Sheet

 

*** Test Sage Connection Template ***

Sheet:'Test Sheet'
DO NOT DELETE - Used for testing, or when setup is pressed without PostTrans Sheet
   Help article for *** Test Sage Connection Template ***

Static Data - Customer:

 

Customer Records

Sheet:'Customer Rec'
Allow Extract/Amend/Import of Customer records
   Main page for Customer records Import
   Help article for Customer records Import

 

Customer Price Lists

Sheet:'Customer Price List'
Extract, Add and Amend the Price List at top level
   Main page for Price List Update
   Help article for Price List Update

 

Customer Price List/Qty Assignment

Sheet:'Customer Price List Assignment'
Allow assignement of Customer Qty breaks and Price list
   Main page for Customer records Import
   Help article for Customer records Import

 

Product Customer Price Structure

Sheet:'Customer Prices Structure'
Extract, Add and Amend Customer Prices
   Main page for Customer Prices Update
   Help article for Customer Prices Update

 

Product Customer Special Prices

Sheet:'Special Prices'
Extract, Add and Amend Customer Special Prices
   Main page for Customer Prices Update