Menu

Transactions

Xero:Import Transactions

This type of PostTrans import, imports Sales/Purchase Transactions, which can be input manually, data from an ODBC connection, copy and paste or imported from CSV file.

Page Contents

Import Transactions into Xero Features

  • Import Single Transaction, after data entry, or Multiple Transactions and types from file
  • Purchase/Sales Invoice/Credits/Quotes and Purchase orders
  • Payment with Sales Invoice/Credits
  • In-Cell Searching for Tax codes, Contacts, Product, Tracking code, etc.
  • OrderPad - Quick Order entry, just list Items/Service and only import those with Qty
  • Copy and Paste data from sources
  • Complex Customised manual data entry templates
  • Easy importation from CSV file
  • e-mail Sales Invoices automatically after import
  • Add notes during import
  • Inclusive/Exclusive Tax
  • Post into Multiple Xero Companies Pro
  • Visual Validation of data as processed highlighting an error
  • Use Excel formula, in adjacent columns, Macros or VBA to transform data/functionality
  • Double click on transaction Ref to view related Xero Transactions
  • Optional Update/Create Contacts during import
  • Multi Currency Pro
  • Optional Detailed Transaction Summary before posting
  • Chain posting, allows a second template to post, for Back to Back order Pro
  • Cannot Post Transaction twice. Writes POSTED in row, with transaction Ref generated.
  • Easily Add your own validation, using the THBefDisplayMessage tag

Template Structure for Xero Transaction Import

The template must have tag ‘xeroTrans’ in the comment of cell A1 to instruct PostTrans that we are interested in importing Transactions, and that the sheet is a PostTrans Template. 

See On-line Training course

Single Transaction Data entry

Single Transaction Data entry

Above is a screen shot of the Xero Transaction example template. Press Help, on the PostTrans toolbar to access example template, and select "Single Transaction" to open it.

The same template, just by changing the Transaction Type code, can import:

  • SQ - Sales Quote
  • SI - Sales Invoice + with payment + e-mail to customer
  • SC - Sales Credit + with payment + e-mail to customer
  • PO - Purchase Order
  • PI - Purchase Invoice
  • PC - Purchase Credit

Some of the columns are optional and can be removed, rearranged or extras added by using the Tags button to map Xero fields to the Excel Template.

These tags, listed in the Xero Transation Tags Table bottom of page, can be easily added to a spreadsheet using the Tags button.

See On-line Training course

Xero Copy and Paste data in Single Transactions Import Video

 
 
   Copy and Paste Xero Transactions and then Import into Xero
  0:00 Open Example included with PostTrans for Xero
  0:32 Price Bands stored in Sheet
  1:30 Import Transactions into Xero
 
 

Example OrderPad in Excel

OrderPad Quick Order Entry

OrderPad Quick Order Entry

A setting in Setup makes PostTrans ignore lines with Zero Qty. Hence a long list of Products/Services can be listed and simple quantities entered against them to place order. PostTrans will look up the price from the Xero Product Table or you simple list the price Inclusive or Exclusive of Tax according the settings in Setup

Any Column ending in a ^ denotes In-Cell Searching which means the user can easily search for associated codes. As seen in this example PostTrans writes back CODE, DESCRIPTION depending on the context of the data lookup. Everything after the comma is ignored. Therefore, if imported from a CSV file, or pasted in to sheet, column B would contain simple set of codes. See section below which explains more about In-Cell Searching

Xero Order Pad – with Price Bands Video

 
 
   Xero Order Pad – with Price Bands
  0:00 Open Example included with PostTrans for Xero
  0:33 Price Bands
  1:00 Enter New customer or Search for customer
  1:30 Import transaction into Xero
 
 

Multiple Transactions from file into Xero

Multiple Transactions from file

Multiple Transactions from file

The above example shows how data could be imported from CSV file or simply copied and pasted into template. Formula/Macros can also be used to manipulate the data for endless possibilities.

Multiple Transactions with Contact Create/Update

Multiple Transactions with Contact Create/Update

Here we have added mapping beginning with "Co" which map to the Xero Contact data. Depending on the settings in Setup these can update/create Contacts in Xero, identified by Xero ID, Contact Number, Account Number and Company Name (All have to be unique in Xero) in that order if given. See Create/Update contacts whilst importing below.

Multiple Transactions with Payment

Multiple Transactions with Payments + Contact Update/Create

