Menu

Transaction Data

Xero:Transaction Data Template Training

When we refer to "Transaction data" we are talking about the Mominal, Purchase/Sales Invoice/Credits/Quotes and Purchase orders etc.

Page Contents

PostTrans Toolbar

PostTrans Toolbar

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

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

Cache - View the Xero Cached data. Customise how data is cached

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.

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

Support - Find answers to common questions or contact us

Single Transaction Template Layout

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

Transaction Template Layout

Transaction Template Layout

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 Window

Tags Window

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.

Tags Window

Tags Window

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.

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 reads the template design

PostTrans reads the template design

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 reads the Lines

PostTrans reads the Lines

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

Transaction Summary

Transaction Summary

See Training Video : Essential 2: Structure for Single Transaction

Multiple Transaction Import

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:

Transaction Summary

Transaction Summary

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

Transaction Summary

Transaction Summary

See Training Video : Essential 3: Structure for Multiple transactions

Multiple Transaction Import with Contact Creatation

Controlled by a setting in Setup >> "Defaults" tab, contacts can be Created/Updated from transaction information. Ideal for import from an external system.

See Training Video : From CSV file with formula

Transaction Summary

Transaction Summary

Training Videos

The following set of videos give you the essential training needed to fully understand PostTrans for Xero when Importing Transactional data templates for Single/Multiple Transactions. The “Essential Training” videos are then supplemented with additional material which may be of use in specific areas.

Training Transaction: Essential 1: Intro

 
 
   PostTrans Training Transaction: Essential 1: Intro
  0:00 Same for all Accounts Packages
  0:10 Toolbar Buttons
  1:00 How to contact us
  1:20 How to open examples
  2:50 How to select different Company
  3:00 Where are settings stored
 
 

Training Transaction: Essential 2: Structure for Single Transaction

 
 
   PostTrans Training Transaction: Essential 2: Structure for Single Transaction
  0:00 Open example sheet
  0:30 Header fields
  2:50 Transaction Defaults expressed in sheet
  2:00 Defaults in settings Tax, Contact Searching
  4:00 Add a mapping in Header section
  6:50 How PostTrans knows where everything is?
  6:40 How to delete a Header Mapping
  7:00 Turning ON/OFF in cell searching
  7:10 Turning ON/OFF Header Clear
  7:45 Mapping a column using the Tags button
  9:35 Double click on Ref to See Transaction in Xero
 
 

Training Transaction: Essential 3: Structure for Multiple transactions

 
 
   PostTrans Training Transaction: Essential 3: Structure for Multiple transactions
  0:00 Open example sheet
  0:30 Defaults in Header Section
  1:00 Defaults in Settings
  2:00 Columns Mappings
  3:20 How THContact selects the Xero Contact
  4:20 Contact updates
  4:30 Payment Ref
  5:10 Change in header causes new transaction
  6:00 Importing Transactions
  7:00 Double click to see results
 
 

Training Transaction: Essential 4: How to get help?

 
 
   PostTrans Training Static: Essential 3: How to get help?
  0:00 Comments in cells
  1:00 Tag (Mapping) documentation
  1:30 Online Support
 
 

Training Transaction: Essential 5: In cell searching

 
 
   PostTrans Training Transaction: Essential 5: In cell searching
  0:20 How to turn ON/OFF in Cell or Column
  1:50 How to turn ON/OFF for whole sheet
  1:00 Demo of Searching for Contact by Name or Account Code
  2:00 Searching for a Xero Product by Code or Any part of Description
  4:00 Search for Tax Codes
  4:30 Nominal Account Searching
  5:20 Cache Button to get all codes to clipboard
 
 

Training Transaction: From CSV file with formula

 
 
   PostTrans Training Transaction: From CSV file with formula
  0:00 Open example sheet
  0:30 Defaults needed in the Header
  1:20 Enabling File importation
  2:00 First time import of CSV file
  3:00 Mapping columns to Xero data
  5:50 How to get help on Tags and what they do
  5:10 Adding a VLOOKUP to manipulate data
  7:40 Make sue fields are formatted to General
  10:30 How to make PostTrans copy formula down
  11:40 Clear the sheet
  13:00 Cleared default Woops!
  14:00 Double click to view in Xero
 
 

Training Transaction: Tax

 
 
   PostTrans Training Transaction: Tax
  0:00 Open example sheet
  0:30 Exclusive of Tax demo
  2:20 Making Transaction Inclusive of Tax
  4:40 Adding a matching Payment
  5:30 Double click for the proof in Xero
 
 

Training Transaction: Clear Sheet

 
 
   PostTrans Training Transaction: Clear Sheet
  0:13 How to mark a column for deletion
  1:00 How to mark a Header Cell for deletion
 
 

Training Transaction: Contact update Multiple Transactions

 
 
   PostTrans Training Transaction: Contact update Multiple
  0:00 Open example sheet
  1:00 Read only/Create/Update settings
  1:40 How THContact relates to Xero data
  3:00 Columns update Contact
 
 

Training Transaction: Contact update Single Transaction

 
 
   PostTrans Training Transaction: Contact update Single
  1:00 Changing the Contact Search Mode
  1:20 Update/Create Contacts
  2:51 Creating a new Customer with Transaction
  3:00 Double click to see invoice
 
 

Training Transaction: Conformation window

 
 
   PostTrans Training Transaction: Conformation window
  0:20 Don’t ask again
  0:30 Options in settings
 
 

Training Transaction: System Setup how is it stored?

 
 
   PostTrans Training Transaction: System Setup how is it stored?
  0:40 Settings stored in comment
  1:10 How to overide company on each workbook
 
 

Training Transaction: Protect sheet

 
 
   PostTrans Training Transaction: Protect sheet
  0:20 SAVE a backup
  0:30 Save as a Template
  0:40 Make Read only
 
 

Training Transaction: Data Grouping and how to hide cells

 
 
   PostTrans Training Transaction: Data Grouping and how to hide cells
 
 

Training Transaction: Cursor Path

 
 
   PostTrans Training Transaction: Cursor Path