Menu

Sales/Purchase Transactions

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

Sage 200:Import Quotation/Order/Invoice Import

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

Page Contents

Transaction Import via XML API for Sage 200

Related Pages

Import Types/Features in Sage 200

Sage 200 Transaction Types

  • SQ - Sales Quote *†
  • SP - Proforma †
  • SO - Sale Order, including with Payment, and Batch or Serial number allocation *†
  • SSO - Sale Service Order, including with Payment - Has not stock, and thus does not need dispatching *†
  • SI - Sales Invoice, including with Payment
  • SIA - Purchase Invoice + Adjust Stock OUT, including with Payment ♦
  • SIM - Sales Invoice Module †
  • SCM - Sales Credit (Invoice module †)
  • SSI - Sales Service Invoice
  • SCM - Sales Credit Module
  • SC - Sales Credit Note,
  • SR - Sales Receipt
  • SOB - Sales Opening Balance
  • SOC - Sales Opening Balance Credit
  • PO - Purchase * Order with Authorisation
  • PSO - Purchase Service Order - Has not stock, and thus does not need dispatching †
  • PI - Purchase Invoice
  • PIA - Purchase Invoice + Adjust Stock IN ♦
  • PC - Purchase Credit Note, Opening Balance and Opening Balance Credit Note
  • POB - Purchase Opening Balance
  • POC - Purchase Opening Balance Credit

* - Can be Printed or automatically e-mailed to customers when posted

♦ - Does not support Batches at present

† - This transaction type does not support Manual VAT or Inclusive of VAT amount Tags

The transaction type is set, either on the Header of the import, or on the lines of the import. See examples below.

Import Transaction Features in Sage 200

Simply add new lines to the spreadsheet from row 30 on-wards. Then press Import button, on the PostTrans Toolbar, to import single or multiple Sage 200 Quotes, Orders, or Invoice transactions (Examples shown below).

Advantages

  • Detailed conformation before transaction importation
  • Print or e-mail Transaction after importation
  • Highlighted Validation of codes and data
  • Search for Account, Stock, Project, Nominal, using our In-Cell Searching
  • Looks up, and fills in default values, including customer price from Matrix
  • Add formula, vba or macros to manipulate the imported data
  • Cannot Post Sage transaction twice. Writes POSTED in row, with transaction Ref generated.
  • Create valid Nom/Cost Centre/Department combination Pro
  • Can skip Zero Qty, and thus allows the creation of order pads Pro
  • Chain posting, allows a second template to post, for Back to Back order Pro
  • Press Extract to populate the sheet with product codes, and optionally sort by Category, to quickly create an Order Pad for fast Order entry Pro
  • Easily Add your own validation, using the THBefDisplayMessage tag
  • Easy importation from CSV file

Examples of importing Sage 200 Transactions

Posting Single Transaction

Header values can be specified in the top portion of the PostTrans template, and/or in line items in rows 30 on-wards. The template structure detail is discussed in Template Structure (below this section).

Single Transaction Layout

Single Transaction Layout

After pressing the Import Button:

Conformation and Validation before Posting

Conformation and Validation before Posting

The below is a screen shot of Sales Order template which is automatically generated after selecting this type of transaction, many of the fields are optional and are filled in automatically by PostTrans. Indeed the user need only enter Product Code and Quantity, all other fields are filled in by PostTrans, including the price to the customer.

Note: The delivery address, and credit information, is automatically populated after selecting the account code.

Thus:

Default Single Transaction Layout

Default Single Transaction Layout

Formula, VBA and macros can be added to further enhance this data entry template.

The Path the Cursor takes can also be defined

Multiple Transactions

Multiple Transactions can be imported by adding the Header fields to the Tag row 28. These are easily inserted using the Tags button, see all tags listed at the bottom of page.

Multiple Transaction Layout

Multiple Transaction Layout

Indeed we can add tag "THTransType" to allow us to post different types of transactions:

Multiple Transaction Type Layout

Multiple Transaction Type Layout

In this configuration PostTrans forces a new transaction when Account code, Transaction date or Customer reference changes. Blank values are ignored, but would probably be repeated if brought in from a CSV file.

Indeed multiple companies can be imported into:

Multiple Transaction Layout

Multiple Transaction Layout

Formula, VBA and macros can be added to further enhance this data entry template.

The Path the Cursor takes can also be defined

Customised User Defined Fields

For this type of import, PostTrans can be map Excel fields to customised user defined fields.

Template Structure for Transaction Data

The template must have tag ‘Sage200Trans’ 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.

Cell A1 (Sage 50 Sales Order in this example.  Priciple is the same)

Cell A1 (Sage 50 Sales Order in this example. Priciple is the same)

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, see examples below:

Comments map to Sage fields (Sage 50 Sales Order in this example.  Priciple is the same)

Comments map to Sage fields (Sage 50 Sales Order in this example. Priciple is the same)

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 “TH”, denoting for use in a header, can then be inserted. Table of Sales/Purchase Transactions 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

Row 28 defines the data in row 30 onwards

Row 28 defines the data in row 30 onwards

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. 

Transaction Header fields added to Row 28

Transaction Header fields added to Row 28

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 TLDesc 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

Address Entry


Pro Feature: UK addresses can have Postcode look enabled for faster data entry.   Postcode Address Lookup

Printing


Pro Feature: Transactions can be printed, or e-mailed, at the end of the import  Printing - e-mailing Transactions. Ideal for order entry templates.

Restrictions

  • Does not apply the Sage Line 200 Additional Discount, but can be calculated in sheet and posted using header Tag THAddDisc

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:

 
 
   Importing a CSV file into Sage 50/200
  0:00 Video Introduction
  1:25 Opening File Import Example
  2:00 Import Sales Orders into Sage 200
  5:00 How to setup file import
  7:15 How to find more information and Training
View the full article on this
 
 

 
 
   Quick Sales Order Pad 1/2 using PostTrans in 30 seconds
  0:00 Video Introduction
  1:10 Taking an order in 30 seconds
  2:08 Rewind for a slower explanation of process
  4:00 Adding another product to Order Pad
  6:50 Posting the Sales Order into Sage 50
  8:00 How to find more information and Training
View the full article on this
 
 

Inter-Company Transaction

It is easy to post Sage Line 50 Inter-Company Transactions, since you can either:

  • Specify the Company Code on each line, in which case PostTrans will change company every time it sees a change in company. Example 1 in the following video.
  • Specify the Company Code on the header to override the company set in system settings, then simply create the transaction on a separate sheet for each company, in THBefSWCompany, and post each individually. This can then be automated by specifying the Header tag THAftProcessSheet, to switch to named sheet and process after current. Example 2 in the following video.

 
 
   Sage 50/200 Inter-Company Transfer within Excel
  0:00 Video Introduction
  1:50 Posting SI/PI to 2 companies (Example 1)
  5:00 Prove the data has been imported into Sage 50
  6:40 Posting 2 Nominal Journals to 2 companies
  7:30 Posting SO/PO into 2 companies using 2 sheets (Ex. 2)
View the full article on this
 
 

Transactions Import Options

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

Switches in Setup window

Switches in Setup window

Option Description
  Header Options  