Multiple Transactions with Payments + Contact Update/Create

It is advisable to bring in as Inclusive of Tax to reduce rounding problems. See Tax Options below.

Xero Multiple Transactions from CSV With payment Video

 
 
   Import Multiple Xero Transactions from CSV with Payments
  0:00 Open Example included with PostTrans for Xero
  0:20 Look at the CSV file
  1:20 Import the CSV file
  2:00 Import Sales Transaction into Xero
  2:20 Using a VLOOKUP to translate codes to Xero Codes
  3:40 Viewing the Transaction in Xero
 
 

Xero Transactions Imported into Multiple Companies Video

The following video shows how you can import into multiple Xero companies from within one sheet. Ideal for Inter-Company transactions and recharges between companies.

 
 
   Xero Transactions Imported into Multiple Companies
  0:00 Open Example included with PostTrans for Xero
  1:30 Xero Transactions explained
  2:20 Importing Xero Transactions into Multiple Xero Companies
  4:50 Viewing the result Transactions in Xero
 
 

Transaction Import Options

The header of the Transaction Import Templates have a section which contains Settings/Defaults, used to alter the PostTrans behaviour, and provide default values:

Transaction Import Options in Header of Template

Transaction Import Options in Header of Template

The default Account is only used if a line has no Product Item and not set as a default on Contact Record. i.e. an option of last resort. It can be specified on each line if need be.

Option Description
Bank DBL Need if payments included. Double click to view Bank account in Xero
Status Import Transaction as DRAFT,SUBMITTED,AUTHORISED. If with payment must be AUTHORISED (Quotes can also be SENT,DECLINED,ACCEPTED)
Def Sales Acc The default Account is only used if a line has no Product Item and not set as a default on Contact Record. It can be specified on each line if need be.
Def Purchase Acc ditto

The Transaction Import has the following options, which are accessible by pressing the Setup button on the toolbar:

Transaction Import Options in Header of Template

Transaction Import Options in Header of Template

From this tab we can:

  • View Current Xero Company. Change it by pressing Change or Force Login to change the Xero authorisation of companies
  • Check for updates
  • View registration details. View Your Account and Billing takes you to your online account to purchase/cancel and manage subscription.

Transaction Import Options in Header of Template

Transaction Import Options in Header of Template

Option Description
Turn off all Cell Lookups Stop In-Cell Searching

Option Description
Read Product description from spreadsheet If OFF then get description from Xero Product Item if Product. If ON gets from cell
Qty : Ignore 0 qty Transactions Ignore any lines with 0 Qty
Enforce Tracking entry 1 Enforce Tracking entry
Enforce Tracking entry 2 Enforce Tracking entry
Allow zero sales price If OFF will not import a Product with zero value
with warning (allowed to continue) Or just warn
THPayAmt:add up if in Lines (Else take last value) If Payment is specified in THPayAmt then add up all values in transactions, else use the last value read.

Option Description
Show Summary before posting transaction Show Summary, Otherwise process in batch of 25 Transactions
if ABOVE OFF don't show warnings SILENT Do silently. Ideal if import done by Marco or VBA
Show Nominal breakdown Show detailed breakdown or not
Show Cost Tracking breakdown Show detailed breakdown or not
Show Product Item breakdown Show detailed breakdown or not
include Margin and Cost Show detailed breakdown or not

Default Tax System Exclusive/Inclusive

Transaction Import Options in Header of Template

Transaction Import Options in Header of Template

This options sets if the Transaction/ Transactions in the template are all inclusive of TAX, Exclusive or have no Tax.

INCLUSIVE of TAX - the value of each line is taken from line tag TLTotIncluOfTax. If product item, Xero will lookup Standard price and apply discount if different from the Net amount given.

OR

TLUnitPrice is treated as being inclusive of Tax, multiplied by Qty to give the total including Tax.

If Payments are to be imported then it is advisable to go with Inclusive Tax, specifying it in column tagged with TLTotIncluOfTax.

EXCLUSIVE of TAX – the line value is from TLUnitPrice or (TLQty*(TLUnitPrice-Discount)). Tax is then calculated for line, but this calculated Tax can be overidden using TLTaxManAmt.

NOTAX - Same as for Exclusive, but "No Tax".

Tax Explained

 
 
   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
 
 

Contact Search

