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.
- Transaction Import Features
- Template Example
- Demo Videos
- Template Structure in detail
- Common Features
- Transaction Options
- Template Tags
- Related Example Templates included with PostTrans
Related Pages
- Blog - Importing CSV/Fixed file into Sage 200
- Blog - Sage 200 Purchase Order Authorisation
- Blog - How to create a Quick Order Pad for Sage 200
- Blog - Printing and e-mailing Transaction after posting in Sage 200
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
- Cashbook Transactions
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
- Create Stock Locations if need be 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
After pressing the Import Button:
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
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
Indeed we can add tag "THTransType" to allow us to post different types of transactions:
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
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)
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)
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
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
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 Setup. In-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:
| 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 |
| 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 |
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.
| 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) |
Transactions Import Options
Transaction Import has the following options, which are accessible by pressing the Setup button on the toolbar:
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. |
| Create Warehouse rec for stock if need be. | Create Warehouse rec for stock record if it does not exist with 0 Min,Max and Reorder + "Unspecified" bin. |
| Stock Search - Show All | Normmaly searching just shows records relating to current Warehouse. If on shows all. To be used with above switch |
| 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.
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.
| 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 IOX |
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 OXU |
Stock Code |
|
TLDesc |
Text 60 EOF XML IOXU |
Product Description. PostTrans stops importing after finding 5 consecutive blank cells. |
|
TLQty^ |
Double XML OXU |
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 |
|
TLQtyAlloc^ |
Text 20 XML OX |
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. |
|
TLUnitPrice^ |
Currency XML IOXU |
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. If SIM and Inclusive VAT on then inclusve amount. |
|
TLFulfilment^ |
Integer XML SO |
Fulfilment from stock by default. SOP Only |
|
Other: |
||
|
TLDiscPerc^ |
Double XML OXU |
Sales:Additional Discount Percent to be applied to TLUnitPrice. Note TLUnitPrice has already had any system discount applied on account record. |
|
TLLoc^ |
Text 20 XML OXU |
Location. Ignored if warehouses are off. DoubleClick to see Stock Levels |
|
TLBin |
Text 20 XML O |
Bin for PIA and SIA |
|
TLBinSetAllocP |
Integer |
Bin Allocation Priority 1-9 if created (settings allows this in setup button) |
|
TLBinSetPrim |
Yes/NoTF |
Y Set Primary Bin if created (settings allows this in setup button) |
|
TLReqDelDate |
Date XML IOXU |
Requested Delivery Date on Order, Expire Date on Quote. Overides the date set on Header in THDueDate |
|
TLPromDate |
Date XML SOXU |
Date Promised. SOP Only |
|
Nom Analysis: |
||
|
TLNomCode^ |
Text 16 XML IOXU |
Nominal Code, if not specified then uses default |
|
TLCC^ |
Text 3 IO |
Default CC. Only use for setting with formula |
|
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 |
|
UOM: |
||
|
TLQtyMul^ |
Double XML OX |
Qty Multiplier set by unit of meaure |
|
TLUOM^ |
Text 20 XML O |
Unit of Measure for Stock Item. SOP Only |
|
Other Values: |
||
|
TLTotNet |
Currency Read IOX |
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. |
|
TLStdPrice^ |
Currency Read Pro O |
Standard Selling price with currency conversion |
|
TLCostPrice |
Currency Read XML OXU |
Stock Cost Price. Standard, Average Cost. If Serial Batch/FIFO then returns Average. |
|
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. |
|
Line Type: |
||
|
TLAddChType^ |
Text 20 XML OXU |
Additional Charge Type, if not specified then set to "Carriage", Will Search on POP/SOP list. |
|
TLLineType^ |
Text 2 XML OXU |
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 |
|
TLDispatchListComment |
Text 160 XML OXU |
Comment on Dispatch |
|
TLPickListComment |
Text 160 XML OXU |
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. |
|
TLTaxCode^ |
Text 2 XML IOXU |
Tax Code. Make sure the format of the cell is “General” else search may not work properly |
|
TLTaxAmount |
Currency Read IOXU |
Tax Amount for Display calculated during manual input of Invoice/Order |
|
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. DoubleClick to see Stock Levels |
|
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 |
|
StSuppPartNo |
Text 16 Read Rel O |
Supplier Part Number |
|
StMinOrdQty |
Double Read Rel O |
Supp Min Order Qty |
|
StSuppLeadTime |
Double Read Rel O |
Supp Lead Time |
|
StSuppLeadTimeUnit |
Text 20 Read Rel O |
Supp Lead Time |
|
StSuppQtyOnOrd |
Double Read Rel O |
Supp Qty on Order |
|
StSuppBuyPrice |
Currency Read Rel O |
Supp Buy Price |
|
StSuppLastPriceDate |
Date Read Rel O |
Supp Last Price Date |
|
StSuppListPrice |
Currency Read Rel O |
Supp List Price |
|
StQtyReOrder |
Double Read Rel O |
Supp Re Order Qty |
|
StSuppLastOrdDate |
Date Read Rel O |
Supp Last Order Date |
|
Important Header Fields: |
||
|
THTransType^ |
Text 4 Head Chg XML IOX |
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. |
|
THManDocRef |
Text 30 Head Chg XML O |
Manual ref if manual Reference in sage 200 is ON |
|
THAccCode^ |
Text 8 Head Chg XML IOX |
Transaction Account Code. Customer or Supplier depending on Transaction Type. DOUBLE CLICK to see ledger |
|
THTransDate |
Date Head Chg XML IOXU |
Transaction Date |
|
THDueDelDate |
Date Head XML IOXU |
Delivery Date on Order, Expire Date on Quote. PO it’s the Requested Date |
|
THPromDelDate |
Date Head XML IOXU |
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 IOXU |
Suppliers/Customer`s Order Number, 10 chars long on PI/SI |
|
THCustRef2 |
Text 30 Head Chg XML IXU |
Customer`s Order Number, 10 chars long on PI/SI |
|
THDefaultLoc^ |
Text 20 Head XML OX |
Default Location for all entered lines. Ignored if warehouses are off |
|
THOrderPriority |
Text 1 Head XML OXU |
Order Priority A-Z |
|
CASH Invoice Address: |
||
|
THInvName |
Text 60 Head XML O |
Invoice name if Cash account |
|
THInvAdd1 |
Text 60 Head XML OXU |
Invoice Add1 if Cash account |
|
THInvAdd2 |
Text 60 Head XML OXU |
Invoice Add2 if Cash account |
|
THInvAdd3 |
Text 60 Head XML OXU |
Invoice Add3 if Cash account |
|
THInvAdd4 |
Text 60 Head XML OXU |
Invoice Add4 if Cash account |
|
THInvPostcode^ |
Text 60 Head XML OXU |
Invoice Postcode if Cash account |
|
CASH Segmented Address: |
||
|
THInvCity |
Text 60 Head XML OXU |
Invoice City if Cash account |
|
THInvCounty |
Text 60 Head XML OXU |
Invoice County if Cash account |
|
THInvCountry^ |
Text 60 Head Read XML OXU |
Invoice Country if Cash account |
|
Currency: |
||
|
THCurCode |
Text 3 Head Read IOX |
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 IOXU |
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 OXU |
Delivery Address Contact Name. Double click to change delivery address |
|
THPostalName |
Text 60 Head XML OXU |
Delivery PostalName |
|
THDelAdd1 |
Text 60 Head XML OXU |
Delivery Address Line 1. Double click to change delivery address. On PO is the direct delivery address |
|
THDelAdd2 |
Text 60 Head XML OXU |
Delivery Address Line 2. Double click to change delivery address |
|
THDelAdd3 |
Text 60 Head XML OXU |
Delivery Address Line 3. Double click to change delivery address. On PO is the direct delivery address |
|
THDelAdd4 |
Text 60 Head XML OXU |
Delivery Address Line 4. Double click to change delivery address |
|
THDelPostcode^ |
Text 60 Head XML OXU |
Delivery Postcode. PRO -If Postcode Search is enabled in ‘Setup >> Lookup >> Address Lookup’ then enter Space or Space + Postcode to search for an address. |
|
THDelEmail |
Text 255 Head XML OXU |
Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order. |
|
THDelTelNo |
Text 60 Head XML OXU |
Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order. |
|
THDelFaxNo |
Text 60 Head XML OXU |
Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order. |
|
Delivery Address Segmented Address: |
||
|
THDelCounty |
Text 60 Head XML OXU |
Delivery Address County. Double click to change delivery address |
|
THDelCountry^ |
Text 60 Head XML OXU |
Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order. |
|
THDelCity |
Text 60 Head XML OXU |
Delivery Address City. Double click to change delivery address |
|
Project (Pro): |
||
|
THProject^ |
Text 20 Head Pro IO |
Default Project code for Transactions, can be overidden using TLProject |
|
Project on Line: |
||
|
TLProject^ |
Text 20 Pro XML IO |
Project code, overides THProject Can also be Project"/"Phase to book to specific Project Item |
|
TLPhaseFilter^ |
Text 30 XML |
Phase Filter. If project has duplicate Project items in different phases, this can direct to correct Phase |
|
TLCostCode^ |
Text 20 Pro XML IO |
Cost Code, overides THCostCode |
|
Header Discount: |
||
|
Header Payment: |
||
|
THPayAmt |
Double Head XML IOX |
Amount Prepaid. If used in lines then Sum is posted. Option in `Setup >> Switches’, can stop posting if UNDER or OVER paid |
|
THPayRef |
Text 20 Head XML IOX |
Payment Reference |
|
THBankAccList |
Text HeadOnly XML |
When posting Payments Bank Account to use. |
|
THBankAcc^ |
Text Head XML |
Specify the bank account |
|
THPayMethod^ |
Text 30 Head XML IOX |
Payment Method |
|
Header:Add Message: |
||
|
THMessTop |
Text HeadOnly IO |
Add message at top of transaction using Comment on IN/ORD |
|
THMessBot1 |
Text HeadOnly IO |
Add message at bottom of transaction using Comment on IN/ORD |
|
THMessBot2 |
Text HeadOnly IO |
Add message at bottom of transaction using Comment on IN/ORD |
|
Additional Charge: |
||
|
THAddChgTxt1 |
Text 20 HeadOnly O |
Additional Charge Text 1 |
|
THAddChgType1^ |
Text 20 HeadOnly O |
Additional Charge Type 1 (Placed above THMessBot1/THMessBot2) |
|
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) |
|
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) |
|
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) |
|
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 |
|
THPrintEmailCC |
Text HeadOnly IO |
Email address to CC to. Multiples and be expressed using “,”. |
|
THPrintSubject |
Text HeadOnly IO |
Subject of email, which overrides that set in System >> Printing. |
|
THPrintBody |
Text HeadOnly IO |
Body of email, which overrides that set in System >> Printing. |
|
THPrintAttachment |
Text HeadOnly IO |
Add an attachment. Full Path. Multiples and be expressed using “,”. |
|
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 |
|
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 |
|
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. |
|
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. |
|
THBefDisplayMessYN |
Text 60 HeadOnly IO |
If contains value, then this text is displayed. Asks Yes/No to continue |
|
Header - After 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 |
|
THAftSwitchSheet |
Text 60 HeadOnly Read Pro IO |
After posting this sheet, switch to work book with this name PRO |
|
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. |
|
THAftSaveSubDir |
Text 60 HeadOnly Read Pro IO |
See THAftSavePATH |
|
THAftSaveFilename |
Text 60 HeadOnly Read Pro IO |
See THAftSavePATH. Example ‘ABAP01’ will saveas ABAP01_0001.xls. |
|
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. |
|
THImportFileName |
Text 500 HeadOnly IO |
Filename of the file just imported using the Import button |
|
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. |
|
AcOut1MDays^ |
Currency Read Rel IO |
Outstanding over 30 days. |
|
AcAvCredit^ |
Currency Read Rel IO |
Available Credit. |
|
AcCreditLimit^ |
Currency Read Rel IO |
Credit Limit. PRO - Double clicking on this cell will drill down to Ledger. |
|
AcOutDaysTxt^ |
Text Read Rel IO |
Outstanding 30,60,90,120 day break down in text form. |
|
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. |
|
AcAddCountry |
Text 60 Read Rel IO |
Account Country. |
|
AcCountryCode^ |
Text 2 Read Rel IO |
Country Code |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
AcDcontCountry |
Text 60 Read Rel IO |
Default Contact Country |
|
Header Analysis: |
||
|
THAnal1^ |
Text 60 Head XML IOXU |
Header Analysis 1 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal2^ |
Text 60 Head XML IOXU |
Header Analysis 2 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal3^ |
Text 60 Head XML IOXU |
Header Analysis 3 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal4^ |
Text 60 Head XML IOXU |
Header Analysis 4 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal5^ |
Text 60 Head XML IOXU |
Header Analysis 5 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal6^ |
Text 60 Head XML IOXU |
Header Analysis 6 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal7^ |
Text 60 Head XML IOXU |
Header Analysis 7 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal8^ |
Text 60 Head XML IOXU |
Header Analysis 8 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal9^ |
Text 60 Head XML IOXU |
Header Analysis 9 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal10^ |
Text 60 Head XML IOXU |
Header Analysis 10 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal11^ |
Text 60 Head XML IOXU |
Header Analysis 11 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal12^ |
Text 60 Head XML IOXU |
Header Analysis 12 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal13^ |
Text 60 Head XML IOXU |
Header Analysis 13 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal14^ |
Text 60 Head XML IOXU |
Header Analysis 14 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal15^ |
Text 60 Head XML IOXU |
Header Analysis 15 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal16^ |
Text 60 Head XML IOXU |
Header Analysis 16 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal17^ |
Text 60 Head XML IOXU |
Header Analysis 17 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal18^ |
Text 60 Head XML IOXU |
Header Analysis 18 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal19^ |
Text 60 Head XML IOXU |
Header Analysis 19 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
THAnal20^ |
Text 60 Head XML IOXU |
Header Analysis 20 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
Spare: |
||
|
THSpareTxt1 |
Text 60 Head XML IOXU |
SpareTxt1 also used in XML to allocate Sales Receipt information |
|
THSpareTxt2 |
Text 60 Head XML IOXU |
SpareTxt2 |
|
THSpareTxt3 |
Text 60 Head XML IOXU |
SpareTxt3 |
|
THSpareBit1 |
Yes/NoTF Head XML IOXU |
SpareBit1 |
|
THSpareBit2 |
Yes/NoTF Head XML IOXU |
SpareBit2 |
|
THSpareBit3 |
Yes/NoTF Head XML IOXU |
SpareBit3 |
|
THSpareNo1 |
Integer Head XML IOXU |
SpareNo1 |
|
THSpareNo2 |
Integer Head XML IOXU |
SpareNo2 |
|
THSpareNo3 |
Integer Head XML IOXU |
SpareNo3 |
|
THSpareDate1 |
Date Head XML IOXU |
SpareDate1 |
|
THSpareDate2 |
Date Head XML IOXU |
SpareDate2 |
|
THSpareDate3 |
Date Head XML IOXU |
SpareDate3 |
|
Line Analysis: |
||
|
TLAnal1^ |
Text 60 XML IOXU |
Line Analysis 1 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal2^ |
Text 60 XML IOXU |
Line Analysis 2 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal3^ |
Text 60 XML IOXU |
Line Analysis 3 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal4^ |
Text 60 XML IOXU |
Line Analysis 4 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal5^ |
Text 60 XML IOXU |
Line Analysis 5 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal6^ |
Text 60 XML IOXU |
Line Analysis 6 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal7^ |
Text 60 XML IOXU |
Line Analysis 7 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal8^ |
Text 60 XML IOXU |
Line Analysis 8 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal9^ |
Text 60 XML IOXU |
Line Analysis 9 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal10^ |
Text 60 XML IOXU |
Line Analysis 10 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal11^ |
Text 60 XML IOXU |
Line Analysis 11 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal12^ |
Text 60 XML IOXU |
Line Analysis 12 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal13^ |
Text 60 XML IOXU |
Line Analysis 13 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal14^ |
Text 60 XML IOXU |
Line Analysis 14 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal15^ |
Text 60 XML IOXU |
Line Analysis 15 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal16^ |
Text 60 XML IOXU |
Line Analysis 16 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal17^ |
Text 60 XML IOXU |
Line Analysis 17 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal18^ |
Text 60 XML IOXU |
Line Analysis 18 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal19^ |
Text 60 XML IOXU |
Line Analysis 19 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
TLAnal20^ |
Text 60 XML IOXU |
Line Analysis 20 - Setup View >> Analaysis code menu option will list mapping numbers and values |
|
Line Spare: |
||
|
TLSpareTxt1 |
Text 60 XML IOXU |
SpareTxt1 |
|
TLSpareTxt2 |
Text 60 XML IOXU |
SpareTxt2 |
|
TLSpareTxt3 |
Text 60 XML IOXU |
SpareTxt3 |
|
TLSpareBit1 |
Yes/NoTF XML IOXU |
SpareBit1 |
|
TLSpareBit2 |
Yes/NoTF XML IOXU |
SpareBit2 |
|
TLSpareBit3 |
Yes/NoTF XML IOXU |
SpareBit3 |
|
TLSpareNo1 |
Integer XML IOXU |
SpareNo1 |
|
TLSpareNo2 |
Integer XML IOXU |
SpareNo2 |
|
TLSpareNo3 |
Integer XML IOXU |
SpareNo3 |
|
TLSpareDate1 |
Date XML IOXU |
SpareDate1 |
|
TLSpareDate2 |
Date XML IOXU |
SpareDate2 |
|
TLSpareDate3 |
Date XML IOXU |
SpareDate3 |
|
Ac Anal: |
||
|
AcAnal1 |
Text 60 IO |
Account Anal1 |
|
AcAnal2 |
Text 60 IO |
Account Anal2 |
|
AcAnal3 |
Text 60 IO |
Account Anal3 |
|
AcAnal4 |
Text 60 IO |
Account Anal4 |
|
AcAnal5 |
Text 60 IO |
Account Anal5 |
|
AcAnal6 |
Text 60 IO |
Account Anal6 |
|
AcAnal7 |
Text 60 IO |
Account Anal7 |
|
AcAnal8 |
Text 60 IO |
Account Anal8 |
|
AcAnal9 |
Text 60 IO |
Account Anal9 |
|
AcAnal10 |
Text 60 IO |
Account Anal10 |
|
AcAnal11 |
Text 60 IO |
Account Anal11 |
|
AcAnal12 |
Text 60 IO |
Account Anal12 |
|
AcAnal13 |
Text 60 IO |
Account Anal13 |
|
AcAnal14 |
Text 60 IO |
Account Anal14 |
|
AcAnal15 |
Text 60 IO |
Account Anal15 |
|
AcAnal16 |
Text 60 IO |
Account Anal16 |
|
AcAnal17 |
Text 60 IO |
Account Anal17 |
|
AcAnal18 |
Text 60 IO |
Account Anal18 |
|
AcAnal19 |
Text 60 IO |
Account Anal19 |
|
AcAnal20 |
Text 60 IO |
Account Anal20 |
|
Post Extra Stock Line 1: |
||
|
THExLineStock1^ |
Text 30 Head O |
Stock code. Not used on Service Type Transactions. For Extra Line |
|
TLExLineDesc1 |
Text 60 O |
Product Description. PostTrans stops importing after finding 5 consecutive blank cells. |
|
TLExLineQty1^ |
Double O |
Quantity For Extra Line |
|
TLExLineNet1^ |
Currency O |
Unit Price for each item For Extra Line |
|
TLExLineNomCode1^ |
Text 8 O |
Nominal Code, if not specified then uses default from stock/account. For Extra Line |
|
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 |
|
TLExLineTaxManTot1 |
Currency O |
Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line |
|
TLExTotIncluOfTax1 |
Currency Pro O |
Line Total inclusive of Tax. Best choice when importing data as reduces rounding problems For Extra Line |
|
Post Extra Add Charge Line 1: |
||
|
THExAddChrCode1^ |
Text 30 Head O |
Additional Charge code. Not used on Service Type Transactions. For Extra Line |
|
TLExAddChrDesc1 |
Text 60 O |
Product Description. PostTrans stops importing after finding 5 consecutive blank cells. |
|
TLExAddChrNet1^ |
Currency O |
Unit Price for each item For Extra Line |
|
TLExAddChrNomCode1^ |
Text 8 O |
Nominal Code, if not specified then uses default from stock/account. For Extra Line |
|
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 |
|
TLExAddChrTaxManTot1 |
Currency O |
Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line |
|
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 |
|
Post Extra Add Charge Line 2: |
||
|
THExAddChrCode2^ |
Text 30 Head O |
Additional Charge code. Not used on Service Type Transactions. For Extra Line |
|
TLExAddChrDesc2 |
Text 60 O |
Product Description. PostTrans stops importing after finding 5 consecutive blank cells. |
|
TLExAddChrNet2^ |
Currency O |
Unit Price for each item For Extra Line |
|
TLExAddChrNomCode2^ |
Text 8 O |
Nominal Code, if not specified then uses default from stock. For Extra Line |
|
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 |
|
TLExAddChrTaxManTot2 |
Currency O |
Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line |
|
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 |
|
Post Extra Add Charge Line 3: |
||
|
THExAddChrCode3^ |
Text 30 Head O |
Additional Charge code. Not used on Service Type Transactions. For Extra Line |
|
TLExAddChrDesc3 |
Text 60 O |
Product Description. PostTrans stops importing after finding 5 consecutive blank cells. |
|
TLExAddChrNet3^ |
Currency O |
Unit Price for each item For Extra Line |
|
TLExAddChrNomCode3^ |
Text 8 O |
Nominal Code, if not specified then uses default from stock. For Extra Line |
|
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 |
|
TLExAddChrTaxManTot3 |
Currency O |
Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line |
|
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 |
|
Post Extra Add Charge Line 4: |
||
|
THExAddChrCode4^ |
Text 30 Head O |
Additional Charge code. Not used on Service Type Transactions. For Extra Line |
|
TLExAddChrDesc4 |
Text 60 O |
Product Description. PostTrans stops importing after finding 5 consecutive blank cells. |
|
TLExAddChrNet4^ |
Currency O |
Unit Price for each item For Extra Line |
|
TLExAddChrNomCode4^ |
Text 8 O |
Nominal Code, if not specified then uses default from stock. For Extra Line |
|
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 |
|
TLExAddChrTaxManTot4 |
Currency O |
Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line |
|
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 |
|
ObjectStore Mappings Lines: |
||
|
ObjectStore Mappings: |
||
|
THUser1 |
Text 30 Head XML IO |
10 user defined fields which can be mapped to 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 |
|
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 |
|
TLUser12 |
Text 30 XML IO |
TLUser12 |
|
TLUser13 |
Text 30 XML IO |
|
|
TLUser14 |
Text 30 XML IO |
|
|
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 |
|
|
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 |
|
TLUser22 |
Text 30 XML IO |
TLUser22 |
|
TLUser23 |
Text 30 XML IO |
TLUser23 |
|
TLUser24 |
Text 30 XML IO |
|
|
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 |
|
|
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 |
|
TLUser32 |
Text 30 XML IO |
TLUser32 |
|
TLUser33 |
Text 30 XML IO |
TLUser33 |
|
TLUser34 |
Text 30 XML IO |
|
|
TLUser35 |
Text 30 XML IO |
|
|
TLUser36 |
Text 30 XML IO |
|
|
TLUser37 |
Text 30 XML IO |
TLUser37 |
|
TLUser38 |
Text 30 XML IO |
TLUser38 |
|
TLUser39 |
Text 30 XML IO |
TLUser39 |
|
TLUser40 |
Text 30 XML IO |
TLUser40 |
|
Copy/Edit Transactions: |
||
|
Copy/Edit Transaction: |
||
|
TLEdCRC |
Text Pro IOX |
Turns ON Editing : Contains CRC number to help PostTrans detect changes in current row. Deleting this code will force the line to be processed. |
|
TLEdLineID |
Integer Pro IOXU |
Turns ON Editing : Line ID allowing PostTrans to update correct line. If deleted will creat a new line in transaction. Sheet should be in this order before importing. |
|
TLEdStatus |
Text IOXU |
Transaction Line Status= Locked (posted), Edit, (Ok to edit) and Alloc (stock allocated) |
|
THEdDocNo |
Text 30 Head IOXU |
Turns ON Copying : If in header then gets single Transaction : Contains the DocNo if a single transaction is being edited |
|
THEdExtComp^ |
Text 30 HeadOnly IOU |
Defines the company to extract Transactions from. Allowing inter company transactions |
|
THEdExtTransOfType^ |
Text 30 HeadOnly IOU |
Extract Transactions of only of type |
|
TLEdCommand |
Text Pro IOXU |
X=Delete,C=Cancel for PO X=Delete,C=Cancel,L=Cancel create line,R=Cancel and reject SOR lines,B=Cancel create line and reject SOR lines |
Req = Field is required EOF = End of file, Stop when no more values
Pro = PostTrans Pro version needed to use this field Read = Read Only
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: |
||
| Download Sage200trans.xlsx | ||
|
Sales Order |
Sheet:'Sales Order' |
|
|
Sales Order with Serial and Batch allocation |
Sheet:'Sales Order' |
|
| Download Sage200orderPad.xlsx | ||
|
Sales Order Pad Pro |
Sheet:'Order Pad' |
|
| Download Sage200Purchase.xlsx | ||
|
PO with Project costing and Phase Filter |
Sheet:'PO Phase Filter' |
|
| Download Sage200trans.xlsx | ||
|
Sales Order + Info |
Sheet:'Sales Order Info' |
|
| Download Sage200Purchase.xlsx | ||
|
Purchase Order |
Sheet:'PO' |
|
|
Purchase Order with Authorisation |
Sheet:'PO Auth' |
|
| Download Sage200RemotePur.xlsx | ||
|
Remote Purchase Order |
Sheet:'PO Auth' |
|
| Download Sage200trans.xlsx | ||
|
Sales Order Additional Chrage |
Sheet:'Sales Order Charge' |
|
|
Sales Order with Payment Pro |
Sheet:'Sales Order Pay' |
|
|
Sales Order in Currency Pro |
Sheet:'SO in Currency' |
|
|
Sales Service Order |
Sheet:'Sales Service Order' |
|
|
Sales Invoice (Invoice Ledger) |
Sheet:'Sales Invoice INV LEDGER' |
|
|
Sales Invoice to Accounts Ledger with Gross Rounding |
Sheet:'Sales Invoice Round' |
|
|
Sales Opening Balance |
Sheet:'Sales Opening Balance' |
|
|
Sales Credit Note |
Sheet:'Sales Credit Note' |
|
|
Sales Invoice to Accounts Ledger |
Sheet:'Sales Invoice' |
|
| Download Sage200transMultiples.xlsx | ||
|
File Import:Multiple Types from single CSV |
Sheet:'MultiType' |
|
|
File Import:Multiple Types from multiple CSV |
Sheet:'Multiple Trans multi file' |
|
|
File Import:Multiple Sales Invoice from CSV |
Sheet:'Invoice' |
|
|
File Import:Multiple Sales Order from CSV |
Sheet:'Sales Order' |
|
| Download Sage200transMultiplesByCode.xlsm | ||
|
File Import:Multiple Types activated by VBA button |
Sheet:'MultiType' |
|
| Download Sage200transMultiples.xlsx | ||
|
Multiple Purchase Invoices |
Sheet:'PI' |
|
| Download Sage200Projects.xlsx | ||
|
File Import:Sales Invoice with Project from CSV |
Sheet:'SI' |
|
|
File Import:Purchase Invoice with Project from CSV |
Sheet:'PI' |
|
|
File Import:Sales Orders with Project from CSV |
Sheet:'SO' |
|
|
File Import:Purchase Orders with Project from CSV |
Sheet:'PO' |
|
|
File Import:Sales/Purchase Invoices with Project from CSV |
Sheet:'Mixed' |
|
| Download Sage200FileImportPayment.xlsx | ||
|
File Import:Orders from CSV with Payment, Rounding and Extra Lines |
Sheet:'Order and Payment CSV' |
|
| Download Sage200PaymentDD.xlsx | ||
|
File Import:Direct Debit payments posted as SI and Receipt with Currency |
Sheet:'Direct Debit' |
|
|
File Import:Receipts with Currency |
Sheet:'Receipts' |
|
| Download Sage200InterCompany1.xlsx | ||
|
Transaction - Inter Company in 1 sheet (Module: Pro) |
Sheet:'All In One' |
|
| Download Sage200InterCompany2.xlsx | ||
|
Transaction - Inter Company in 2 sheets SO/PO (Module: Pro) |
Sheet:'Sales' |
|
| Download Sage200InterCompany3.xlsx | ||
|
Transaction - Inter Company in 2 sheets SIA/PIA (Module: Pro) |
Sheet:'Sales' |
|
| Download Sage200trans Multicompany.xlsx | ||
|
Import into Multiple Companies from CSV |
Sheet:'Multi in one sheet' |
|
Edit/Copy Transactions: |
||
| Download Sage200transEdit.xlsx | ||
|
Edit Multiple Transactions SO/PO/SI/SQ/Proforma |
Sheet:'EditTransMultiple' |
|
|
Copy Multiple Transactions SO/PO/SI/SQ/Proforma |
Sheet:'CopyTransMultiple' |
|
|
Copy Single Transactions SO/PO/SI/SQ/Proforma |
Sheet:'CopyTransSingle' |
|
Transaction Data - Purchase: |
||
| Download Sage200expense.xlsm | ||
|
Expenses + Mileage |
Sheet:'Expense demo' |
|
| Download Sage200ExpenseIn.xlsx | ||
|
Expenses iPhone/Android or Web via ExpenseIn |
Sheet:'Import Expenses' |
|
| Download Sage200Purchase.xlsx | ||
|
Purchase Invoice |
Sheet:'Purchase Invoice' |
|
| Download Sage200trans.xlsx | ||
|
Purchase Service Order |
Sheet:'Purchase Service Order' |
|
| Download Sage200cashbook.xlsx | ||
|
Sales Payment On Account Pro |
Sheet:'SPOA' |
|
Transaction Data - Stock Adjustments: |
||
| Download Sage200TransADJ.xlsx | ||
|
Stock Adjustment Free of Charge |
Sheet:'Free Of Charge' |
|
|
Stock Adjustment Write Off |
Sheet:'Write Off' |
|
|
Stock Adjustment OUT with no Bins |
Sheet:'OUT No Bins' |
|
|
Stock Adjustment Goods Return |
Sheet:'Goods Return' |
|
|
Stock Adjustment OUT |
Sheet:'OUT' |
|
|
Stock Adjustment IN |
Sheet:'IN' |
|
|
Stock Adjustment OUT with Batch and Serial |
Sheet:'Batches OUT' |
|
|
Stock Adjustment IN with Batch and Serial |
Sheet:'Batches IN' |
|
Bins: |
||
| Download Sage200bins.xlsx | ||
|
Locations and Bins |
Sheet:'Bins' |
|
|
Stock Take Locations and Bins with Serial and Batches |
Sheet:'Bins Stock Take Batch Serial' |
|
|
Stock Take Locations and Bins |
Sheet:'Bins Stock Take' |
|
|
Stock movement between Locations and Bins |
Sheet:'Bins Movement' |
|
Transaction Data - Purchase: |
||
| Download Sage200cashbook.xlsx | ||
|
Cashbook Single Payment Pro |
Sheet:'BP' |
|
|
Cashbook Single Receipt Pro |
Sheet:'BR' |
|
|
Cashbook Single Receipt with Tax specified on lines Pro |
Sheet:'BR with Tax' |
|
|
Cashbook Multiples Receipts with Tax specified on lines Pro |
Sheet:'BR Multiples with Tax' |
|
|
Cashbook Multiples Receipts + All on lines Pro |
Sheet:'BR Multiples' |
|
|
Cashbook Multiples Receipts, Companies + All on lines Pro |
Sheet:'BR Multiple Companies' |
|
| Download Sage200nom.xlsx | ||
|
Nominal Journal - Basic |
Sheet:'NOM' |
|
|
Nominal Journal Ref |
Sheet:'NOM Ref' |
|
|
Nominal Journal - VAT |
Sheet:'NOM VAT' |
|
|
Nominal Journal - Contra |
Sheet:'NOM Contra' |
|
|
Nominal Journal - Contra CC/Dep to multiple Companies |
Sheet:'NOM Multi Company' |
|
|
Nominal Journal - Contra CC/Dep specified |
Sheet:'NOM Contra CC Dep' |
|
|
Nominal Journal - Recurring |
Sheet:'NOM Recurring' |
|
|
Nominal Journal - Reversing |
Sheet:'NOM Reversing' |
|