Menu

Sales Receipts

Save time updating

Sage 50

Stock/Product Creation
Customer/Supplier Prices
Bill of Material
Project Creation
Location Bins

Sage 200

Simply extract, edit and import

Xero

Import from Excel

50 Trans

Nominal Journals
Quotes/Invoices/Orders
Remote Orders
Product/Stock Adjustments
Sales Receipts
Quick OrderPad

200 Trans

Transaction from Manual Input

Image Alt TextImage Alt Text

Multiple Transactions from CSV file

Xero

Solve the Puzzle?

Sage 50

Help on every type of import
Demo Video of all functionality
Loads of Examples included
Online Training
We can help too…

Sage 200

Image Alt Text

Xero

Great Customer Satisfaction

Customer Comments

Full Trial with Demo Data
Low cost Subscription
Cancel anytime
From a £1 a day
Saving you hours

Simply extract, edit and import

Power of Excel

Sage 50

Quick Template Creation
Customise with:
    Formula
    Macros
    VBA

Sage 200

To reach your goals quickly

Sage 200

Sage 200 Bins

Sage 200 Warehouse & Bins

Create Sage 200 Bins
Stock Take Bins
Move Bin Stock

Fast, cost effective, easy…

Image Alt TextImage Alt Text

Manufacturing

200 Manufact.

Create BOM Stock Easily
Create Bill of Materials
Labour, Machine, Tools

Image Alt Text

Complex tasks quicker...

50 BOM

Project Accounting

200 Projects

Create Project Structure
Update Budgets
Simple Project Entry

Make life easier...

Image Alt Text50 Projects

Import Sales Receipts into Sage 200

Sage 200:Import Receipts against outstanding invoices

All of the following functionality requires the PostTrans Extras Module.

This type of PostTrans import, imports Receipts against outstanding invoices, which can be input manually, data from an ODBC connection or imported from CSV file.

Page Contents

Receipts against outstanding invoices in Sage 200

Sage 200 Receipt Import Features

  • Import a simple list of Receipt with matching ref to allocate to Outstanding Invoices
  • Use for manual entry, simply enter amount and Cheque No against Outstanding Invoices
  • Work on 1 or more back accounts at a time in different currencies
  • Allocate to Multiple Invoices
  • Matching information written back to sheet for conformation
  • Validation highlights any problems for easy correction

Examples of importing Sage 200 Receipts

Manual Allocation of Receipts

Multiple Receipts lines can be entered by using the "receipts" example (download below).

Extract is pressed PostTrans will read all outstanding Invoices and populate the sheet for allocation, for all Back Accounts listed in list (Cell D3 in example below). The user can then simple enter the amount to allocate in column L (Tag TLRcPayment) and the reference in Column M (Tag TLRcRef)

After all receipt data is entered Import will then scan down the sheet looking, and attempt to match the Receipt to the Invoice by Invoices number. If matched it then imports a receipt into Sage 200, and allocate to the invoice (or invoices if same ref or 'X' entered) listed on the line.

In the example below we have all columns included, but unwanted once can be removed or hidden.

Posting a Receipts after manual allocation

Posting a Receipts after manual allocation

After pressing the Import Button:

Conformation and Validation before Posting Receipt Line

Conformation and Validation before Posting Receipt Line

Conformation can be turned off in PostTrans Setup

Posting a Receipts via imported Data

See "receipts from file" example (download below). The "Invoice" sheet allows you to import test invoices.

Data can be brought in from an external system via CSV file very easily (see demo video). Receipts are then allocated to invoices based on a uniquer reference specified in invoice Ref, Ref2 or User defined field (Named in cell D7), plus optionally matching the Account code. Or oldest matching value in account, in which case we must specify the account code too.

Import Receipts Data from External System into Sage 200

Import Receipts Data from External System into Sage 200

In the above example we have set the options to match by the Ref2 field on an invoice (Cell D3). The import file need only specify this Reference to match by, Receipt Value and Receipt reference, and therefore must be unique, to import a payment an match off to invoice.

Receipts Validation

The receipt must be in same currency as the Invoice, but one sheet can handle many different currencies as specified in cell D3

If any errors are found the user is offered the debtors list, as read from Sage 200, in order to verify the error

The Debtor list can extracted by pressing Extract, No and then Yes to the questions

Template Structure for Transaction Data

The template must have tag ‘Sage200TransReceipt’ in the comment of cell A1 to instruct PostTrans that we are interested in importing Transactions, and that the sheet is a PostTrans Template. Pressing Import or Extract on a blank sheet will either open an existing example or create basic template for you to customise.