If single Transaction template, THContact tag Cell allows in-cell searching to search for Xero Contact, or identify Xero Contact by Account Number or Name, depending on the switch “Search mode”. Default is “2-Search by Name match”. When “1-Search by Account Number and then by Name” the search will try to match Xero Contact by Account Number, but enter a SPACE +”EXAMPLE” will search for all contacts with “EXMAPLE” in their name.

If a single Transaction, after Contact Selection the Tagged Cells beginning with “Co” will be populated, allowing Company name, address, phone, and overdue balance to be displayed in Excel template, as shown in above examples. These can even be updated, see next section.

If “and Clear transaction after Contact Selection” is ticked then PostTrans will perform the action of Clear button before populating Contact details to sheet.

Contact Update/Create Pro

While importing Single Transaction, after data entry, or Multiple Transactions, curtain contact fields can be updated or a whole Xero Contact created depending on the settings “Create/Update Contact” section of the “Defaults” tab.

Firstly, the Mode can be set, to Read Only, Update, Create/Update or Create Only, which is fairly self explanatory.

If “Ready Only” then none of the following is applied.

Single Transaction

Example, see above

We have a Xero transaction template, with THContact (Search for Contact) cell, and CoID, CoName, CoAccountNum (Account Number), CoAddressFields mapped in the header.

Single Transaction Update

If update then, the THContact cell allows for searching, and populates the CoID, CoName, CoAccountNum (Account Number), CoAddressFields. The user then edits the address, enters a Transaction, and presses Import. PostTrans will then find the Contact by the value in CoID

Single Transaction Create

To enter new contact leave THContact and CoID cell empty. The user then enters the address, enters a Transaction, and presses Import. PostTrans will then create the Contact and populate CoID.

If need be tag THDefTaxCode can be added to the header to specify the default Tax Code for the whole transaction if not set on the Xero Contact record. Which would the case if new Contact.

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
 
 

Multiple Transactions Update/Create

We have a Xero transaction template, with CoName, CoAccountNum (Account Number), CoAddressFields as columns heading is the lines of data. CoID, CoContactNum, or just CoName can be used to identify the contact instead of CoAccountNum depending on your requirements.

As PostTrans process each line, it reads the CoAccountNum (or the alternatives mentioned above) and checks if it exists, if not it is created.

Note: in Xero, Contact Company Name, tag CoName, has to be a unique name.

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
 
 

Cache and Performance

PostTrans, when needing Xero Data (Accounts, Tax Rates, Contacts) for validation and associated values, caches the data in a local store. The Cache button gives access to the currenct Cache Status and the refresh settings:

Cache settings for Xero Data

Cache settings for Xero Data

When PostTrans needs an associated value, it checks to see how long it since that table was updated in the cache. If the data is older than the setting, then PostTrans will read and changes made in Xero since the last read from Xero Accounting data. The time period can be:

  • 10 secs
  • 30 secs
  • 1 min
  • 5 mins
  • 15 mins
  • 2 hrs
  • 1 day
  • Never

Certain tables, which you don't change can be set to a long refresh period, or Never and refreshed data manually.

The Product Item refresh has the additional setting:

  • Qty - Refresh whole Product list - to show Qty during searching SLOWER
  • No Qty - Refresh Product changes - Qty not shown FASTER

For performance, when posting Transactions, Contact, Product items PostTrans writes to Xero in batches of 50 records for opitmal performance.

Please see Online Training for how to customise PostTrans Templates for Xero.

Common Features

In-Cell Searching

Any Tag ending with ^ will 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.

Xero Data Cache

All associated Account, Tax, Currency, Contact and Product Item data needed to validate, or provide In-Cell Searching, is cached locally on you PC to enable PostTrans to deliver Optimal performance. This cache can be tailored to you own particular needs using the Cache button the tool bar.

Cursor Path

The path the cursor takes through the sheet, when entering data manually, can also be easily defined in Setup.  See Set Cursor Path

Importing the Data

Simply press the Import button to validate and import the data into Xero.  PostTrans will read the Header cells and then process the rows of data until TLDesc column contains no data. The Xero 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.

Validation

Any values and associated codes will be invalid during import.   The cursor will be moved to highlight any problems in data.

POSTED Document Ref

After Posting a transaction, PostTrans will write "POSTED:" and the transaction reference number in column A (tag TLPosted).  Lines marked as "POSTED" are then ignored by PostTrans when re-posting. So, transactions cannot easily be posted twice. So the transaction cannot easily be posted twice.   The user would have to remove "POSTED" from column A in order to post the transaction a second time.  The user would have to remove "POSTED" from column A, to post the transaction a second time.