Turn off all Cell Lookups This option turns all in cell searching more about Sage Line 200 Related Code Search
Don't Post if over Credit Limit Check customer credit Balance before importing Order/Invoice
Don't Post if Payment is UNDER PAID Used in conjunction with THAmtPrePaid for Payments with transaction. Don’t post if the Payment does not agree with total Used in conjunction with THAmtPrePaid for Payments with transaction. Don’t post if the Payment does not agree with total.
on't Post if Payment is OVER PAID Used in conjunction with THAmtPrePaid for Payments with transaction.
Fix rounding Problems with extra line (+) or discount (-) If a difference between Payment and Lines total (can also use tag THRoundToGross), then posts an additional line if rounded up, or adds discount to round down, to account for difference in transaction. Use tag THRoundToNom, THRoundToDep to specify the Nominal/Department, else uses Sales Discount Nominal
Use System Exchange Rate Use System Exchange Rate, when posting to an account with currency other than base. Else allow override using tag THExchange, and will warn if 10% variance from system.
  Line Options  
Read Product description from spreadsheet Read description from spreadsheet, otherwise get from stock record so user cannot alter it.
Don't write TLUnitPrice to sheet Never write the Unit Price, for customer with Discount applied, to TLUnitPrice column
Do NOT apply customer discount when selecting Product Just pull through standard price band for customer
Once written Don't update TLUnitPrice If OFF then always look up the Unit Price for customer with Discount applied and write to Tag Column TLUnitPrice, after user changes TLQty. If ON then only if cell is empty, thus allowing user to overide the price, and not change when TLQty changes. If you use QTY discount breaks then this needs to be turned on.
TLUnitPrice ignore, use system price Ignore any prices specified in TLUnitPrice. So ensures prices are from system with Discount (TLDiscPerc) applied if need be.
Qty : Ignore 0 qty/hours Transactions This allows the template to have a predefined list of Products. Allowing the user to simply enter Qty against the required product lines required on Quote/Invoice/Order. This allows for the creation of Order Pads and Telesales data entry. Pro
Qty : Allocate Available Stock on Sales Order Allocate any available stock on import
Qty : Allow PO over MAX Stock Levels On purchase Order allow the stock level to go over the Max stock level.
Allow zero sales price Allow zero sales price. If cell is Blank PostTrans will look-up price and write back to cell during import. Thus to look-up prices simply press import and press Cancel on summary window, and customer prices will be populated.
with warning (allowed to continue) Display warning and allow to continue
Project Costing : Enforce Job codes in Cells Enforce Project Costing code entry on transaction Pro
with warning splay warning and allow to continue
Create valid Nom/Cost Centre/Department comb. Auto Create valid Nom/Cost Centre/Department comb. If not presnet in system. It uses the the first matching nominal code as a basisi for the settings for newly created option.
 Summary  
Show Summary before posting transaction Turn ON/OFF Summary window.
Show Nominal breakdown Show a breakdown of Nominal values
Show Department breakdown Show a breakdown of Department values
Show Project Costing breakdown Show a breakdown of Project Costing values
Show Stock Code breakdown Show a breakdown of Stock values
include Margin and cost Show Margin and Costs on summary window
Post all silently as possible Turn on to suppress registration message, and status at end

The above settings are stored in each sheet, Cell A1 Comment, where as most settings are stored per workbook

Confirmation window showing summary of transaction.

Confirmation window showing summary of transaction.

This would be turn off if importing a large number of lines from an external file.

In Cell Searching Options

After the user enters an account code PostTrans can perform the following:

Confirmation window showing summary of transaction.

Confirmation window showing summary of transaction.

Option Description
 After Selecting an Account  
Offer Delivery Addresses Offer delivery address for the account
Put Del Address in Tras Del After selecting an account, and delivery address, it is written to the delivery address tags THDel…
Put Del Address in Tras Inv After selecting an account, and delivery address, it is written to the delivery address tags THInv…
Show Credit Memo Show customer/supplier credit memo before proceeding. Useful for better communicationPro
Clear Transaction Will Clear Transaction lines before proceeding More about Clear transaction copy formula​
 After Selecting Stock on lines  
Explode Stock BOM Will expand BOM's and formula for price calculation. Pro More about Expand BOM on Order​
And Indent Description Indent description when adding lines with 3 spaces
Add a blank line after Add blank line on transaction
Show free stock in search When searching for stock, show free stock level Pro
Show In Stock Qty in search When searching for stock, show Stock Qty level Pro
Show Qty on order in search When searching for stock, show Qty on Order Pro

The above settings are stored in each sheet, in the comment in cell A1.

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 IO

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

TLStock^

Text 30 XML O

Stock Code
    In Cell Searching values in table ProdCodeNoDesc

TLDesc

Text 60 EOF Req XML IO

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
How Descriptions are Imported

TLQty^

Double XML O

