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.
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
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
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
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
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
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
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
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
| 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
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
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 Setup. In-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.
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:
|
|
|
|
|
Invoice Single Transaction
|
|
|
|
OrderPad with Price Bands
|
|
|
|
Invoice OrderPad
|
|
Multiple Transactions:
|
|
|
|
|
Invoices/Credits Inc of Tax with Pay and creates/updates Contacts
|
|
|
|
|
|
Invoices/Credits Inc of Tax with Pay, Product Item and creates/updates Contacts from file
|
|
|
|
|
|
Invoices/Credits Nett
|
|
|
|
Invoices/Credits Nett to Multiple Companies
|
|
|
|
|
|
Import Transactions from File BLANK TEMPLATE
|
|
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
|
|