Confirmation before Posting Transaction

Just before posting the validated transaction, PostTrans will optionally show a transaction summary for the user to validate.   This can be turned off in Setup window.

File Import Button

This can be enabled in Setup to easily import CSV, 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.

Clear Button

The Clear button will clear all values on the sheet, according to the row just above the tag row, and copy down any formula.  Clear Transaction and copy down formula

Evaluation and License

This functionality will work with Xero Demo data without a license. A license is required to import into any other Xero Company. 
A subscription be purchased, and cancel on-line at any time very easily.   Pricing

What else can I do?

Other Xero Extract/Import types

Frequently Asked Questions?

Got another question? It may well be covered on our Frequently Asked Questions page.

PostTrans Tags which map to Xero

Not all of these tags/columns are needed. The table below lists the required and recommended:

Tag Name

Data Type

Description

Important Fields:

TLPosted

Text Req

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

TLCRC

Text

When posting multiples a code is written to this column, it this then used to detect changes

Lines:

TLItem^

Text 100

Item/Product code. (Double Click to view Product Item in Xero)
    In Cell Searching values in table ITEMNODESC

TLDesc

Text 3000 EOF

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.

TLQty^

Double

Quantity, if Service Transaction then can be omitted, and lines with TLNett will have Qty 1. PRO – For Product Transactions - If switch `ignore ZERO qty` ignores any lines with zero qty. This allows a long product list to be listed, and user simply edit a qty to book IN/OUT
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLUnitPrice^

Currency

Unit Price for each item for Exclusive of Tax. . If cell is blank then PostTrans will lookup customer price on change of Qty, or adding a product. If option is on to override the price, in System setup >> Lookups >> Switches, then the customers price will overwrite any value already in cell, on change of Qty. Price will be after customer discount. PRO - If multi-currency is ON will be in account currency.
Unit Price
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

Tax:

TLTaxCode^

Text 100

Tax Code. Will fill in default if blank. Can be Xero Code or Tax name e.g. OUTPUT2 or 20% (VAT on Income)
Default Tax System Exclusive/Inclusive
    In Cell Searching values in table TAXCODE-TRANS_TLTAXCODE

TLTaxManAmt

Currency

Tax Amount, per item, if calculated manually. EXCLUSIVE of Tax only. Use with Caution as you are responsible for calculating Tax correctly.
Default Tax System Exclusive/Inclusive

TLTotIncluOfTax

Currency

If set Tax Type to INCLUSIVE, Line Total inclusive of Tax. PostTrans will calculate Tax and Unit Price back from Inclusive Tax Total for Line given Qty and overcome rounding problems. Best choice when importing data as reduces rounding problems Line Total inclusive of Tax. PostTrans will calculate Tax and Unit Price back from Inclusive Tax Total for Line given Qty and overcome rounding problems. Use THNetDiscount to account for discount. Best choice when importing data as reduces rounding problems

Financial Analysis:

TLAccCode^

Text 10

Account/Nominal Code, if not specified then uses default (Double Click to view Xero Chart of Accounts)
    In Cell Searching values in table ACCCODE-TRANS_TLACCCODE

TLTracking1^

Text 100

Tracking code on the Line.
    In Cell Searching values in table TRACKING1

TLTracking2^

Text 100

Tracking code on the Line.
    In Cell Searching values in table TRACKING2

Discount:

TLDiscValue^

Double

Sales:Additional Discount Value to be applied to TLUnitPrice,
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLDiscPerc^

Double

Sales:Additional Discount Percent to be applied to TLUnitPrice
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

Line Special Functions:

TLSkipLine

Yes/No

If YES then skip this line

TLForceNewTrans

Yes/NoTF

If YES then force a new transaction

TLForceBlankDesc

Yes/NoTF

If YES then force Blank Description

Analysis:

Transaction Header:

THTransType^

Text 2 Head Chg

Transaction Type. SI,PI,SQ,PO,SC,PC etc. (Double Click to view Xero Transactions)
        In Cell Searching values:Transaction Type
                  SQ, Sales Quote
                  SI, Sales Invoice
                  SC, Sales Credit
                  PO, Purchase Order
                  PI, Purchase Invoice
                  PC, Purchase Credit

THContact^

Text 250 Head Chg

