Xero:Import Xero Receipts/Payments and Allocate/Match to Invoices in Excel
All of the following functionality requires the Pro version of PostTrans.
Import Xero Sales Receipts/Payments
A more recent version, not shown on any of the screenshots/video below, has an additional tag in the Header section of the template to set the Process Type to "R" for Receipts or "P" for Payments.
PostTrans imports Sales Receipts, or Payments, in two different modes, each discussed below with demonstration videos:
- Manual Allocation - Extract outstanding invoices, enter receipt info against them and import into Xero.
- Automatic Allication Mode - Import receipts, and allocate against outstanding invoices, based on a matching unique Referance number or Invoice No.
1) Manual Allocation Mode
Match by:MANUAL, Manual Allocation
See example "Import and Match Sales Receipts/Payments"
Pressing Extract button will populate the Excel sheet with all Outstanding Invoices in Xero. The template columns can be rearranged, additional functionality added or items removed to for fill your needs.
TLRcMatch column is populated with the Invoice number, and when Import is pressed the sheet is scanned for payment values. If this cell contains a value then, a Sales Receipt is imported for the value and entered reference. If just a reference is entered then the full value of the outstanding invoice is allocated, plus multiple invoices can be allocated to single payments. Please see the demo video below for more details.
Demonstration Video
Import Xero Receipts/Payments and Allocate/Match to Invoices in Excel 1/2
| |
0:00 |
Open Example included with PostTrans for Xero |
| |
1:10 |
Explanation of Template |
| |
2:00 |
Bank Accounts and Currency |
| |
3:20 |
Match by Options |
| |
4:30 |
Extract Xero Outstanding Invoices |
| |
5:00 |
Explanation of Xero Data Extracted |
| |
5:30 |
How to enter Xero Payment Allocations |
| |
6:45 |
Adding a Note against Xero Receipt |
| |
7:55 |
Import Xero Payments and Allocate against Invoices |
| |
9:00 |
Showing the Proof in Xero |
To use the template, with Xero, 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.
2) Automatic Allocation Mode
Match by not set to:MANUAL, Manual Entry
See example "Import and Match Sales Receipts/Payments from CSV file"
In this mode, Xero Sales Receipts/Payment are imported into Xero, and are matched to Outstanding Sales Invoice by a matching value in TLRcMatch Column. The value in the TLRcMatch can be matched by Invoice Number, Reference, or Oldest matching value on account ledger.
This is therefore ideal for users who want to import Invoice from an external system, and at a later date import the Sales Receipts, matched on a matching value on the original invoice.
In the above example we can see we can allocate one payment to multiple invoices simply by using the same Payment Ref. See video below for demo.
Debtors List
To aid diagnosis, if a reference does not match PostTrans will optionally open a new sheet showing the outstanding transactions found in Xero and all references on those transactions. This can also be seen by pressing the Extract button on the Sales Receipt template.
Demonstration Video
The video shows how to use PostTrans Pro for Xero, to import Sales Receipts from within Excel, which relate to existing Sales Invoices in Xero. These are allocated to the invoice by a matching unique reference, which is in the Invoice data. In our example the Invoice Number.
This would be used, in a scenario where the Invoice data has originated from an external source, website or bespoke program. And at a later date the receipt information is generated by same program, which needs matching off against the invoices in the accounts.
Import Xero Receipts/Payments and Allocate/Match to Invoices from CSV file in Excel 1/2
| |
0:00 |
Open Example included with PostTrans for Xero |
| |
1:00 |
Explanation of Template |
| |
1:30 |
Importing into Xero Example Invoices |
| |
3:00 |
Reading CSV file using File button |
| |
3:30 |
Changing options |
| |
5:50 |
Optionally add a note to payments |
| |
6:00 |
Allocate Payment in Xero from the CSV file |
| |
7:00 |
Showing the proof in Xero |
| |
7:40 |
Options in Setup - Turn off Conformation |
| |
8:00 |
File import options |
To use the template, with Xero, 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.
Requirements
Pro feature
The Invoice and Payment have to be in same currency
Cannot be over allocated
Option to only match if value agrees
Available Tags
|
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
|
|
Posting Settings:
|
|
THBankAcc^
|
Text Req, HeadOnly
|
Bank account or Account marked 'Enable Payments'. Only transactions of the same currency will be processed.
In Cell Searching values in table ACCCODEPAYMENT
|
|
THMatchBy^
|
Text 60 Req, HeadOnly
|
Match by x when looking for invoice to allocate too in field TLMatch
In Cell Searching values:Match BY
MANUAL, Manual Allocation
VALUE, Account Oldest Matching Value
REF, Reference
INVOICE#, Invoice Number
|
|
THProcessType^
|
Text 60 Req, HeadOnly
|
Process Receipts or Payments
In Cell Searching values:Process Type
R, Receipts
P, Payments
|
|
THTolerance^
|
Integer Req, HeadOnly
|
Match Tolerance
In Cell Searching values:Tolerance
1, Exact
2, Exact and Anything less
|
|
THPayIsRec
|
Yes/NoTF Req, HeadOnly
|
Payment is Reconciled.
|
|
THRcPayDate
|
Date HeadOnly
|
Date paid, if not specified on line (tag TLRcPayDate). If not specified then will use todays date
|
|
Receipts Lines:
|
|
TLRcRef
|
Text 18 Req
|
Set Referance of Receipt. Repeat Ref or 'X' to continue allocation to one recipt
|
|
TLRcMatch
|
Text 60 EOF Req
|
Use this value to search for matching Invoice, allocate payment to this matched invoice
|
|
TLRcPayment
|
Currency Req
|
Payment Amount, zeros are ignored.
|
|
TLRcDetails
|
Text 60
|
Details. If not specified will be `Sales Receipts`
|
|
TLRcPayDate
|
Date
|
Date paid, blank defaults to today
|
|
TLRcCode
|
Text 60
|
Code (NZ only)
|
|
TLRcParticulars
|
Text 60
|
Particulars (NZ only)
|
|
TLRcAddNote
|
Text 100
|
Add note to Payment
|
|
Contact Info:
|
|
TLCoAccNo
|
Text 8
|
Contact Account Number
|
|
TLCoName
|
Text 60
|
Contact Name
|
|
Ready Only Tags:
|
|
TLInvOut
|
Double Read
|
When used in Manual Mode, will be populated with Outstanding amount on Extract
|
|
TLInvDate
|
Date Read
|
Date of invoice
|
|
TLInvDateDue
|
Date Read
|
When used in Manual Mode, will be populated with Due Date on Extract
|
|
TLInvDateExp
|
Date Read
|
When used in Manual Mode, will be populated with Expected Date on Extract
|
|
TLInvCredit
|
Double Read
|
When used in Manual Mode, will be populated with Credited amount on Extract
|
|
TLInvGross
|
Double Read
|
When used in Manual Mode, will be populated with Gross amount on Extract
|
|
TLInvTax
|
Double Read
|
When used in Manual Mode, will be populated with Tax amount on Extract
|
|
TLInvCur
|
Text 3 Read
|
Currency Number
|
|
TLInvNo
|
Text 20 Read
|
Reference2
|
|
TLInvRef
|
Text 20 Read
|
Reference
|
|
Header:Before Posting:
|
|
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
|
|
THBefDisplayMessYN
|
Text 60 HeadOnly
|
If contains value, then this text is displayed. Asks Yes/No to continue
Validating Transaction before posting
|
|
Header - After Posting:
|
|
THAftProcessSheet
|
Text 60 HeadOnly Pro
|
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 multiple sheets
|
|
THAftSwitchSheet
|
Text 60 HeadOnly Pro
|
After posting this sheet, switch to work book with this name PRO
Posting multiple sheets
|
|
Header - After Save:
|
|
THAftSavePATH
|
Text 60 HeadOnly Read Pro
|
If THAftSavePATH and THAftSaveFilename specified, will save as PATH + ‘\YYYMM\’ + Filename + "_0000.xls". YYYMM can be overridden with value in THSaveSubDirectory cell.
more about saving
|
|
THAftSaveSubDir
|
Text 60 HeadOnly Read Pro
|
See THAftSavePATH
more about saving
|
|
THAftSaveFilename
|
Text 60 HeadOnly Read Pro
|
See THAftSavePATH. Example ‘ABAP01’ will save as 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
|
Req = Field is required EOF = End of file, Stop when no more values
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.
|
|
|
|
Import and Match Sales Receipts/Payments
|
|
|
|
Import and Match Sales Receipts/Payments from CSV file
|
|