Quantity. PRO - If switch `Iignore 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.

TLQtyAlloc^

Text 20 XML O

Qty to Allocate on Sales Order. Overrides 'Allocate Available Stock' switch in system settings, if not blank. If not enough Free stock then Allocates max amount allowed.
Sage 50 Quick Pad
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLUnitPrice^

Currency XML IO

Unit Price for each item. If cell is blank then PostTrans will lookup customer price on change of Qty, or adding a product. System switches can control if this value is used and how it is updated. 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.

TLFulfilment^

Integer XML SO

Fulfilment from stock by default. SOP Only
        In Cell Searching values:Fulfilment from
                  0, Stock
                  1, Supplier via Stock
                  2, Supplier Direct

TLDiscPerc^

Double XML O

Sales:Additional Discount Percent to be applied to TLUnitPrice. Note TLUnitPrice has already had any system discount applied on account record.
Discount calculation
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

Other:

TLLoc^

Text 20 XML O

Location. Ignored if warehouses are off
    In Cell Searching values in table Loc

Nom Analysis:

TLNomCode^

Text 16 XML IO

Nominal Code, if not specified then uses default
How Nom/CC/Dep are specified
    In Cell Searching values in table Nom

TLCC^

Text 3 IO

Default CC. Only use for setting with formula
How Nom/CC/Dep are specified
    In Cell Searching values in table CC

TLDep^

Text 3 IO

Department, if not specified then uses default. Make sure the format of the cell is “General” else search may not work properly
How Nom/CC/Dep are specified
    In Cell Searching values in table Dep

UOM:

TLQtyMul^

Double XML O

Qty Multiplier set by unit of meaure
    In Cell Searching values in table 0, Stock, 1, Supplier via Stock, 2, Supplier Direct

TLUOM^

Text 20 XML O

Unit of Measure for Stock Item. SOP Only
    In Cell Searching values in table 0, Stock, 1, Supplier via Stock, 2, Supplier Direct

Other Values:

TLTotNet

Currency Read IO

Total Net Price, TLQty * TLUnitPrice calculated with discount. Allows formula to total Transaction. PRO - If multi-currency is ON will be in account currency.

TLTotCost

Currency Read XML O

Total Cost Price, TLQty * PrCostPrice calculated with discount. Allows formula to cost Transaction to get Margin. PRO - If multi-currency is ON will be in account currency.

TLCostPrice

Currency Read XML O

Stock Cost Price. Standard, Average Cost. If Serial Batch/FIFO then returns Average.

TLStdPrice^

Currency Read Pro O

Standard Selling price with currency conversion
Sage 50 Quick Pad
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLTotIncluOfTax

Currency Pro XML IO

Line Total inclusive of Tax (Only SO,SI,SC,PI,PC). 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.
    Sage V17 onwards

Line Type:

TLAddChType^

Text 20 XML O

Additional Charge Type, if not specified then set to "Carriage"
    In Cell Searching values in table OPAddCharge

TLLineType^

Text 2 XML O

Line Type. This controls the Line type, which defaults to Standard item if stock, else FreeText Default specified in system settings, if line has a value, but no TLStock. If line has no value, then the TLDesc will be imported as INV/Order Comment. If Transaction is a "Service" transaction then line type is FreeTextConfirmationNotRequired
        In Cell Searching values:Line Type
                  ST, Standard
                  FG, FreeTextWithGoods
                  FS, FreeTextAsService
                  FN, FreeTextConfirmationNotRequired
                  AC, AdditionalChargeLine
                  CI, Comment INV/ORD
                  CO, Comment Picking

TLDispatchListComment

Text 160 XML O

Comment on Dispatch

TLPickListComment

Text 160 XML O

Comment on Picking List

Tax:

TLTaxManTotAmount

Currency XML I

Tax Amount, for line, if calculated manually. Use with Caution as you are responsible for calculating Tax correctly.
more about VAT

TLTaxCode^

Text 2 XML IO

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

TLTaxAmount

Currency Read IO

Tax Amount for Display calculated during manual input of Invoice/Order
more about VAT

TLTaxRate

Double Read IO

Tax Rate for Display

Serial Batch:

TLBatSerial^

Text 30 Pro XML O

Batch Ref or for Serial then a comma seperated list or list with each on new line. SO only
    In Cell Searching values in table TranTLBatSerial

TLSellByDate

Date Pro XML O

Sell by date of Batch

TLUseByDate

Date Pro XML O

Use by date of Batch

TLBatSerRef

Text 30 Pro XML O

Additional ref for Batch

After Stock Selection:

StQtyFree

Double Read Rel O

Product Qty free, updated when TLLoc/TLStock code entered

StQtyInStock

Double Read Rel O

Product Qty In stock, updated when stock code entered

StQtyAllocated

Double Read Rel O

Product Qty Allocated, updated when stock code entered

StMax

Double Read Rel O

Max Stock Qty

StMin

Double Read Rel O

Min Stock Qty

Stock Supplier Record:

StSuppCode

Text 20 Read O

1

StSuppCodeName

Text 30 Read Rel O

Supp Name

StSuppLastPriceDate

Date Read Rel O

Supp Last Price Date

StSuppListPrice

Currency Read Rel O

Supp List Price

StSuppBuyPrice

Currency Read Rel O

Supp Buy Price

StSuppLeadTimeUnit

Text 20 Read Rel O

Supp Lead Time

StSuppLeadTime

Double Read Rel O

Supp Lead Time

StSuppQtyOnOrd

Double Read Rel O

Supp Qty on Order

StMinOrdQty

Double Read Rel O

Supp Min Order Qty

StSuppPartNo

Text 16 Read Rel O

Supplier Part Number

StQtyReOrder

Double Read Rel O

Supp Re Order Qty

StSuppLastOrdDate

Date Read Rel O

Supp Last Order Date

Important Header Fields:

THTransType^

Text 3 Head Chg XML IO

Transaction Type. Make sure the format of the cell is “General” else search may not work properly. Must be specified on each line. Service orders allow simple free text Orders to be imported with not dispatch confirmation needed.
        In Cell Searching values:Transaction Type
                  SQ, Sales Quote
                  SP, Proforma
                  SO, Sales Order
                  SSO, Sales Service Order
                  SI, Sales Invoice
                  SIM, Sales Invoice Module
                  SIA, Sales Invoice And Adj OUT
                  SSI, Sales Service Invoice
                  SC, Sales Credit
                  SCM, Sales Credit Module
                  SOB, Sales Opening Bal
                  SOC, Sales Opening Bal Credit
                  SR, Sales Receipt
                  PO, Purchase Order
                  PSO, Purchase Service Order
                  PI, Purchase Invoice
                  PIA, Purchase Invoice and Adj OUT
                  PC, Purchase Credit
                  POB, Purchase Open Balance
                  POC, Purchase Open Balance

THManDocRef

Text 30 Head Chg XML O

Manual ref if manual Reference in sage 200 is ON

THAccCode^

Text 8 Head Chg XML IO

Transaction Account Code. Customer or Supplier depending on Transaction Type.
    In Cell Searching values in table THAccCode

THTransDate

Date Head Chg XML IO

Transaction Date

THDueDelDate

Date Head XML IO

Delivery Date on Order, Expire Date on Quote.

THPromDelDate

Date Head XML IO

Promised Delivery date on SO,SQ, SP

THRoundToGross

Double Head XML I

When importing Financial Invoice/Credits (SI/SC/PI/PC). Round highest VAT figure to make Transaction this Gross value, with 5p limit

Header Text:

THCustRef

Text 30 Head Chg XML IO

Suppliers/Customer`s Order Number, 10 chars long on PI/SI

THCustRef2

Text 30 Head Chg XML I