Used to Identify Contact. When in cell searching can search by Account number (or name prefixed with SPACE) or Name (see System Setup >> 'Defaults'). But will also match Contact given Account Number, Xero Contact ID, Contact Number or Contact Name, but if omitted will match by CoID, CoContactNum, CoAccountNum or CoName
    In Cell Searching values in table CONTACTALL

THTransDate

Date Head Chg

Transaction Date

THDueDate

Date Head

Due Date for Inv, Delivery Date on Order, Expire Date on Quote. SO/PO only

THExpctDate

Date Head

Expected Payment Date

THTransRef

Text 255 Head Chg

System assigned OR if specified must be unique(sales) and non-unique(Purchase), blank to have system assign. If transaction already exists it will be overwritten

THRef

Text 255 Head Chg

Reference number

THDefTaxCode^

Text 60 HeadOnly

Set all lines to this Tax Code if not overridden on line, or on contact. Else use Nominal Account default. Needed if Contact creation allowed, as we don't know default
    In Cell Searching values in table TAXCODE-TRANS_THDEFTAXCODE

THDefSalAcc^

Text 60 Req, HeadOnly

Set Default Sales Nominal code if not specified in transactions or on Contact defaults
    In Cell Searching values in table ACCCODEREVENUE

THDefPurAcc^

Text 60 Req, HeadOnly

Set Default Purchase Nominal code if not specified in transactions or on Contact defaults
    In Cell Searching values in table ACCCODEEXPENSE

THStatus^

Text 60 Req, HeadOnly

Import Transaction as DRAFT,SUBMITTED,AUTHORISED. If with payment must be AUTHORISED (Quotes can also be SENT,DECLINED,ACCEPTED)

Currency:

THCurCode^

Text 3 Head Pro

Currency code, will only show currencies defined as Bank accounts
    In Cell Searching values in table CURRENCY

THExchRate

Double Head Pro

Will use system rate or override using this tag

Discount on Header:

THNetDiscount

Currency Head

Net Discount Value applied to whole transaction

Other:

THDocTheme^

Text 60 Head

Theme/Branding of documents
    In Cell Searching values in table Branding

Purchase Order:

THPoAttenTo

Text 255 Head

Delivery Attention To

THPoInst

Text 255 Head

Delivery instructions

THPoPhone

Text 255 Head

PO Phone number

THPoAdd

Text 255 Head

PO Delivery Address

Header Payment:

THPayBank^

Text 10 Head

Bank Account for Payments
    In Cell Searching values in table ACCCODEBANK

THPayAmt

Double Head

Amount Paid - Best to use INCLUSIVE TAX when posting payment to make sure the totals agree, else rounding problems could be encountered. Switch in Setup adds these values up or use the last value in column

THPayRef

Text 60 Head

Payment Ref. If specified without THPayAmt, then takes invoice amount

THPayIsRec

Yes/NoTF Head

Payment is Reconciled.

Quote:

THQuTitle

Double Head

Quote Title

THQuSummary

Text 3000 Head

Quote Summary

THQuTerms

Text 4000 Head

Quote Terms

Attachments:

THAttch1

Text 255 Head XML

File attachement1

THAttch2

Text 255 Head XML

File attachement2

THAttch3

Text 255 Head XML

File attachement3

THAttch4

Text 255 Head XML

File attachement4

Other xxxx:

THSendEmail

Yes/NoTF Head

YES to send by e-mail (Sales Invoice only - STATUS SUMBITTED,AUTHORISED or PAID)

THSent

Yes/NoTF Head

Mark as Sent to customer if YES

Header:Add Message:

THAddNote

Text 2500 HeadOnly

Add Note to Transaction

THMessTop

Text HeadOnly

Add message at top of transaction
Creating Message Top/Bottom

THMessBot1

Text HeadOnly

Add message at bottom of transaction
Message Top/Bottom

THMessBot2

Text HeadOnly

Add message at bottom of transaction
Message Top/Bottom

Analysis xxxx:

THDefTrack1^

Text 100 HeadOnly

Set Tracking for whole transaction unless overridden in Lines
    In Cell Searching values in table TRACKING2

THDefTrack2^

Text 100 HeadOnly

Set Tracking for whole transaction unless overridden in Lines
    In Cell Searching values in table TRACKING2

Transaction Update:

Lookup Item Values:

PrQtyOnHand

Double Read

Item/Product Qty free, updated when stock code entered