The top section of this template contains Transaction header fields, in the case of a single transaction, or just transaction type of all multiples transactions list from row 30 onwards.

These Transaction header fields can be deleted if not required, or added by moving the cursor, to a cell in the header area, and pressing the Tag button. This will display a list of PostTrans Tags. A tag beginning with “PeH”, denoting for use in a header, can then be inserted. Table of Receipts against outstanding invoices Tags

Row 28 contains a series of tags, which denote to PostTrans the data to be read/written in each column. These tags, and data columns can be rearranged to suite your needs, and the tags are added using the Tags button. Deleting the Tag name, in the cell, from this row removes the Sage 200 mapping.

Row 27 designates what will happen when the Clear button is pressed, see Clear Transaction and copy down formula

Hovering over these tags, and header cells will show a comment which explains the tags purpose, data type and any other useful details.

For importing multiple transactions, most header tags can be copied/moved to row 28, thus designating a column to set the header value. 

On-line Training video of how all this works 

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.

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 Sage 200.  PostTrans will read the Header cells and then process the rows of data until TLRcMatch column contains no data. The Sage 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 can not 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 Sage Demo data without a license. A license is required to import into any other Sage Company. 
A subscription be purchased, and cancel on-line at any time very easily.   Pricing

What else can I do?

Other Sage 200 Extract/Import types

Frequently Asked Questions?

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

Demonstration Video

Since the functionality of this type of import is quite broad, the following Videos/Blog articles have videos showing it in action:

 
 
   Sage 200 Receipt Import and Allocation via Manual imput
  0:00 Template Design - Where, How and optional See http://www.posttrans.co.uk/Sage-200/Transactions-for-Sage-200/Sales-Receipts
  1:08 Specify the Bank Accounts and Options
  2:00 Extract outstanding invoices
  2:40 Copy formulee down sheet
  4:00 Add some Receipt figures
  5:00 Import to match and allocate to invoices
  7:00 Show in Sage 200
  7:40 How to see Debtors list
 
 

 
 
   Sage 200 Receipt Import and Allocation from file
  0:00 File Import Scenario
  1:00 Template Design - Where, How and optional
  2:00 Invoice Matching Options
  3:10 How to see Debtors list
  4:50 Simulating the CSV file
  5:10 Explain File import
  6:00 Import to match and allocate to invoices
  9:00 Show in Sage 200
 
 

PostTrans Tags which map to Sage 200

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

Posting Settings:

THBankAccList

Text Req, HeadOnly

Filter by Currency and allocate to Bank Account for currency. e.g. List of Currency Code, Bank Account No like ‘GBP,1,EUR,2,USD,3’

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 or URN
                  VALUE, Account Oldest Matching Value
                  REF, Reference
                  REF2, Reference2
                  USER, User defined
                  REFACC, Reference and Account Code
                  REF2ACC, Reference2 and Account Code
                  USERACC, User defined and Account Code

THTolerance^

Integer Req, HeadOnly

Match Tolerance
        In Cell Searching values:Tolerance
                  1, Exact
                  2, Exact and Anything less

THUserDefName

Text 60 Req, HeadOnly

If USER is selected then this cell defines the user defined field name, in table SLPostedCustomerTran, to match on

Receipts Lines:

TLRcRef

Text 20 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

Sales Receipt Fields:

TLRcRef2

Text 30

Set Referance2 on Sales Receipt

TLRcDetails

Text 60

Details. If not specified will be `Sales Receipts`

TLRcTaxCode^

Integer

Tax Code. Make sure the format of the cell is “General” else search may not work properly
    In Cell Searching values in table Tax

TLRcAcCode^

Text 8

Optional, depending on match settings
    In Cell Searching values in table Cust

TLRcPaymentDate

Date

Date paid, blank defaults to today

TLRcProject^

Text 12 Pro

Project Code
    In Cell Searching values in table ProjectCode

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

TLInvDueD

Date Read

When used in Manual Mode, will be populated with Due Date on Extract

TLInvRef

Text 20 Read

Reference

TLInvCur

Text 3 Read

Currency Number

TLInvRef2

Text 20 Read

Reference2

TLInvTransType

Text 60 Read

Transaction Type Invoice/Credit Note

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 200, 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 receipts from FILE (Module: Extras)

Sheet:'Receipts from File'
Import Receipts

 

List outstanding Invoices, manual receipt and allocation (Module: Extras)

Sheet:'Receipts'
Import Receipts