Customer`s Order Number, 10 chars long on PI/SI

THDefaultLoc^

Text 20 Head XML O

Default Location for all entered lines. Ignored if warehouses are off
    In Cell Searching values in table Loc

THOrderPriority

Text 1 Head XML O

Order Priority A-Z

CASH Invoice Address:

THInvName

Text 60 Head XML O

Invoice name if Cash account

THInvAdd1

Text 60 Head XML O

Invoice Add1 if Cash account

THInvAdd2

Text 60 Head XML O

Invoice Add2 if Cash account

THInvAdd3

Text 60 Head XML O

Invoice Add3 if Cash account

THInvAdd4

Text 60 Head XML O

Invoice Add4 if Cash account

THInvPostcode^

Text 60 Head XML O

Invoice Postcode if Cash account
More about Postcode Address Lookup
    In Cell Searching values in table SimplyPostcode

CASH Segmented Address:

THInvCity

Text 60 Head XML O

Invoice City if Cash account

THInvCounty

Text 60 Head XML O

Invoice County if Cash account

THInvCountry^

Text 60 Head Read XML O

Invoice Country if Cash account
    In Cell Searching values in table CountryCode

Currency:

THCurCode

Text 3 Head Read IO

Currency Code, PRO need to post in currency

THCurCodeName

Text 25 Head Read

Currency Code, PRO need to post in currency

THCurSymbol

Text 3 Head Read IO

Currency Symbol

THCurExch

Double Head XML IO

Currency Exchange Rate. Normally uses System Exchange Rate, unless option changed in PostTrans system settins

THSetDisc

Double Head Pro XML IO

Settlement Disc

THSetDays

Integer Head Pro XML IO

Settlement Days

Delivery Address:

THDelContact

Text 60 Head XML O

Delivery Address Contact Name. Double click to change delivery address

THPostalName

Text 60 Head XML O

Delivery PostalName

THDelAdd1

Text 60 Head XML O

Delivery Address Line 1. Double click to change delivery address. On PO is the direct delivery address

THDelAdd2

Text 60 Head XML O

Delivery Address Line 2. Double click to change delivery address

THDelAdd3

Text 60 Head XML O

Delivery Address Line 3. Double click to change delivery address. On PO is the direct delivery address

THDelAdd4

Text 60 Head XML O

Delivery Address Line 4. Double click to change delivery address

THDelPostcode^

Text 60 Head XML O

Delivery Postcode. PRO -If Postcode Search is enabled in ‘Setup >> Lookup >> Address Lookup’ then enter Space or Space + Postcode to search for an address.
More about Postcode Address Lookup
    In Cell Searching values in table SimplyPostcode

THDelEmail

Text 255 Head XML O

Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order.
emailing Sage 200 transaction

THDelTelNo

Text 60 Head XML O

Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order.

THDelFaxNo

Text 60 Head XML O

Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order.

Delivery Address Segmented Address:

THDelCountry^

Text 60 Head XML O

Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order.
    In Cell Searching values in table CountryCode

THDelCity

Text 60 Head XML O

Delivery Address City. Double click to change delivery address

THDelCounty

Text 60 Head XML O

Delivery Address County. Double click to change delivery address

Project (Pro):

THProject^

Text 20 Head Pro IO

Default Project code for Transactions, can be overidden using TLProject
    In Cell Searching values in table ProjectCode

Project on Line:

TLProject^

Text 20 Pro XML IO

Project code, overides THProject Can also be Project"/"Phase to book to specific Project Item
    In Cell Searching values in table ProjectCode

TLPhaseFilter^

Text 30 XML

Phase Filter. If project has duplicate Project items in different phases, this can direct to correct Phase
    Sage V21 onwards
    In Cell Searching values in table PROJECTPHASE

TLCostCode^

Text 20 Pro XML IO

Cost Code, overides THCostCode
    In Cell Searching values in table ProjectCostCode

Header Discount:

Header Payment:

THPayAmt

Double Head XML IO

Amount Prepaid. If used in lines then Sum is posted. Option in `Setup >> Switches’, can stop posting if UNDER or OVER paid
Sage 50 Payment with Transaction

THPayRef

Text 30 Head XML IO

Payment Reference
more about with payment

THBankAccList

Text HeadOnly XML

When posting Payments a List of Currency Code, Bank Account to use. E.g ‘GBP,1,EUR,2,USD,3’

THPayMethod^

Text 30 Head XML IO

Payment Method
more about with payment
    In Cell Searching values in table PaymentMethod

Header:Add Message:

THMessTop

Text HeadOnly IO

Add message at top of transaction using Comment on IN/ORD
Creating Message Top/Bottom

THMessBot1

Text HeadOnly IO

Add message at bottom of transaction using Comment on IN/ORD
Message Top/Bottom

THMessBot2

Text HeadOnly IO

Add message at bottom of transaction using Comment on IN/ORD
Message Top/Bottom

Additional Charge:

THAddChgTxt1

Text 20 HeadOnly O

Additional Charge Text 1

THAddChgType1^

Text 20 HeadOnly O

Additional Charge Type 1 (Placed above THMessBot1/THMessBot2)
    In Cell Searching values in table OPAddCharge1

THAddChg1

Text 20 HeadOnly O

Additional Charge 1

THAddChgTxt2

Text 20 HeadOnly O

Additional Charge Text 2

THAddChgType2^

Text 20 HeadOnly O

Additional Charge Type 2 (Placed above THMessBot1/THMessBot2)
    In Cell Searching values in table OPAddCharge2

THAddChg2

Text 20 HeadOnly O

Additional Charge 2

THAddChgTxt3

Text 20 HeadOnly O

Additional Charge Text 3

THAddChgType3^

Text 20 HeadOnly O

Additional Charge Type 3 (Placed below THMessBot1/THMessBot2)
    In Cell Searching values in table OPAddCharge3

THAddChg3

Text 20 HeadOnly O

Additional Charge 3

THAddChgTxt4

Text 20 HeadOnly O

Additional Charge Text 4

THAddChgType4^

Text 20 HeadOnly O

Additional Charge Type 4 (Placed below THMessBot1/THMessBot2)
    In Cell Searching values in table OPAddCharge4

THAddChg4

Text 20 HeadOnly O

Additional Charge 4

TLSkipLine

Yes/No IO

If YES then skip this line

Header Printing:

THPrintEmail

Text Head IO

Email address to send to. Multiples and be expressed using “,”. If put on lines will then will email doc to address if print dialog is off
Printing after posting a Sage 200 Order

THPrintEmailCC

Text HeadOnly IO

Email address to CC to. Multiples and be expressed using “,”.
Printing after posting a Sage 200 Order

THPrintSubject

Text HeadOnly IO

Subject of email, which overrides that set in System >> Printing.
Printing after posting a Sage 200 Order

THPrintBody

Text HeadOnly IO

Body of email, which overrides that set in System >> Printing.
Printing after posting a Sage 200 Order

THPrintAttachment

Text HeadOnly IO

Add an attachment. Full Path. Multiples and be expressed using “,”.
Printing after posting a Sage 200 Order

THPrintLayout^

Text HeadOnly IO

Sage 200 layout file for printing. E.g. SOP\\Picking List.report Enter SPACE to search display list of form layouts
Printing after posting a Sage 200 Order
    In Cell Searching values in table FormLayout

THPrintNoCopies

Integer Head IO

Print x copies to printer. 0 for no print. If put on lines will then will print doc if print dialog is off
Printing after posting a Sage 200 Order

Header:Before Posting:

THBefSWCompany^

Text 6 Head Chg Pro IO

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 IO

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 IO

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

THAftWriteAlocRef

Text 60 HeadOnly Read IO

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

THAftWriteRef

Text 60 HeadOnly Read IO

After posting write back transaction reference to this cell

THAftWritePayRef

Text 60 HeadOnly Read IO

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

THAftProcessSheet

Text 60 HeadOnly Pro IO

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 Sage Companies

THAftSwitchSheet

Text 60 HeadOnly Read Pro IO

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

Header - After Save:

TLForceNewTrans

Yes/NoTF HeadOnly IO

If YES then force a new transaction

THAftSavePATH

Text 60 HeadOnly Read Pro IO

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 Pro IO

See THAftSavePATH
Saving template after posting

THAftSaveFilename

Text 60 HeadOnly Read Pro IO

See THAftSavePATH. Example ‘ABAP01’ will saveas ABAP01_0001.xls.
Saving template after posting

Import File:

THImportFullName

Text 500 HeadOnly IO

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 IO

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

After Account Selection:

AcName

Text 60 Read Rel IO

Account Name. PRO - Double clicking on this cell will drill down to Ledger.

AcBalance^

Currency Read Rel IO

Balance. PRO - Double clicking on this cell will drill down to Ledger.
Double click to view ledger
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

AcOut1MDays^

Currency Read Rel IO

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

AcAvCredit^

Currency Read Rel IO

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

AcCreditLimit^

Currency Read Rel IO

Credit Limit. PRO - Double clicking on this cell will drill down to Ledger.
Double click to view ledger
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

AcOutDaysTxt^

Text Read Rel IO