PrPrice^

Currency Read

Price in currency
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

PrSuppAccount

Text 8 Read

Supplier`s Account Reference Lookup

Header:Before Posting:

THBefSWCompany^

Text 6 Head Chg Pro

This will cause PostTrans to change company before posting if different from current Sage Line 50 Company. Thus can be user to post transactions into different companies, thus intercompany transactions are possible. It uses a 6 letter company code, which found by pressing the Select Company button in system setup. Must be specified in each row.
Posting to multiple companies
    In Cell Searching values in table CompanySelect

THBefDisplayMess

Text 60 HeadOnly

If contains value, then this text is displayed, and import cannot take place until this text if clear. Thus you can your own formula, macro or VBA code to stop import until a certain condition.
Validating Transaction before posting

Header - After Posting:

THBefDisplayMessYN

Text 60 HeadOnly

If contains value, then this text is displayed. Asks Yes/No to continue
Validating Transaction before posting

THAftWriteRef

Text 60 HeadOnly Read

After posting write back transaction reference to this cell

THAftWriteAlocRef

Text 60 HeadOnly Read

SOP After posting write back stock allocation status, Full, Part

THAftWritePayRef

Text 60 HeadOnly Read

Writes back the reference of payment transaction, if posting a SA on Sales Order

THAftProcessSheet

Text 60 HeadOnly Read

After posting this sheet, switch to work book with this name, and post that. Allows back to back purchase orders, or multiple company transfers. PRO
Posting into multiple Xero Companies

THAftSwitchSheet

Text 60 HeadOnly Read

After posting this sheet, switch to work book with this name PRO
Posting into multiple Xero Companies

Header - After Save:

THAftSavePATH

Text 60 HeadOnly Read

If THAftSavePATH and THAftSaveFilename specified, will save as PATH + ‘\YYYMM\’ + Filename + "_0000.xls". YYYMM can be overridden with value in THSaveSubDirectory cell.
Saving template after posting

THAftSaveSubDir

Text 60 HeadOnly Read

See THAftSavePATH
more about saving

THAftSaveFilename

Text 60 HeadOnly Read

See THAftSavePATH. Example ‘ABAP01’ will saveas ABAP01_0001.xls.
more about saving

Import File:

THImportFullName

Text 500 HeadOnly

Full PATH and Filename of the file just imported using the Import button. This is required if you set the directory to move file after posting transaction.
File Import instructions

THImportFileName

Text 500 HeadOnly

Filename of the file just imported using the Import button
File Import instructions

After Account Selection:

CoID

Text 60 Rel

Xero Contact ID. System Setup >> 'Defaults' allows Contact creation. If Single Transactions then this is used to store the Contact ID for subsequent contact updates

CoName

Text 60 Rel

Xero Contact Name. System Setup >> 'Defaults' allows Contact creation/update.

CoAccountNum

Text 100 Rel

Account Code for Contact. If importing mutiple Transactions this will be used to identify accounts, if no Xero Contact ID given

CoContactNum

Text 100 Rel

Contact Number used by external systems. Shown at the bottom of Xero UI. If importing mutiple Transactions this will be used to identify accounts, if no Xero Contact ID given

Financial:

CoOutstanding^

Currency Read Rel

Outstanding over 30 days.
PRO Double clicking on this cell will drill down to Ledger.
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

CoOverdue^

Currency Read Rel

Available Credit.
PRO Double clicking on this cell will drill down to Ledger.
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

Registered Address:

CoAddressType

Text 100 Rel

AddressType

CoAttentionTo

Text 100 Rel

AttentionTo

CoAdd1

Text 100 Rel

AddressLine1

CoAdd2

Text 100 Rel

AddressLine2

CoAdd3

Text 100 Rel

AddressLine3

CoAdd4

Text 100 Rel

AddressLine4

CoCity

Text 100 Rel

City

CoRegion

Text 100 Rel

Region

CoPostalCode^

Text 100 Rel

PostalCode
More about Postcode Address Lookup
    In Cell Searching values in table SimplyPostcode

CoCountry

Text 100 Rel

Country

Street Address:

CoStAddressType

Text 100 Rel

StAddressType

CoStAttentionTo

Text 100 Rel

StAttentionTo

CoStAdd1

Text 100 Rel

StAddressLine1

CoStAdd2

Text 100 Rel

StAddressLine2

CoStAdd3

Text 100 Rel

StAddressLine3

CoStAdd4

Text 100 Rel

StAddressLine4

CoStCity

Text 100 Rel

StCity

CoStRegion

Text 100 Rel

StRegion

CoStPostalCode^

Text 100 Rel

StPostalCode
More about Postcode Address Lookup
    In Cell Searching values in table SimplyPostcode

CoStCountry

Text 100 Rel

StCountry

Primary Conctact:

CoFirstName

Text 100 Rel

First Name

CoLastName

Text 100 Rel

Last Name

CoEmailAddress

Text 255 Rel

e-mail1

Other Contacts:

CoFirstName1

Text 100 Rel

First Name

CoLastName1

Text 100 Rel

Last Name

CoEmailAddress1

Text 255 Rel

e-mail1

CoEmailInc1

Yes/NoTF Rel

Include in Mail

CoFirstName2

Text 100 Rel

First Name

CoLastName2

Text 100 Rel

Last Name

CoEmailAddress2

Text 255 Rel

e-mail1

CoEmailInc2

Yes/NoTF Rel

Include in Mail

CoFirstName3

Text 100 Rel

First Name

CoLastName3

Text 100 Rel

Last Name

CoEmailAddress3

Text 255 Rel

e-mail1

CoEmailInc3

Yes/NoTF Rel

Include in Mail

CoFirstName4

Text 100 Rel

First Name

CoLastName4

Text 100 Rel

Last Name

CoEmailAddress4

Text 255 Rel

e-mail1

CoEmailInc4

Yes/NoTF Rel

Include in Mail

CoFirstName5

Text 100 Rel

First Name

CoLastName5

Text 100 Rel

Last Name

CoEmailAddress5

Text 255 Rel

e-mail1

CoEmailInc5

Yes/NoTF Rel

Include in Mail

Other Contact xxx:

CoSkypeUserName

Text 100 Rel

Skype

Defaults:

CoSalesTaxType

Text 100 Read Rel

SalesTaxType

CoPurchTaxType

Text 100 Read Rel

Phone

CoTaxType

Text 100 Read Rel

Tax Type

CoCurrency^

Text 3 Read Rel

Currency
    In Cell Searching values in table CurrencyCode

Phone:

CoPhoneContry

Text 100 Rel

Country

CoPhoneArea

Text 100 Rel

Area

CoPhonePhone

Text 100 Rel

Number

CoFaxContry

Text 100 Rel

Fax Country

CoFaxArea

Text 100 Rel

Fax Area

CoFaxPhone

Text 100 Rel

Fax Number

CoMobContry

Text 100 Rel

Mob Country

CoMobArea

Text 100 Rel

Mob Area

CoMobPhone

Text 100 Rel

Mob Number

CoDDContry

Text 100 Rel

DD Country

CoDDArea

Text 100 Rel

DD Area

CoDDPhone

Text 100 Rel

DD Number

Contact Groups:

CoGrps

Text 100 Read Rel

Contact Groups

CuDiscRate

Double Rel

Discount Rate %

CoSalesAcc^

Text 100 Read Rel

Sales Default Nominal Code
    In Cell Searching values in table NomCode

CoPurchAcc^

Text 100 Read Rel

Purchase Default Nominal Code
    In Cell Searching values in table NomCode

CoAccCode^

Text 100 Read Rel

Default Nominal Code which will be Sales or Purchase based on transaction type
    In Cell Searching values in table NomCode


Req = Field is required    EOF = End of file, Stop when no more values    


Rel = Used to get associated data in Transaction Header

XML = Un-attended PostTrans XML API for Sage 200

Head  A header tag can be used in header section, before row 30, of the transaction template.  Or in the rows data of data, below row 30, of the template like a traditional CSV importer.

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.

Single Transaction:

 

Single Transaction

 

OrderPad with Price Bands

 

OrderPad

Multiple Transactions:

 

Inc of Tax with Pay and creates/updates Contacts

 

Inc of Tax with Pay, Product Item and creates/updates Contacts from file

 

Import Transactions from File BLANK TEMPLATE

 

Exclusive of Tax

 

Transactions to Multiple Companies

Multiple Transactions:

 

Single Nominal Journal

 

Single Nominal Journal with Contra Line

 

Multiple Nominal Journals to Multiple Companies

 

Multiple Nominal Journals

 

Import and Match Sales Receipts/Payments

 

Import and Match Sales Receipts/Payments from CSV file