Menu

Online Training

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

PostTrans Online Training

Insert Tags

The following video shows how design PostTrans Templates to Extract, Amend and Import Static data, as discussed in this section.

Importing Static Data, customers, stock/products etc.

Importing Transaction Data

PostTrans for Sage toolbar

Button Functions:

Import - For static data, Customer, Products, etc Import data in row 4 onwards.  Transaction template use row 30 onwards.   See below

Extract -  Extract data from sage for particular type of import static data.  See How to Filter Extracted Data

On a transaction this allows you to populate the sheet with Products, so when using in conjunction with Ignore ZERO qty, allows you to create an Sage Order Pad for quick order taking. Pro

Clear - Clears the header cells and data in rows.   On transaction templates it also copies down any other values and formula expressed in row 27.  See Clear transaction and copy down formula

Tags - Gives access to the Tags to define the data mapping between Sage and Excel in columns, or Header fields in a Transaction.  From this window you can add new Tags, or edit the properties of current cell mapping.

Setup - Access to Registration, Sage Company selection and system switches.

Help - Access links to these help pages and prepared examples.  Sage 50 List, Sage 200 List

File - Optional - Allows user to import a text file into template. This is hidden unless enabled. Importing a text file

[Find] - Optional - Allows the search of Quotes/Oders in the PostTrans register, for easy retriveal. PRO

Static Data template layout

When we refer to "Static data" we are talking about the Customer, Supplier, Product, Departments, etc. Records which don't change much when compared to transaction tables.

The diagram below shows the layout of a Static Data PostTrans template

The Tags, or data columns, can be in order as long as the tag in cell A1 remains in position.  PostTrans uses this tag to know what data the template relates to.  In the above example "Sage50cust" defines the template as being for Sage Line 50 Customer Extract/Amend/Import template. "Sage50Prod" would define as Product Extract/Amend/Import template.

The tag "CuErrorDesc" is required, to enable the PostTrans to report back any errors or warnings when importing data.  PostTrans also uses this column to write a CRC number for the row.   The CRC number is calculated on the contents of the row, to enable PostTrans to detect changes in the data, and thus only import data which has changed.  Deleting this number forces the row to be updated in Sage.

End of File

One Tag in each type of import will be designated as EOF (End of File).  PostTrans uses this to detect the end of the import. In the above example, for customers, it will be "CuCode" as all customer records have to have a code and the last record will be the empty one.

Filter

When extracting data, filter criteria can be added in row 2. How to filter Sage Line 50 extracted data.

Some tags end with ^.  This denotes the have a code search function allowing the user to quickly find a code.  Such as Account, Nominal code, etc.

PostTrans static templates are for Sage Line 50 Extract/Amend/Import of Customer, Supplier, Product, Bill of Materials, Prices List, Suppplier Special Prices, Customer DiscountsProjects records.

PostTrans Training Video - Static Data Template design

 
 
   PostTrans Training Video - Static Data Template design
  0:00 Video Introduction
  1:00 Creating a new Template
  2:00 Structure of a Static Template
  4:00 Extracting data from sage line 50
  5:15 Altering the data columns using Tags
  8:00 In cell searching
  9:30 Importing data
  10:30 Errors and warnings
  12:00 Copy data
  12:30 Adding formula to copy down Extracted Data
  13:40 Clear Button
  14:06 Help button to get Example templates
  15:00 Filter Extracted Data
 

Added Filtering for large set of data

 
 
   Sage 50 - Extract and Import Prices using PostTrans filters
  0:00 Introduction
  0:22 The Search Column
  0:48 Extract a Single Trader Price list
  1:16 Extract a Trader Price list from list of codes
  2:00 Extract Filter by Stock Code List
  2:20 Extract Filter by Stock Code List and Trader List
  2:48 Other Areas : Special Prices
 
 

 
 
   Sage 50 - Extract, Auto Filter and then Import Prices using PostTrans
  0:00 Introduction
  0:19 Extract All Price Data
  1:53 Apply Auto Filter
  2:00 Edit Prices and Import
 
 
 

Transaction template layout

The diagram below shows the basic layout of a Transaction based PostTrans template:

In the above example "Sage50trans" defines the template as being for Sage Line 50 and a Transaction template.  The cell A1 can be hidden, and protected, by simply hiding the row.

Please note: The demo/training videos say a Tag must exist in Cell A1, for transaction type imports.  Later in development PostTrans was altered to look at the tag value in the comment of cell A1.  Thus the tag text in cell A1 can be deleted.

The Tags button, on the PostTrans toolbar, gives access to the Tags which define the data within a PostTrans template.  Tags beginning with TH can be placed in the Header of the transaction, row 1-26, or in row 28 to denote the data will be entered in the lines from row 30 onwards.  Some tags have to be placed in the header only, but not many.

 


Tags beginning with TL must be placed in row 28 to define the data in the column below row 30 as being transaction line data.

Some of the TH Tags, if defined in row 28 will force a new transaction.  See below example in next section...

Current Tag Properties

Clicking on the "Current Tag Properties" displays information about the mapping to sage for the current Excel Cell.   Depending on the Tag used, optionas can be turned ON/OFF.

 

 

The values in row 27, enable the Clear button to delete and copy down formula.  The "On [Clear] button..." section easily allow you to set these values.

Some tags end with ^.  This denotes the have a code search function allowing the user to quickly find a code.  Such as Account, Nominal code, etc.

Transactional templates are for Sales/Purchase Orders/InvoicesAdjustmentNominal Journals 

How PostTrans reads the template design

When the Import button is pressed, PostTrans scans the first 26 rows looking for cell comments.  If the first line of a cell comment matches the name of a PostTrans tag, then it knows that cell should contain Sage data to import.  It then remembers any of the values in this header section.   Thus deleting a cell comment removes the PostTrans field definition.

PostTrans then scans row 28, the Tag row, which defines the data to import in the lines section of the template.  Row 30 onwards.

PostTrans will then scan down the template, reading the lines of data as it goes.  Thus when posted:

Multiple Transactions

If a TH* tag is defined in row 28, then those values in the lines will override the values specified in the header section, thus multiple transactions can be specified in the template.  PostTrans will start a new transaction if the data under a certain tags change.

 Example: 

The following examples has three line on each transaction, and four transactions are listed in row 30 onwards:

Within a Transaction template, if tag THTransDateTHTransTypeTHCustRef or THAccCode are specified, then a change in value will force PostTrans to start a new transaction.  

PostTrans Training Video - Transaction Template design

 
 
   PostTrans Training Video - Transaction Template design
  0:00 Video Introduction
  0:45 The Template structure - Sales Order
  3:30 The Header section of a transaction
  10:00 Reprinting a Transaction, to e-mail or printer
  10:30 Clearing a Transaction
  11:00 Postcode Address lookup in Delivery Address
  11:40 Customisation Adding extra fields, Currency, Price list, Message at top of Transaction
  12:00 How to get Help on Tags
  14:30 Blogs relating to tags
  16:48 Clear button
  17:30 Help button to access blog examples
  18:57 Posting Multiple Transactions
  24:30 System settings
 
 

 
 
   Training Addendum – Easy Tag edit