Outstanding 30,60,90,120 day break down in text form.
PRO Double clicking on this cell will drill down to Ledger.
Double click to view ledger
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

Registered Address:

AcAdd1

Text 60 Read Rel IO

Account Address Line 1

AcAdd2

Text 60 Read Rel IO

Account Address Line 2

AcAdd3

Text 60 Read Rel IO

Account Address Line 3

AcAdd4

Text 60 Read Rel IO

Account Address Line 4

AcAddCity

Text 60 Read Rel IO

Account Address City

AcAddCounty

Text 60 Read Rel IO

Account Address County

AcAddPostcode^

Text 60 Read Rel IO

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

AcAddCountry

Text 60 Read Rel IO

Account Country.

AcCountryCode^

Text 2 Read Rel IO

Country Code
    In Cell Searching values in table CountryCode

AcTaxRegCode

Text 20 Read Rel IO

VAT Registration Number

Contact Information:

AcContactName

Text 30 Read Rel IO

Contact Name

AcDefPhone

Text 30 Read Rel IO

Telephone Number

AcDefFax

Text 30 Read Rel IO

Fax Number

AcDefMob

Text 30 Read Rel IO

Mobile Number

AcWWW

Text 255 Read Rel IO

WWW Address

Credit Dates:

AcApplication^

Date Read Rel IO

Application Date
    In Cell Searching values in table CreditPosition

AcNextRev

Date Read Rel IO

Next Review

AcOpened

Date Read Rel IO

Date Opened

AcAppRecieved

Date Read Rel IO

Application Received

AcLastRev

Date Read Rel IO

Last Review Date

AcDiscGroup

Text 20 Read Rel IO

Discount Group

AcCreditPosition^

Text 20 Read Rel IO

Credit Position
        In Cell Searching values:Discount Type
                  0, None
                  1, Invoice Value
                  2, Band A
                  3, Band B
                  4, Band C
                  5, Band D
                  6, Band E

AcPriceBand^

Text 20 Read Rel IO

Price Band

Pricing:

AcInvoiceDisc

Double Read Rel IO

Invoice Discount

AcLineDisc

Double Read Rel IO

Line Discount

AcOrderValueDisc

Text 20 Read Rel IO

Order Value Discount

AcTaxCode^

Integer Read Rel IO

Default Tax Code
    In Cell Searching values in table TaxCode

Credit Control - Terms:

AcSettDays

Integer Read Rel IO

Settlement Due Days

AcSettDiscount

Double Read Rel IO

Settlement Discount Rate

AcPaymentDueDays

Integer Read Rel IO

Payment Due Days

AcAvgPayDays

Integer Read Rel IO

Average Time To Pay

AcTermsTxt

Text 30 Read Rel IO

Terms

AcCreditRef

Text 60 Read Rel IO

Credit Bureau Reference

AcBureau^

Text 30 Read Rel IO

Credit Bureau Used
    In Cell Searching values in table BureauCode

AcTermsAgreed

Yes/NoTF Read Rel IO

Terms Agreed Flag

AcOnHold

Yes/NoTF Read Rel IO

Account On Hold

Contact Delivery Address:

AcDcontAdd1

Text 60 Read Rel IO

Default Contact Address Line 1

AcDcontAdd2

Text 60 Read Rel IO

Default Contact Address Line 2

AcDcontAdd3

Text 60 Read Rel IO

Default Contact Address Line 3

AcDcontAdd4

Text 60 Read Rel IO

Default Contact Address Line 4

AcDcontCity

Text 60 Read Rel IO

Default Contact City

AcDcontCounty

Text 60 Read Rel IO

Default Contact County

AcDcontPostcode

Text 60 Read Rel IO

Default Contact Postcode
Postcode Address Lookup

AcDcontCountry

Text 60 Read Rel IO

Default Contact Country

Header Analysis:

THAnal1

Text 60 Head XML IO

Header Analysis 1
    Sage V2015 onwards

THAnal2

Text 60 Head XML IO

Header Analysis 2
    Sage V2015 onwards

THAnal3

Text 60 Head XML IO

Header Analysis 3
    Sage V2015 onwards

THAnal4

Text 60 Head XML IO

Header Analysis 4
    Sage V2015 onwards

THAnal5

Text 60 Head XML IO

Header Analysis 5
    Sage V2015 onwards

THAnal6

Text 60 Head XML IO

Header Analysis 6
    Sage V2015 onwards

THAnal7

Text 60 Head XML IO

Header Analysis 7
    Sage V2015 onwards

THAnal8

Text 60 Head XML IO

Header Analysis 8
    Sage V2015 onwards

THAnal9

Text 60 Head XML IO

Header Analysis 9
    Sage V2015 onwards

THAnal10

Text 60 Head XML IO

Header Analysis 10
    Sage V2015 onwards

THAnal11

Text 60 Head XML IO

Header Analysis 11
    Sage V2015 onwards

THAnal12

Text 60 Head XML IO

Header Analysis 12
    Sage V2015 onwards

THAnal13

Text 60 Head XML IO

Header Analysis 13
    Sage V2015 onwards

THAnal14

Text 60 Head XML IO

Header Analysis 14
    Sage V2015 onwards

THAnal15

Text 60 Head XML IO

Header Analysis 15
    Sage V2015 onwards

THAnal16

Text 60 Head XML IO

Header Analysis 16
    Sage V2015 onwards

THAnal17

Text 60 Head XML IO

Header Analysis 17
    Sage V2015 onwards

THAnal18

Text 60 Head XML IO

Header Analysis 18
    Sage V2015 onwards

THAnal19

Text 60 Head XML IO

Header Analysis 19
    Sage V2015 onwards

THAnal20

Text 60 Head XML IO

Header Analysis 20
    Sage V2015 onwards

Spare:

THSpareTxt1

Text 60 Head XML IO

SpareTxt1

THSpareTxt2

Text 60 Head XML IO

SpareTxt2

THSpareTxt3

Text 60 Head XML IO

SpareTxt3

THSpareBit1

Yes/NoTF Head XML IO

SpareBit1

THSpareBit2

Yes/NoTF Head XML IO

SpareBit2

THSpareBit3

Yes/NoTF Head XML IO

SpareBit3

THSpareNo1

Integer Head XML IO

SpareNo1

THSpareNo2

Integer Head XML IO

SpareNo2

THSpareNo3

Integer Head XML IO

SpareNo3

THSpareDate1

Date Head XML IO

SpareDate1

THSpareDate2

Date Head XML IO

SpareDate2

THSpareDate3

Date Head XML IO

SpareDate3

Line Analysis:

TLAnal1

Text 60 XML IO

Header Analysis 1
    Sage V2015 onwards

TLAnal2

Text 60 XML IO

Header Analysis 2
    Sage V2015 onwards

TLAnal3

Text 60 XML IO

Header Analysis 3
    Sage V2015 onwards

TLAnal4

Text 60 XML IO

Header Analysis 4
    Sage V2015 onwards

TLAnal5

Text 60 XML IO

Header Analysis 5
    Sage V2015 onwards

TLAnal6

Text 60 XML IO

Header Analysis 6
    Sage V2015 onwards

TLAnal7

Text 60 XML IO

Header Analysis 7
    Sage V2015 onwards

TLAnal8

Text 60 XML IO

