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
• Single Transaction Import
• Multiple Transaction Import
• Multiple Transaction Import with Contact Creatation
• Training Course Videos

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 Xero 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 Xero 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. See Xero Cached data for more information

Tags - Gives access to the Tags to define the data mapping between Xero 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, Xero Company selection and system switches. See Xero Company selection for more information

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

In the above example "Xero50trans" defines the template as being for Xero 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 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 Xero for the current Excel Cell. Depending on the Tag used, optionas can be turned ON/OFF.

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 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 will then scan down the template, reading the lines of data as it goes. Thus when posted:

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

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

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

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

Training Transaction: Essential 2: Structure for Single Transaction

Training Transaction: Essential 3: Structure for Multiple transactions

Training Transaction: Essential 4: How to get help?

Training Transaction: Essential 5: In cell searching

Training Transaction: From CSV file with formula

Training Transaction: Tax

Training Transaction: Clear Sheet

Training Transaction: Contact update Multiple Transactions

Training Transaction: Contact update Single Transaction

Training Transaction: Conformation window

Training Transaction: System Setup how is it stored?

Training Transaction: Protect sheet

Training Transaction: Data Grouping and how to hide cells

Training Transaction: Cursor Path