This type of PostTrans import, imports Sales/Purchase Transactions, which can be input manually, data from an ODBC connection or
imported from CSV file.
The transaction type is set, either on the Header of the import, or on the lines of the import. See examples below.
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).
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).
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.
Formula, VBA and macros can be added to further enhance this data entry template.
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.
Indeed we can add tag "THTransType" to allow us to post different types of transactions:
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.
Formula, VBA and macros can be added to further enhance this data entry template.
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.
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:
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.
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.
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.
The path the cursor takes through the sheet, when entering data manually, can also be easily defined in
Setup. See Set Cursor Path
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.
Any values and associated codes will be invalid during import.
The cursor will be moved to highlight any problems in data.
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.
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.
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
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
Since the functionality of this type of import is quite broad, the following Videos/Blog articles have videos showing it in action:
It is easy to post Sage Line 50 Inter-Company Transactions, since you can either:
Transaction Import has the following options, which are accessible by pressing the Setup button on the toolbar:
The above settings are stored in each sheet, Cell A1 Comment, where as most settings are stored per workbook
This would be turn off if importing a large number of lines from an external file.
The above settings are stored in each sheet, in the comment in cell A1.
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
In Cell Searching values in table ProdCodeNoDesc
|
|
TLDesc
|
Text 60 EOF XML IOXU
|
Product Description. PostTrans stops importing after finding 5 consecutive blank cells.
How Descriptions are Imported
|
|
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
Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
|
|
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.
Sage 50 Quick Pad
Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
|
|
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.
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
|
|
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.
Discount calculation
Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
|
|
TLLoc^
|
Text 20 XML OXU
|
Location. Ignored if warehouses are off. DoubleClick to see Stock Levels
In Cell Searching values in table Loc
|
|
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
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 OX
|
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 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
Sage 50 Quick Pad
Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
|
|
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.
Sage V17 onwards
|
|
Line Type:
|
|
TLAddChType^
|
Text 20 XML OXU
|
Additional Charge Type, if not specified then set to "Carriage", Will Search on POP/SOP list.
In Cell Searching values in table OPAddCharge
|
|
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
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 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.
more about VAT
|
|
TLTaxCode^
|
Text 2 XML IOXU
|
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 IOXU
|
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. DoubleClick to see Stock Levels
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
|
|
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 3 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.
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
SPOA, Sales Payment On Account
PO, Purchase Order
PSO, Purchase Service Order
PI, Purchase Invoice
PIA, Purchase Invoice and Adj IN
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 IOX
|
Transaction Account Code. Customer or Supplier depending on Transaction Type. DOUBLE CLICK to see ledger
In Cell Searching values in table THAccCode
|
|
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
In Cell Searching values in table Loc
|
|
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
More about Postcode Address Lookup
In Cell Searching values in table SimplyPostcode
|
|
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
In Cell Searching values in table CountryCode
|
|
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.
More about Postcode Address Lookup
In Cell Searching values in table SimplyPostcode
|
|
THDelEmail
|
Text 255 Head XML OXU
|
Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order.
emailing Sage 200 transaction
|
|
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.
In Cell Searching values in table CountryCode
|
|
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
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 IOX
|
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 20 Head XML IOX
|
Payment Reference
more about with payment
|
|
THBankAccList
|
Text HeadOnly XML
|
When posting Payments Bank Account to use.
|
|
THBankAcc^
|
Text Head XML
|
Specify the bank account
In Cell Searching values in table BANKACCOUNT
|
|
THPayMethod^
|
Text 30 Head XML IOX
|
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
|
|
THBefDisplayMessYN
|
Text 60 HeadOnly IO
|
If contains value, then this text is displayed. Asks Yes/No to continue
Validating Transaction before posting
|
|
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
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 IOXU
|
Header Analysis 1 set for all lines on thransaction - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_01
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_02
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_03
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_04
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_05
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_06
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_07
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_08
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_09
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_10
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_11
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_12
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_13
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_14
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_15
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_16
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_17
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_18
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_19
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_20
|
|
Spare:
|
|
THSpareTxt1
|
Text 60 Head XML IOXU
|
SpareTxt1
|
|
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
Sage V2015 onwards
In Cell Searching values in table AnalLine_01
|
|
TLAnal2^
|
Text 60 XML IOXU
|
Line Analysis 2 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_02
|
|
TLAnal3^
|
Text 60 XML IOXU
|
Line Analysis 3 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_03
|
|
TLAnal4^
|
Text 60 XML IOXU
|
Line Analysis 4 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_04
|
|
TLAnal5^
|
Text 60 XML IOXU
|
Line Analysis 5 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_05
|
|
TLAnal6^
|
Text 60 XML IOXU
|
Line Analysis 6 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_06
|
|
TLAnal7^
|
Text 60 XML IOXU
|
Line Analysis 7 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_07
|
|
TLAnal8^
|
Text 60 XML IOXU
|
Line Analysis 8 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_08
|
|
TLAnal9^
|
Text 60 XML IOXU
|
Line Analysis 9 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_09
|
|
TLAnal10^
|
Text 60 XML IOXU
|
Line Analysis 10 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_10
|
|
TLAnal11^
|
Text 60 XML IOXU
|
Line Analysis 11 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_11
|
|
TLAnal12^
|
Text 60 XML IOXU
|
Line Analysis 12 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_12
|
|
TLAnal13^
|
Text 60 XML IOXU
|
Line Analysis 13 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_13
|
|
TLAnal14^
|
Text 60 XML IOXU
|
Line Analysis 14 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_14
|
|
TLAnal15^
|
Text 60 XML IOXU
|
Line Analysis 15 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_15
|
|
TLAnal16^
|
Text 60 XML IOXU
|
Line Analysis 16 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_16
|
|
TLAnal17^
|
Text 60 XML IOXU
|
Line Analysis 17 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_17
|
|
TLAnal18^
|
Text 60 XML IOXU
|
Line Analysis 18 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_18
|
|
TLAnal19^
|
Text 60 XML IOXU
|
Line Analysis 19 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_19
|
|
TLAnal20^
|
Text 60 XML IOXU
|
Line Analysis 20 - Setup View >> Analaysis code menu option will list mapping numbers and values
Sage V2015 onwards
In Cell Searching values in table AnalLine_20
|
|
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
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
|
|
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
In Cell Searching values in table CompanySelect
|
|
THEdExtTransOfType^
|
Text 30 HeadOnly IOU
|
Extract Transactions of only of type
In Cell Searching values:Extract Only
SQ, Quote
Sp, Pro Forma
SIM, Sales Invoice Module
PO, Purchase Order
|
|
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
|
All Text fields are trimmed (spaces before) by default. Add ' to the end of the tag name to stop this.
.
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
|
|
|
|
|
PO with Project costing and Phase Filter
|
Sheet:'PO Phase Filter'
Post a Purchase Order
|
|
|
|
|
Sales Order + Info
|
Sheet:'Sales Order Info'
Post a Sales Order showing more customer information
|
|
|
|
|
Purchase Order
|
Sheet:'PO'
Post a Purchase Order
|
|
|
|
|
Remote Purchase Order
|
Sheet:'PO Auth'
Post a Purchase Order filled in by remote subsidiary
Help article for Remote Purchase Order
|
|
|
|
|
Purchase Order with Authorisation
|
Sheet:'PO Auth'
Post a Purchase Order with Authorisation
|
|
|
|
|
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 Service Order
|
Sheet:'Sales Service Order'
Post a Sales Service Order which alows long text and bypasses dispatch routine
|
|
|
Sales Invoice (Invoice Ledger)
|
Sheet:'Sales Invoice INV LEDGER'
Post a Sales Invoice to invoice ledger
|
|
|
Sales Opening Balance
|
Sheet:'Sales Opening Balance'
Post a Sales Opening Balance
|
|
|
Sales Credit Note
|
Sheet:'Sales Credit Note'
Post a Sales Credit Note
|
|
|
Sales Invoice to Accounts Ledger
|
Sheet:'Sales Invoice'
Post a Sales Invoice to Accounts Ledger
|
|
|
|
|
File Import:Multiple Types from multiple CSV
|
Sheet:'Multiple Trans multi file'
Post a Multple Transaction Types from multiple CSV files
|
|
|
File Import:Multiple Types from single CSV
|
Sheet:'MultiType'
Post a Multple Transaction Types from single CSV files
|
|
|
File Import:Multiple Sales Invoice from CSV
|
Sheet:'Invoice'
Post a Multple Sales Invoices
|
|
|
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
|
|
|
|
|
Multiple Purchase Invoices
|
Sheet:'PI'
Post a Multiple PI Transactions
|
|
|
|
|
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: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:Sales/Purchase Invoices with Project from CSV
|
Sheet:'Mixed'
Post a Sales/Purchase Invoices to Projects
|
|
|
|
|
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 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)
|
|
|
|
|
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
|
|
|
|
|
Import into Multiple Companies from CSV
|
Sheet:'Multi in one sheet'
Post a Multple Companies
|
Edit/Copy Transactions:
|
|
|
|
|
Edit Multiple Transactions SO/PO/SI/SQ/Proforma
|
Sheet:'EditTransMultiple'
Edit Multiple Transactions SO/PO/SI/SQ/Proforma
|
|
|
Copy Multiple Transactions SO/PO/SI/SQ/Proforma
|
Sheet:'CopyTransMultiple'
Copy Multiple Transactions SO/PO/SI/SQ/Proforma
|
|
|
Copy Single Transactions SO/PO/SI/SQ/Proforma
|
Sheet:'CopyTransSingle'
Copy Single Transactions SO/PO/SI/SQ/Proforma
|
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 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 Write Off
|
Sheet:'Write Off'
Issues Stock Free of Write Off
|
|
|
Stock Adjustment Goods Return
|
Sheet:'Goods Return'
Issues Stock Free of Goods Return
|
|
|
Stock Adjustment IN
|
Sheet:'IN'
Post a Stock adjustment to bring stock IN
|
|
|
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
|
Transaction Data - Purchase:
|
|
|
|
|
Cashbook Single Payment Pro
|
Sheet:'BP'
Posting a Cashbook Single Payment
|
|
|
Cashbook Single Receipt Pro
|
Sheet:'BR'
Posting a Cashbook Single Receipt
|
|
|
Cashbook Single Receipt with Tax specified on lines Pro
|
Sheet:'BR with Tax'
Posting a Cashbook Single Receipt with Tax specified on lines
|
|
|
Cashbook Multiples Receipts with Tax specified on lines Pro
|
Sheet:'BR Multiples with Tax'
Posting a Cashbook Multiples Receipts with Tax specified on lines
|
|
|
Cashbook Multiples Receipts + All on lines Pro
|
Sheet:'BR Multiples'
Posting a Cashbook Multiples Receipts + All on lines
|
|
|
Cashbook Multiples Receipts, Companies + All on lines Pro
|
Sheet:'BR Multiple Companies'
Posting a Cashbook Multiples Receipts, Companies + All on lines
|
|
|
|
|
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 - Contra CC/Dep to multiple Companies
|
Sheet:'NOM Multi Company'
Import a Contra Nominal Journal with CC/Dep specified in separate cells
Main page for Nominal Journal to Multiple Copanies
|
|
|
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
|