Header Analysis 8
    Sage V2015 onwards

TLAnal9

Text 60 XML IO

Header Analysis 9
    Sage V2015 onwards

TLAnal10

Text 60 XML IO

Header Analysis 10
    Sage V2015 onwards

TLAnal11

Text 60 XML IO

Header Analysis 11
    Sage V2015 onwards

TLAnal12

Text 60 XML IO

Header Analysis 12
    Sage V2015 onwards

TLAnal13

Text 60 XML IO

Header Analysis 13
    Sage V2015 onwards

TLAnal14

Text 60 XML IO

Header Analysis 14
    Sage V2015 onwards

TLAnal15

Text 60 XML IO

Header Analysis 15
    Sage V2015 onwards

TLAnal16

Text 60 XML IO

Header Analysis 16
    Sage V2015 onwards

TLAnal17

Text 60 XML IO

Header Analysis 17
    Sage V2015 onwards

TLAnal18

Text 60 XML IO

Header Analysis 18
    Sage V2015 onwards

TLAnal19

Text 60 XML IO

Header Analysis 19
    Sage V2015 onwards

TLAnal20

Text 60 XML IO

Header Analysis 20
    Sage V2015 onwards

Line Spare:

TLSpareTxt1

Text 60 XML IO

SpareTxt1

TLSpareTxt2

Text 60 XML IO

SpareTxt2

TLSpareTxt3

Text 60 XML IO

SpareTxt3

TLSpareBit1

Yes/NoTF XML IO

SpareBit1

TLSpareBit2

Yes/NoTF XML IO

SpareBit2

TLSpareBit3

Yes/NoTF XML IO

SpareBit3

TLSpareNo1

Integer XML IO

SpareNo1

TLSpareNo2

Integer XML IO

SpareNo2

TLSpareNo3

Integer XML IO

SpareNo3

TLSpareDate1

Date XML IO

SpareDate1

TLSpareDate2

Date XML IO

SpareDate2

TLSpareDate3

Date XML IO

SpareDate3

Ac Anal:

AcAnal1

Text 60 IO

Account Anal1
    Sage V2015 onwards

AcAnal2

Text 60 IO

Account Anal2
    Sage V2015 onwards

AcAnal3

Text 60 IO

Account Anal3
    Sage V2015 onwards

AcAnal4

Text 60 IO

Account Anal4
    Sage V2015 onwards

AcAnal5

Text 60 IO

Account Anal5
    Sage V2015 onwards

AcAnal6

Text 60 IO

Account Anal6
    Sage V2015 onwards

AcAnal7

Text 60 IO

Account Anal7
    Sage V2015 onwards

AcAnal8

Text 60 IO

Account Anal8
    Sage V2015 onwards

AcAnal9

Text 60 IO

Account Anal9
    Sage V2015 onwards

AcAnal10

Text 60 IO

Account Anal10
    Sage V2015 onwards

AcAnal11

Text 60 IO

Account Anal11
    Sage V2015 onwards

AcAnal12

Text 60 IO

Account Anal12
    Sage V2015 onwards

AcAnal13

Text 60 IO

Account Anal13
    Sage V2015 onwards

AcAnal14

Text 60 IO

Account Anal14
    Sage V2015 onwards

AcAnal15

Text 60 IO

Account Anal15
    Sage V2015 onwards

AcAnal16

Text 60 IO

Account Anal16
    Sage V2015 onwards

AcAnal17

Text 60 IO

Account Anal17
    Sage V2015 onwards

AcAnal18

Text 60 IO

Account Anal18
    Sage V2015 onwards

AcAnal19

Text 60 IO

Account Anal19
    Sage V2015 onwards

AcAnal20

Text 60 IO

Account Anal20
    Sage V2015 onwards

Post Extra Stock Line 1:

THExLineStock1^

Text 30 Head O

Stock code. Not used on Service Type Transactions. For Extra Line
    Sage V17 onwards
    In Cell Searching values in table ProdCodeNoDesc

TLExLineDesc1

Text 60 O

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
How Descriptions are Imported
    Sage V17 onwards

TLExLineQty1^

Double O

Quantity For Extra Line
    Sage V17 onwards
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLExLineNet1^

Currency O

Unit Price for each item For Extra Line
Unit Price
    Sage V17 onwards
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLExLineNomCode1^

Text 8 O

Nominal Code, if not specified then uses default from stock/account. For Extra Line
    Sage V17 onwards
    In Cell Searching values in table Nom

TLExLineTaxCode1^

Integer O

Tax Code. Will fill in default if blank. Make sure the format of the cell is “General” else search may not work properly
more about VAT
    Sage V17 onwards
    In Cell Searching values in table Tax

TLExLineTaxManTot1

Currency O

Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line
more about VAT
    Sage V17 onwards

TLExTotIncluOfTax1

Currency Pro O

Line Total inclusive of Tax. Best choice when importing data as reduces rounding problems For Extra Line
more about VAT
    Sage V17 onwards

Post Extra Add Charge Line 1:

THExAddChrCode1^

Text 30 Head O

Additional Charge code. Not used on Service Type Transactions. For Extra Line
    Sage V17 onwards
    In Cell Searching values in table OPAddCharge

TLExAddChrDesc1

Text 60 O

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
How Descriptions are Imported
    Sage V17 onwards

TLExAddChrNet1^

Currency O

Unit Price for each item For Extra Line
Unit Price
    Sage V17 onwards
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLExAddChrNomCode1^

Text 8 O

Nominal Code, if not specified then uses default from stock/account. For Extra Line
    Sage V17 onwards
    In Cell Searching values in table Nom

TLExAddChrTaxCode1^

Integer O

Tax Code. Will fill in default if blank. Make sure the format of the cell is “General” else search may not work properly
more about VAT
    Sage V17 onwards
    In Cell Searching values in table Tax

TLExAddChrTaxManTot1

Currency O

Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line
more about VAT
    Sage V17 onwards

TLExAddChrTotIncluOfTax1

Currency O

Line Total inclusive of Tax. Best choice when importing data as reduces rounding problems If main Product has Tax then uses the Tax Rate of Addition charge record
more about VAT
    Sage V17 onwards

Post Extra Add Charge Line 2:

THExAddChrCode2^

Text 30 Head O

Additional Charge code. Not used on Service Type Transactions. For Extra Line
    Sage V17 onwards
    In Cell Searching values in table OPAddCharge

TLExAddChrDesc2

Text 60 O

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
How Descriptions are Imported
    Sage V17 onwards

TLExAddChrNet2^

Currency O

Unit Price for each item For Extra Line
Unit Price
    Sage V17 onwards
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLExAddChrNomCode2^

Text 8 O

Nominal Code, if not specified then uses default from stock. For Extra Line
    Sage V17 onwards
    In Cell Searching values in table Nom

TLExAddChrTaxCode2^

Integer O

Tax Code. Will fill in default if blank. Make sure the format of the cell is “General” else search may not work properly
more about VAT
    Sage V17 onwards
    In Cell Searching values in table Tax

TLExAddChrTaxManTot2

Currency O

Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line
more about VAT
    Sage V17 onwards

TLExAddChrTotIncluOfTax2

Currency O

Line Total inclusive of Tax. Best choice when importing data as reduces rounding problems If main Product has Tax then uses the Tax Rate of Addition charge record
more about VAT
    Sage V17 onwards

Post Extra Add Charge Line 3:

THExAddChrCode3^

Text 30 Head O

Additional Charge code. Not used on Service Type Transactions. For Extra Line
    Sage V17 onwards
    In Cell Searching values in table OPAddCharge

TLExAddChrDesc3

Text 60 O

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
How Descriptions are Imported
    Sage V17 onwards

TLExAddChrNet3^

Currency O

Unit Price for each item For Extra Line
Unit Price
    Sage V17 onwards
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLExAddChrNomCode3^

Text 8 O

Nominal Code, if not specified then uses default from stock. For Extra Line
    Sage V17 onwards
    In Cell Searching values in table Nom

TLExAddChrTaxCode3^

Integer O

Tax Code. Will fill in default if blank. Make sure the format of the cell is “General” else search may not work properly
more about VAT
    Sage V17 onwards
    In Cell Searching values in table Tax

TLExAddChrTaxManTot3

Currency O

Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line
more about VAT
    Sage V17 onwards

TLExAddChrTotIncluOfTax3

Currency O

Line Total inclusive of Tax. Best choice when importing data as reduces rounding problems If main Product has Tax then uses the Tax Rate of Addition charge record
more about VAT
    Sage V17 onwards

Post Extra Add Charge Line 4:

THExAddChrCode4^

Text 30 Head O

Additional Charge code. Not used on Service Type Transactions. For Extra Line
    Sage V17 onwards
    In Cell Searching values in table OPAddCharge

TLExAddChrDesc4

Text 60 O

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
How Descriptions are Imported
    Sage V17 onwards

TLExAddChrNet4^

Currency O

Unit Price for each item For Extra Line
Unit Price
    Sage V17 onwards
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLExAddChrNomCode4^

Text 8 O

Nominal Code, if not specified then uses default from stock. For Extra Line
    Sage V17 onwards
    In Cell Searching values in table Nom

TLExAddChrTaxCode4^

Integer O

Tax Code. Will fill in default if blank. Make sure the format of the cell is “General” else search may not work properly
more about VAT
    Sage V17 onwards
    In Cell Searching values in table Tax

TLExAddChrTaxManTot4

Currency O

Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line
more about VAT
    Sage V17 onwards

TLExAddChrTotIncluOfTax4

Currency O

Line Total inclusive of Tax. Best choice when importing data as reduces rounding problems If main Product has Tax then uses the Tax Rate of Addition charge record
more about VAT
    Sage V17 onwards

ObjectStore Mappings Lines:

ObjectStore Mappings:

THUser1

Text 30 Head XML IO

10 user defined fields which can be mapped to Sage ObjectStore Builder customisations
How to map Sage ObjectStore Builder customisations

THUser2

Text 30 Head XML IO

User2

THUser3

Text 30 Head XML IO

User3

THUser4

Text 30 Head XML IO

User4

THUser5

Text 30 Head XML IO

User5

THUser6

Text 30 Head XML IO

User6

THUser7

Text 30 Head XML IO

User7

THUser8

Text 30 Head XML IO

User8

THUser9

Text 30 Head XML IO

User9

THUser10

Text 30 Head XML IO

User10

TLUser1

Text 30 XML IO

10 user defined fields which can be mapped to Sage ObjectStore Builder customisations for SO/PO Standard Lines and SI/PI lines
How to map Sage ObjectStore Builder customisations

TLUser2

Text 30 XML IO

User2

TLUser3

Text 30 XML IO

User3

TLUser4

Text 30 XML IO

User4

TLUser5

Text 30 XML IO

User5

TLUser6

Text 30 XML IO

User6

TLUser7

Text 30 XML IO

User7

TLUser8

Text 30 XML IO

User8

TLUser9

Text 30 XML IO

User9

TLUser10

Text 30 XML IO

User10

TLUser11

Text 30 XML IO

10 user defined fields which can be mapped to Sage ObjectStore Builder customisations for SO/PO Free Text Lines
How to map Sage ObjectStore Builder customisations

TLUser12

Text 30 XML IO

TLUser12

TLUser13

Text 30 XML IO

TLUser13
How to map Sage ObjectStore Builder customisations

TLUser14

Text 30 XML IO

TLUser14
How to map Sage ObjectStore Builder customisations

TLUser15

Text 30 XML IO

TLUser15

TLUser16

Text 30 XML IO

TLUser16

TLUser17

Text 30 XML IO

TLUser17

TLUser18

Text 30 XML IO

TLUser18

TLUser19

Text 30 XML IO

TLUser19
How to map Sage ObjectStore Builder customisations

TLUser20

Text 30 XML IO

TLUser20

TLUser21

Text 30 XML IO

10 user defined fields which can be mapped to Sage ObjectStore Builder customisations for SO/PO Aditional Charge
How to map Sage ObjectStore Builder customisations

TLUser22

Text 30 XML IO

TLUser22

TLUser23

Text 30 XML IO

TLUser23

TLUser24

Text 30 XML IO

TLUser24
How to map Sage ObjectStore Builder customisations

TLUser25

Text 30 XML IO

TLUser25

TLUser26

Text 30 XML IO

TLUser26

TLUser27

Text 30 XML IO

TLUser27

TLUser28

Text 30 XML IO

TLUser28

TLUser29

Text 30 XML IO

TLUser29
How to map Sage ObjectStore Builder customisations

TLUser30

Text 30 XML IO

TLUser30

TLUser31

Text 30 XML IO

10 user defined fields which can be mapped to Sage ObjectStore Builder customisations for SO/PO Comment Line
How to map Sage ObjectStore Builder customisations

TLUser32

Text 30 XML IO

TLUser32

TLUser33

Text 30 XML IO

TLUser33

TLUser34

Text 30 XML IO

TLUser34
How to map Sage ObjectStore Builder customisations

TLUser35

Text 30 XML IO

TLUser35
How to map Sage ObjectStore Builder customisations

TLUser36

Text 30 XML IO

TLUser36
How to map Sage ObjectStore Builder customisations

TLUser37

Text 30 XML IO

TLUser37

TLUser38

Text 30 XML IO

TLUser38

TLUser39

Text 30 XML IO

TLUser39

TLUser40

Text 30 XML IO

TLUser40


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

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.

IO  This type of import shares common tags for Orders and Invoices

O  Tags for Orders only

I  Tags for Invoices only

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.

Transaction Data - Sales:

 

Sales Order

Sheet:'Sales Order'
Post a Sales Order

 

Sales Order with Serial and Batch allocation

Sheet:'Sales Order'
Post a Sales Order with Serial and Batch allocation

 

Sales Order Pad Pro

Sheet:'Order Pad'
Post a Sales Order Pad for Fast order entry
   Help article for Sales Order Pad Pro

 

Purchase Order

Sheet:'PO'
Post a Purchase Order

 

Sales Order + Info

Sheet:'Sales Order Info'
Post a Sales Order showing more customer information

 

PO with Project costing and Phase Filter

Sheet:'PO Phase Filter'
Post a Purchase Order

 

Purchase Order with Authorisation

Sheet:'PO Auth'
Post a Purchase Order with Authorisation

 

Remote Purchase Order

Sheet:'PO Auth'
Post a Purchase Order filled in by remote subsidiary
   Help article for Remote Purchase Order

 

Sales Order Additional Chrage

Sheet:'Sales Order Charge'
Post a Sales Order with Additional Chrage expressed on header

 

Sales Order with Payment Pro

Sheet:'Sales Order Pay'
Post a Sales Order with Payment

 

Sales Order in Currency Pro

Sheet:'SO in Currency'
Post a Sales Order in Currency

 

Sales Invoice to Accounts Ledger with Gross Rounding

Sheet:'Sales Invoice Round'
Post a Sales Invoice to Accounts Ledger with RoundTo a value

 

Sales Invoice (Invoice Ledger)

Sheet:'Sales Invoice INV LEDGER'
Post a Sales Invoice to invoice ledger

 

Sales Invoice to Accounts Ledger

Sheet:'Sales Invoice'
Post a Sales Invoice to Accounts Ledger

 

Sales Credit Note

Sheet:'Sales Credit Note'
Post a Sales Credit Note

 

Sales Opening Balance

Sheet:'Sales Opening Balance'
Post a Sales Opening Balance

 

Sales Service Order

Sheet:'Sales Service Order'
Post a Sales Service Order which alows long text and bypasses dispatch routine

 

File Import:Multiple Types from multiple CSV

Sheet:'Multiple Trans multi file'
Post a Multple Transaction Types from multiple CSV files

 

Multiple Purchase Invoices

Sheet:'PI'
Post a Multiple PI Transactions

 

File Import:Multiple Types from single CSV

Sheet:'MultiType'
Post a Multple Transaction Types from single CSV files

 

File Import:Multiple Sales Order from CSV

Sheet:'Sales Order'
Post a Multiple Sales Order Transactions

 

File Import:Multiple Types activated by VBA button

Sheet:'MultiType'
Post a Multple Transaction Types

 

File Import:Multiple Sales Invoice from CSV

Sheet:'Invoice'
Post a Multple Sales Invoices

 

File Import:Sales Invoice with Project from CSV

Sheet:'SI'
Post a Sales Invoices to Projects

 

File Import:Purchase Invoice with Project from CSV

Sheet:'PI'
Post a Purchase Invoices to Projects

 

File Import:Sales Orders with Project from CSV

Sheet:'SO'
Post a Sales Orders to Projects

 

File Import:Purchase Orders with Project from CSV

Sheet:'PO'
Post a Purchase Orders to Projects

 

File Import:Sales/Purchase Invoices with Project from CSV

Sheet:'Mixed'
Post a Sales/Purchase Invoices to Projects

 

File Import:Orders from CSV with Payment, Rounding and Extra Lines

Sheet:'Order and Payment CSV'
Post a Sales Order with Payment using total line value from Web Cart system

 

File Import:Direct Debit payments posted as SI and Receipt with Currency

Sheet:'Direct Debit'
Post a Direct Debit payments posted as SI and Receipt with Currency

 

File Import:Receipts with Currency

Sheet:'Receipts'
Post a Receipts with Currency

 

Transaction - Inter Company in 1 sheet (Module: Pro)

Sheet:'All In One'
Post an inter company transaction from one sheet
   Help article for Transaction - Inter Company

 

Transaction - Inter Company in 2 sheets SO/PO (Module: Pro)

Sheet:'Sales'
Post an inter company transaction, one sheet posting a SO, than another to post mirror but PO

 

Transaction - Inter Company in 2 sheets SIA/PIA (Module: Pro)

Sheet:'Sales'
Post an inter company transaction, one sheet posting a SIA, than another to post mirror but PIA (invoice with adjustment)

 

Import into Multiple Companies from CSV

Sheet:'Multi in one sheet'
Post a Multple Companies

Transaction Data - Purchase:

 

Expenses + Mileage

Sheet:'Expense demo'
Posting a Expenses + Mileage in as a Purchase invoice
   Help article for Expenses + Mileage

 

Expenses iPhone/Android or Web via ExpenseIn

Sheet:'Import Expenses'
Import expenses into Sage 50 via IPhone, Android etc. with authorisation
   Help article for Import Expenses

 

Purchase Invoice

Sheet:'Purchase Invoice'
Post a Purchase Invoice

 

Purchase Service Order

Sheet:'Purchase Service Order'
Post a Purchase Service Order which alows long text and bypasses dispatch routine

Transaction Data - Stock Adjustments:

 

Stock Adjustment IN

Sheet:'IN'
Post a Stock adjustment to bring stock IN

 

Stock Adjustment OUT

Sheet:'OUT'
Post a Stock adjustment to bring stock OUT

 

Stock Adjustment Free of Charge

Sheet:'Free Of Charge'
Issues Stock Free of Charge
   Main page for Stock Adjustment Free of Charge

 

Stock Adjustment Goods Return

Sheet:'Goods Return'
Issues Stock Free of Goods Return

 

Stock Adjustment Write Off

Sheet:'Write Off'
Issues Stock Free of Write Off

 

Stock Adjustment OUT with no Bins

Sheet:'OUT No Bins'
Post a Stock adjustment to bring stock OUT with no Bins Specified

 

Stock Adjustment OUT with Batch and Serial

Sheet:'Batches OUT'
ADJ OUT Product with Batches and Serial Nos
   Main page for Stock Adjustment Batches and Serial IN

 

Stock Adjustment IN with Batch and Serial

Sheet:'Batches IN'
ADJ IN Product with Batches and Serial Nos
   Main page for Stock Adjustment Batches and Serial IN

Bins:

 

Locations and Bins

Sheet:'Bins'
This sheet allows the user to extract/amend/delete/create Warehouse Bin records
   Main page for Locations and Bins
   Help article for Locations and Bins

 

Stock Take Locations and Bins

Sheet:'Bins Stock Take'
Stock Take Bins in the same or different Warehouses.
   Main page for Bins Stock Take
   Help article for Bins Stock Take

 

Stock Take Locations and Bins with Serial and Batches

Sheet:'Bins Stock Take Batch Serial'
Stock Take Bins in the same or different Warehouses with Serial and Batches
   Main page for Bins Stock Take

 

Stock movement between Locations and Bins

Sheet:'Bins Movement'
Move Stock items between Bins in the same or different Warehouse
   Main page for Bins Movement
   Help article for Bins Movement

 

Nominal Journal - Basic

Sheet:'NOM'
Import a standard Nominal Journal
   Help article for Nominal Journal - Basic

 

Nominal Journal - VAT

Sheet:'NOM VAT'
Import a VAT, with Contra line, Nominal Journal
   Help article for Nominal Journal - VAT

 

Nominal Journal - Contra

Sheet:'NOM Contra'
Import a Contra Nominal Journal

 

Nominal Journal - Contra CC/Dep specified

Sheet:'NOM Contra CC Dep'
Import a Contra Nominal Journal with CC/Dep specified in separate cells
   Main page for Nominal Journal

 

Nominal Journal - Recurring

Sheet:'NOM Recurring'
Import a Recurring, with Contra line, Nominal Journal

 

Nominal Journal - Reversing

Sheet:'NOM Reversing'
Import a Reversing, with Contra line, Nominal Journal