Import Payments and Receipts into Sage 50 Cashbook

Sage 50:Import Cashbook Payments/Receipts

This type of PostTrans import, imports Cashbook Payments/Receipts, which can be input manually, data from an ODBC connection or imported from CSV file.

Import Sage 200 Cashbook Payments/Receipts

Cashbook Import Features
Template Example
Common Features
Template Tags
Related Example Templates included with PostTrans

Import Cashbook Features in Sage 50

Sage 50 Cashbook Types

BP - Bank Payment
BR - Bank Receipt
CP - Cash Payment
CR - Cash Receipt
VP - Visa Credit Payment
VR - Visa Credit Receipts

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

Import Transaction Features in Sage 50

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 50 cashbook transactions (Examples shown below).

Advantages

Detailed conformation before transaction importation
Highlighted Validation of codes and data
Search for Nominal, Tax code, Bank Account, etc using our In-Cell Searching
Add formula, vba or macros to manipulate the imported data
Cannot Post Sage Cashbook Transaction twice. Writes POSTED in row, with cashbook transaction URN ref generated.
Easy importation from CSV file

Examples of importing Sage 50 Transactions

Posting Cashbook Payment 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).

Many examples are included with PostTrans for Sage 50. Press the Examples button on the PostTrans toolbar and type "Cashbook" into search box to list all cashbook examples.

Multiple Cashbook Transactions to Multiple Companies

Multiple Cashbook Transactions can be imported, to multiple Companies Pro by using the tag THBefSWCompany (Column B). This makes PostTrans switch company before processing the next row. These tags are easily inserted using the Tags button, see all tags listed at the bottom of page.

In the above example we have also overidden the Transaction Type, defined in the header E2, to enable Multi Cashbook Transaction Type importation in column C.

NOTE: all values after the "," in the In-Cell Searching columns are ingnored and would not have to be included if data is imports. More about this below.

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

The Path the Cursor takes can also be defined

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 50. 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

PostTrans will lookup the customer price, but does not support the Sage 50 pricing rounding options.
Does not apply the Sage Line 50 Additional Discount. But this can be calculated in sheet and posted using header Tag THAddDisc.

What else can I do?

Other Sage 50 Extract/Import types

Frequently Asked Questions?

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

PostTrans Tags which map to Sage 50

Not all of these tags/columns are needed. The table below lists the required and recommended:

Tag Name	Data Type	Description
Important Fields:
TLPosted	Text Req	Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row
Common:
TLProduct^	Text 30 XML	Product code. Not used on Service Type Transactions. In Cell Searching values in table ProdCodeNoDesc
TLDesc	Text 60 EOF XML	Product Description. PostTrans stops importing after finding 5 consecutive blank cells. How Descriptions are Imported
TLQty^	Double XML	Quantity, if Service Transaction then can be omitted, and lines with TLNett will have Qty 1. PRO – For Product Transactions - If switch 'ignore 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.
TLUnitPrice^	Currency XML	Unit Price for each item. If cell is blank then PostTrans will lookup customer price on change of Qty, or adding a product. If option is on to override the price, in System setup >> Lookups >> Switches, then the customers price will overwrite any value already in cell, on change of Qty. Price will be after customer discount. PRO - If multi-currency is ON will be in account currency. Unit Price Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLDueDate	Date XML	Due Date for Invoice, Delivery Date on Order, Expire Date on Quote. Overides the date set on Header in THDueDate
Financial Analysis:
TLNomCode^	Text 8 XML	Nominal Code, if not specified then uses default In Cell Searching values in table NomCode
TLDepartment^	Integer XML	Department, if not specified then uses default. Make sure the format of the cell is “General” else search may not work properly In Cell Searching values in table DeptCode
Discount:
TLDiscValue^	Double XML	Sales:Additional Discount Value to be applied to TLUnitPrice, Value should be specified in TLDiscValue OR TLDiscTotValue OR TLDiscPerc but not both cells. Note TLUnitPrice has already had any system discount applied on account record. See discount section of this page Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLDiscTotValue^	Double XML	Sales:Additional Discount Value to be applied to Line, Value should be specified in TLDiscValue OR TLDiscTotValue OR TLDiscPerc but not both cells. Note TLUnitPrice has already had any system discount applied on account record. See discount section of this page Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLDiscPerc^	Double XML	Sales:Additional Discount Percent to be applied to TLUnitPrice, Value should be specified in TLDiscValue OR TLDiscTotValue OR TLDiscPerc but not both cells. 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.
Line Totals:
TLTotNet	Currency Read	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	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.
TLTotIncluOfTax	Currency Pro XML	Line Total inclusive of Tax. PostTrans will calculate Tax and Unit Price back from Inclusive Tax Total for Line given Qty and overcome rounding problems. Use THNetDiscount to account for discount. Best choice when importing data as reduces rounding problems
Text:
TLComment1	Text 60 XML	Comment1 on line. If system settings ‘Large Product Description word wrap in Comment1/2’ is ON and ‘Read Product description from spreadsheet’ then will word wrap Description into these fields How Descriptions are Imported
TLComment2	Text 60 XML	Comment2
TLJobRef	Text 60 XML	Project Ref
TLLineInfo	Text 60 XML	Line Information, N/A for PI/PC
TLOrdRefTxt	Text 30 XML	Line Order Ref Text (only visible on SI/SO), ExtRef on PI/PC/SIAT/SA/SR/PR
TLOrdRefNo	Integer XML	Line Order Ref Number (only visible on SI/SO)
Tax:
TLTaxManAmount	Currency XML	TTax Amount, per item, if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. more about VAT
TLTaxManTotAmount	Currency XML	Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. more about VAT
TLTaxCode^	Integer XML	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 In Cell Searching values in table TaxCode
TLTaxAmount	Currency Read	Tax Amount. Calculated but not read as PostTrans will calculate this more about VAT
Stock price:
TLPriceCur1^	Currency Read Pro	Price in currency 1. Allows Order pad to list prices in Major currencies Sage 50 Quick Pad Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLPriceCur2^	Currency Read Pro	Price in currency 2. Allows Order pad to list prices in Major currencies Sage 50 Quick Pad Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLPriceCur3^	Currency Read Pro	Price in currency 3. Allows Order pad to list prices in Major currencies Sage 50 Quick Pad Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLPriceCur4^	Currency Read Pro	Price in currency 4. Allows Order pad to list prices in Major currencies Sage 50 Quick Pad Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLPriceCur5^	Currency Read Pro	Price in currency 5. Allows Order pad to list prices in Major currencies Sage 50 Quick Pad Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
Product Info (Written Back):
PrCostPrice	Double Read	Product Cost Price, if TLNet has ^, then it will contain the Cost Price. PRO - If multi-currency is ON will be in account currency.
PrQtyFree	Double Read	Product Qty free, updated when stock code entered
PrQtyInStock	Double Read	Product Qty In stock, updated when stock code entered
PrQtyAllocated	Double Read	Product Qty Allocated, updated when stock code entered
PrQtyOnOrder	Double Read	Product Qty On Order, updated when stock code entered
PrQtyReOrderLevel	Double Read	Quantity of stock at which new stock should be ordered.
PrQtyReOrder	Double Read	Product Qty ReOrder, updated when stock code entered
PrLocation	Text 16 Read	Product Location, updated when stock code entered
PrSuppPartNo	Text 16 Read	Product Supplier Part Number, updated when stock code entered
Other Product Info (Written Back):
PrCategory	Integer Read	Stock Category Number Lookup
PrType	Text 16 Read	Type of Stock
PrNominal	Text 8 Read	Nominal Code Lookup
PrPartNo	Text 16 Read	Supplier`s Part Number Lookup
PrSuppAccount	Text 8 Read	Supplier`s Account Reference Lookup
PrCust1	Text 60 Read	Custom Field 1 Lookup
PrCust2	Text 60 Read	Custom Field 2 Lookup
PrCust3	Text 60 Read	Custom Field 3 Lookup
Transaction Header:
THTransType^	Text 2 Head Chg XML	Transaction Type. Make sure the format of the cell is “General” else search may not work properly. Must be specified on each line. In Cell Searching values:Transaction Type SQ, Sales Quote SQI, Sales Quote Invoice SO, Sales Order SI, Sales Invoice SIAT, Sales Invoice Audit Trail SCAT, Sales Credit Audit Trail SSC, Sales Service Credit Note SSI, Sales Service Invoice SC, Sales Credit Note SR, Sales Reciept SA, Payment on Account PO, Purchase Order PI, Purchase Batch Inv PR, Purchase Receipt PA, Payment on Account PIA, Purchase Inv + Adj IN PC, Purchase Credit BP, Bank Payment BR, Bank Receipt CP, Cash Payment CR, Cash Receipt VP, Visa Credit Payment VR, Visa Credit Receipts
THAccCode^	Text 8 Head Chg XML	Transaction Account Code. Customer or Supplier depending on Transaction Type. In Cell Searching values in table THAccCode
THTransDate	Date Head Chg XML	Transaction Date
THDueDate	Date Head XML	Due Date for Delivery Date on Order, Expire Date on Quote. SO/PO only
THAccName	Text 60 Head XML	Customer Account Name
THAdd1	Text 60 Head XML	Customer Address 1
THAdd2	Text 60 Head XML	Customer Address 2
THAdd3	Text 60 Head XML	Customer Address 3
THAdd4	Text 60 Head XML	Customer Address 4
THAddPostcode^	Text 60 Head XML	Customer Postcode. 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
THContact	Text 30 Head XML	Customer Contact Name
THPhone	Text 30 Head XML	Customer/Supplier Telephone Number SO/PO only
THEORIno	Text 17	EORI no Sage V23 2017 onwards
Currency:
THCurrency^	Integer Head Pro XML	Currency, will be set after selecting an account, any prices then altered will be in that currency. In Cell Searching values in table CurrencyCode
THCurExch	Double Head Pro XML	Use System Exchange Rate, unless option changed in PostTrans system settins
THCurSymbol	Text 3 Head Read Pro	Currency Symbol
THCurCode	Text 3 Head Read Pro	Currency Code
Carriage:
THCarrCourierNo^	Integer HeadOnly XML	Carriage Courier Number. Make sure the format of the cell is “General” else search may not work properly Sage 50 Carriage Charge In Cell Searching values in table CourierCode
THCarrConsNo	Text 30 HeadOnly XML	Consign Number More information
THCarrNet^	Currency HeadOnly XML	Carriage Net Amount, else value in Sage Line 50 SOP Settings More information Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
THCarrTaxCode^	Integer HeadOnly XML	Carriage Tax Code, else uses Account Default. Make sure the format of the cell is “General” else search may not work properly More information In Cell Searching values in table TaxCode
THCarrDepartment^	Integer HeadOnly XML	Carriage Department, else value in Sage Line 50 SOP Settingst. Make sure the format of the cell is “General” else search may not work properly More information In Cell Searching values in table DeptCode
THCarrNomCode^	Text 8 HeadOnly XML	Carriage Nominal Code, else value in Sage Line 50 SOP Settings More information In Cell Searching values in table NomCode
Footer:
THGlobalNomCode^	Text 8 Head XML	Footer - Global Overide Nominal Code In Cell Searching values in table NomCode
THGlobalDep^	Integer Head XML	Footer - Overide Department In Cell Searching values in table DeptCode
THGlobalDetails	Text 60 Head XML	Footer - Overide Details
THGlobalTaxCode^	Integer Head XML	Footer - Overide Tax Code. In Cell Searching values in table TaxCode
Header Delivery:
THDelName	Text 60 Head XML	Delivery Address Name. Double click to change delivery address
THDelAdd1	Text 60 Head XML	Delivery Address Line 1. Double click to change delivery address
THDelAdd2	Text 60 Head XML	Delivery Address Line 2. Double click to change delivery address
THDelAdd3	Text 60 Head XML	Delivery Address Line 3. Double click to change delivery address
THDelAdd4	Text 60 Head XML	Delivery Address Line 4. Double click to change delivery address
THDelPostcode^	Text 60 Head XML	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	Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order. emailing Sage 50 transaction
Project (Pro):
TLProject^	Text 12 Pro XML	Project code where appropriate In Cell Searching values in table ProjectCode
THProject^	Text 12 HeadOnly Pro XML	Project code where appropriate In Cell Searching values in table ProjectCode
TLCostCode^	Text 8 Pro XML	Cost Code on Transaction line In Cell Searching values in table ProjectCostCode
THCostCode^	Text 8 HeadOnly Pro XML	Cost Code on Transaction line, but default can be set on header for greater flexability, where appropriate In Cell Searching values in table ProjectCostCode
Header Text:
THCustRef	Text 30 Head Chg XML	Customer`s Order Number
THOrderNo	Text 7 Head Chg XML	Order Number can be set on SI
THDetails	Text 60 Head XML	Details on PI
THDUNSRef	Text 9 Head XML	DUNS credit reference number
THNotes1	Text 60 Head XML	Notes 1
THNotes2	Text 60 Head XML	Notes 2
THNotes3	Text 60 Head XML	Notes 3
THPracSolRef	Text 60 Head XML	Sage Practice Solutions Reference
THTakenBy	Text 60 Head XML	Order Taken By
Header Discount:
THNetDiscount	Currency Head XML	Net Discount Value applied to whole transaction, as shown at the bottom of Sage Line 50 Order. Apply discount to whole Order/Invoice
THNetDiscDesc	Text 60 HeadOnly XML	Net Discount Description. If used in lines then is the sum of values Sage 50 overal disocount
Header Payment:
THPayAmt	Double Head XML	Amount Prepaid. Option in '`Setup` >> Switches’, can stop posting if UNDER or OVER paid. If used in lines, then posts sum of all lines Sage 50 Payment with Transaction
THPayRef	Text 30 Head XML	Payment Reference more about with payment
THPayType^	Text 2 Head XML	Payment Type (PAR/SR/SA). SI or SO should be SA, Post as Type SIAT (SI to Audit Trail) fro SR allocation more about with payment Sage V21 2015 onwards In Cell Searching values:Payment Type PAR, Payment already received SR, Allocate Payment To Invoice SA, Post as Payment on Accounts
THPayNom^	Integer Head XML	Payment Bank Nominal more about with payment In Cell Searching values in table NomCodeBank
Analysis:
THAnal1	Text Head XML	Transaction Analysis 1 Sage V22 2016 onwards
THAnal2	Text Head XML	Transaction Analysis 2 Sage V22 2016 onwards
THAnal3	Text Head XML	Transaction Analysis 3 Sage V22 2016 onwards
Header:Add Message:
THMessTop	Text HeadOnly	Add message at top of transaction using S3 Creating Message Top/Bottom
THMessBot1	Text HeadOnly	Add message at bottom of transaction using S3 Message Top/Bottom
THMessBot2	Text HeadOnly	Add message at bottom of transaction using S3 Message Top/Bottom
Line Special Functions:
TLSkipLine	Yes/No	If YES then skip this line
TLForceNewTrans	Yes/NoTF	If YES then force a new transaction
Header Printing:
THPrintEmail	Text Head Pro	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 Line 50 Order
THPrintEmailCC	Text HeadOnly	Email address to CC to. Multiples and be expressed using “,”. more info on printing
THPrintSubject	Text HeadOnly	Subject of email, which overrides that set in `System` >> Printing. more info on printing
THPrintBody	Text HeadOnly	Body of email, which overrides that set in `System` >> Printing. more info on printing
THPrintAttachment	Text HeadOnly	Add an attachment. Full Path. Multiples and be expressed using “,”. more info on printing
THPrintLayout^	Text HeadOnly	Sage Line 50 layout file for printing. Enter `SPACE` to search display list of form layouts more info on printing In Cell Searching values in table FormLayout
THPrintNoCopies	Integer Head Pro	Print x copies to printer. 0 for no print. If put on lines will then will print doc if print dialog is off more info on printing
Rounding:
THRoundToGross	Currency Head	Round Transaction to this value by either adding a line to add value, or using overall discount
THRoundToNom^	Text 8 Head	Rounding Nominal, if not specified then uses Sales Discount. In Cell Searching values in table NomCode
THRoundToDep^	Integer Head	Rounding Depatment In Cell Searching values in table DeptCode
Header:Before Posting:
THBefSWCompany^	Text 6 Head Chg Pro	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	If contains value, then this text is displayed, and import cannot take place until this text if clear. Thus you can your own formula, macro or VBA code to stop import until a certain condition. Validating Transaction before posting
THBefDisplayMessYN	Text 60 HeadOnly	If contains value, then this text is displayed. Asks Yes/No to continue Validating Transaction before posting
Header - After Posting:
THAftWriteRef	Text 60 HeadOnly Read	After posting write back transaction reference to this cell
THAftWriteAlocRef	Text 60 HeadOnly Read	SOP After posting write back stock allocation status, Full, Part
THAftWritePayRef	Text 60 HeadOnly Read	Writes back the reference of payment transaction, if posting a SA on Sales Order
THAftProcessSheet	Text 60 HeadOnly Pro	After posting this sheet, switch to work book with this name, and post that. Allows back to back purchase orders, or multiple company transfers. PRO Posting into multiple Sage Companies
THAftSwitchSheet	Text 60 HeadOnly Read Pro	After posting this sheet, switch to work book with this name PRO Posting into multiple Sage Companies
Header - After Save:
THAftSavePATH	Text 60 HeadOnly Read Pro	If THAftSavePATH and THAftSaveFilename specified, will save as PATH + ‘\YYYMM\’ + Filename + "_0000.xls". YYYMM can be overridden with value in THSaveSubDirectory cell. Saving template after posting
THAftSaveSubDir	Text 60 HeadOnly Read Pro	See THAftSavePATH more about saving
THAftSaveFilename	Text 60 HeadOnly Read Pro	See THAftSavePATH. Example ‘ABAP01’ will saveas ABAP01_0001.xls. more about saving
Import File:
THImportFullName	Text 500 HeadOnly	Full PATH and Filename of the file just imported using the `Import` button. This is required if you set the directory to move file after posting transaction. File Import instructions
THImportFileName	Text 500 HeadOnly	Filename of the file just imported using the `Import` button File Import instructions
After Account Selection:
CuName	Text 60 Read Rel	Account Name. PRO - Double clicking on this cell will drill down to Ledger.
CuBalance^	Currency Read Rel	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.
CuOutOvr30Days^	Currency Read Rel	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.
CuAvCredit^	Currency Read Rel	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.
CuCreditLimit^	Currency Read Rel	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.
CuOutDaysTxt^	Text Read Rel	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:
CuAdd1	Text 60 Read Rel	Account Address Line 1
CuAdd2	Text 60 Read Rel	Account Address Line 2
CuAdd3	Text 60 Read Rel	Account Address Line 3
CuAdd4	Text 60 Read Rel	Account Address Line 4
CuAddPostcode^	Text 60 Read Rel	Account Postcode. More about Postcode Address Lookup In Cell Searching values in table SimplyPostcode
CuCountryCode^	Text 2 Read Rel	Country Code Sage V22 2016 onwards In Cell Searching values in table CountryCode
CuVATRegNo	Text 20 Read Rel	VAT Registration Number
Contact Information:
CuContactName	Text 30 Read Rel	Contact Name
CuTradeContact	Text 30 Read Rel	Name of Trade Contact
CuPhone	Text 30 Read Rel	Telephone Number
CuPhone2	Text 30 Read Rel	Second Telephone Number
CuFax	Text 30 Read Rel	Fax Number
CuWWW	Text 255 Read Rel	WWW Address
e-mail Settings:
Cue-mail1	Text 255 Read Rel	E-mail Address
Cue-mail2	Text 255 Read Rel	E-mail Address 2
Cue-mail3	Text 255 Read Rel	E-mail Address 3
CuAnal1	Text 30 Read Rel	Analysis 1 Sage V22 2016 onwards
CuAnal2	Text 30 Read Rel	Analysis 2 Sage V22 2016 onwards
CuAnal3	Text 30 Read Rel	Analysis 3 Sage V22 2016 onwards
CuDiscRate	Double Read Rel	Discount Rate %
CuPriceList^	Text 8 Read Rel	Price List Reference In Cell Searching values in table PriceList
CuNomCode^	Text 8 Read Rel	Default Nominal Code In Cell Searching values in table NomCode
CuTaxCode^	Integer Read Rel	Default Tax Code In Cell Searching values in table TaxCode
CuCurrency^	Integer Read Rel	Currency In Cell Searching values in table CurrencyCode
CuDepartment^	Text 2 Read Rel	Department Number In Cell Searching values in table DeptCode
CuPaymentDueDays	Integer Read Rel	Payment Due Days
CuTermsTxt	Text 30 Read Rel	Terms
CuStatus^	Integer Read Rel	Account Status In Cell Searching values in table StatusCode
CuTermsAgreed	Yes/No- Read Rel	Terms Agreed Flag
CuOnHold	Yes/No- Read Rel	Account On Hold
CuMemo	Memo Read Rel	Customer Notes
CuManager	Text 60 Read Rel	Name of Account Manager
Delivery Address:
CuDelAdd1	Text 60 Read Rel	Delivery Address Line 1
CuDelAdd2	Text 60 Read Rel	Delivery Address Line 2
CuDelAdd3	Text 60 Read Rel	Delivery Address Line 3
CuDelAdd4	Text 60 Read Rel	Delivery Address Line 4
CuDelPostcode	Text 60 Read Rel	Delivery Postcode Postcode Address Lookup
CuDelContactName	Text 30 Read Rel	Delivery Contact Name
CuDelFax	Text 30 Read Rel	Delivery Fax Number
CuDelName	Text 60 Read Rel	Delivery Name
CuDelPhone	Text 30 Read Rel	Delivery Telephone Number
Post Extra Line 1:
THExLineProduct1^	Text 30 Head	Product code. Not used on Service Type Transactions. For Extra Line In Cell Searching values in table ProdCodeNoDesc
TLExLineDesc1	Text 60	Product Description. PostTrans stops importing after finding 5 consecutive blank cells. How Descriptions are Imported
TLExLineQty1^	Double	Quantity For Extra Line (If obmitted set to 1) Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLExLineNet1^	Currency	Unit Price for each item For Extra Line Unit Price Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLExLineNomCode1^	Text 8	Nominal Code, if not specified then uses default from stock/account. For Extra Line In Cell Searching values in table NomCode
TLExLineDepart1^	Integer	Department, if not specified then uses default. Make sure the format of the cell is “General” else search may not work properly. For Extra Line In Cell Searching values in table DeptCode
TLExLineTaxCode1^	Integer	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 In Cell Searching values in table TaxCode
TLExLineTaxManTot1	Currency	Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line more about VAT
TLExTotIncluOfTax1	Currency Pro	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 Product record more about VAT
Post Extra Line 2:
THExLineProduct2^	Text 30 Head	Product code. Not used on Service Type Transactions. For Extra Line In Cell Searching values in table ProdCodeNoDesc
TLExLineDesc2	Text 60	Product Description. PostTrans stops importing after finding 5 consecutive blank cells. How Descriptions are Imported
TLExLineQty2^	Double	Quantity For Extra Line (If obmitted set to 1) Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLExLineNet2^	Currency	Unit Price for each item For Extra Line Unit Price Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLExLineNomCode2^	Text 8	Nominal Code, if not specified then uses default from stock. For Extra Line In Cell Searching values in table NomCode
TLExLineDepart2^	Integer	Department, if not specified then uses default. Make sure the format of the cell is “General” else search may not work properly. For Extra Line In Cell Searching values in table DeptCode
TLExLineTaxCode2^	Integer	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 In Cell Searching values in table TaxCode
TLExLineTaxManTot2	Currency Pro	Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line more about VAT
TLExTotIncluOfTax2	Currency	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 Product record more about VAT
Post Extra Line 3:
THExLineProduct3^	Text 30 Head	Product code. Not used on Service Type Transactions. For Extra Line In Cell Searching values in table ProdCodeNoDesc
TLExLineDesc3	Text 60	Product Description. PostTrans stops importing after finding 5 consecutive blank cells. How Descriptions are Imported
TLExLineQty3^	Double	Quantity For Extra Line (If obmitted set to 1) Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLExLineNet3^	Currency	Unit Price for each item For Extra Line Unit Price Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLExLineNomCode3^	Text 8	Nominal Code, if not specified then uses default from stock. For Extra Line In Cell Searching values in table NomCode
TLExLineDepart3^	Integer	Department, if not specified then uses default. Make sure the format of the cell is “General” else search may not work properly. For Extra Line In Cell Searching values in table DeptCode
TLExLineTaxCode3^	Integer	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 In Cell Searching values in table TaxCode
TLExLineTaxManTot3	Currency	Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line more about VAT
TLExTotIncluOfTax3	Currency Pro	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 Product record more about VAT
Post Extra Line 4:
THExLineProduct4^	Text 30 Head	Product code. Not used on Service Type Transactions. For Extra Line In Cell Searching values in table ProdCodeNoDesc
TLExLineDesc4	Text 60	Product Description. PostTrans stops importing after finding 5 consecutive blank cells. How Descriptions are Imported
TLExLineQty4^	Double	Quantity For Extra Line (If obmitted set to 1) Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLExLineNet4^	Currency	Unit Price for each item For Extra Line Unit Price Changes in this cell will recalc other cells. Delete ^ to disable this calculation.
TLExLineNomCode4^	Text 8	Nominal Code, if not specified then uses default from stock. For Extra Line In Cell Searching values in table NomCode
TLExLineDepart4^	Integer	Department, if not specified then uses default. Make sure the format of the cell is “General” else search may not work properly. For Extra Line In Cell Searching values in table DeptCode
TLExLineTaxCode4^	Integer	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 In Cell Searching values in table TaxCode
TLExLineTaxManTot4	Currency	Tax Amount if calculated manually. Use with Caution as you are responsible for calculating Tax correctly. For Extra Line more about VAT
TLExTotIncluOfTax4	Currency Pro	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 Product record more about VAT
TLDonFund^	Integer XML	Charity Fund Number In Cell Searching values in table CharityCode

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

XML = Un-attended PostTrans XML API for Sage 200

Head A header tag can be used in header section, before row 30, of the transaction template. Or in the rows data of data, below row 30, of the template like a traditional CSV importer.

With data type Yes/No fields, if cell starts with 'y' or 'Y' is taken as TRUE

Where ^ denotes a In Cell Code Search, delete ^ at end of tag, to turn off code search

All Text fields are trimmed (spaces before) by default. Add ' to the end of the tag name to stop this.

Related Example Sheets

You can easily access the example sheets by pressing Help button, on the PostTrans Toolbar to access these related examples, or downloading them from the links below.

To use the template, with Sage 50, install PostTrans. Download
Trial with Sage demo company. Purchase a month subscription for Live Sage Company. Price
Watch the On-line Training course on how to use and customise the templates.

Transaction - Sales:
Download Sage50OrderPad.xlsx
	Sales Order PAD Pro	Sheet:'Order PAD' Order Pad demonstation Main page for Posting to multiple Companies
Download Sage50trans.xlsx
	Sales Order	Sheet:'SO' Sales Order demonstation Main page for Posting to multiple Companies Help article for Posting to multiple Companies
	Sales Order with Payment	Sheet:'Payment' Posting a payment with an order Main page for Payment with Order/Invoice
	Auto saving template after posting Pro	Sheet:'Saving' Save transaction to directory with account code Main page for Saving template after posting
	Printing/e-mail Quote/Order after posting Pro	Sheet:'Printing' How to e-mail to specified user Main page for Printing Quote/Order
	Sales Service Invoice	Sheet:'Service SSI' Sales Service Invoice demonstation Help article for Sales Service Invoice
	Sales Service Credit Note	Sheet:'Service SSC' Sales Service Credit Note demonstation Help article for Sales Service Credit Note
Download Sage50SalesReceipts.xlsx
	Sales Receipts - Import Allocation by Referance (Extras Module)	Sheet:'Import Receipts' Import Sales Receipt and allocated to existing invoices
	Sales Receipts - Manual Allocation (Extras Module)	Sheet:'Manual Receipts' Extracts Ourstanding Invoices, enter payment figures and referance, `Import` to create Sr and allocate
Transaction - Purchase:
Download Sage50expense.xlsm
	Expenses + Mileage	Sheet:'Expense demo' Posting a Expenses + Mileage in as a Purchase invoice Main page for Help on this Sheet Help article for Help on this Sheet
Download Sage50ExpenseIn.xlsx
	Expenses iPhone/Android or Web via ExpenseIn	Sheet:'Import Expenses' Import expenses into Sage 50 via IPhone, Android etc. with authorisation Help article for Expenses iPhone/Android or Web via ExpenseIn
Download Sage50RemotePur.xlsx
	Remote Purchase Order	Sheet:'Orders' Purchase Order PAD for remote loactions to fill in, e-mail and import. Help article for Remote Purchase Order
Download Sage50transPur.xlsx
	Purchase Order	Sheet:'PO' Posting a Purchase Order Help article for Purchase Order
	Purchase Order with Authorisation	Sheet:'PO Auth' Posting a Purchase Order with authorisation
	Purchase Invoice with Project	Sheet:'PI' Posting a Purchase Invoice (Project in header) Help article for Purchase Invoice with Project
	Purchase Invoice Multiples + Job Costing	Sheet:'PI Multi' Posting a Purchase Invoice Help article for Purchase Invoice Multiples + Job Costing
	Multi Company PI Multiples + Job Costing	Sheet:'PI Multi Type Multi Company' Posting a Purchase Invoice into different companies
	Purchase Invoice AND adjust stock IN	Sheet:'PIA' Posting a Purchase Invoice, and then Adjust stock in all in one operation
Download Sage50transByCode.xlsm
	Multiple Transactions Called from VBA Button	Sheet:'Multiple Trans' Shows how you can import Multiple Transactions in one sheet like a convesional CSV importer Main page for Importing from file easily
Download Sage50trans.xlsx
	File Import CSV:Multiple Transactions	Sheet:'Multiple Trans' Shows how you can import Multiple Transactions in one sheet like a convesional CSV importer Main page for Importing from file easily Help article for Importing from file easily
	File Import CSV:Multiple Transactions from multiple files	Sheet:'Multiple Trans multi file' Shows how you can import Multiple Transactions in one sheet like a convesional CSV importer Main page for Importing from multiple files easily
Download Sage50FileImport.xlsx
	Import File:Customer and Multiple Orders from CSV	Sheet:'Order CSV' Contains two sheets. The first imports three customers, and then an order for each customer. Demonstrates how to import files easily and importing multiple transactions in one sheet
	Import File:Multiple Orders from CSV with Currency Pro	Sheet:'Order CSV with Currency Pro' Contains two sheets. The first imports three customers, and then an order for each customer in currencies. Demonstrates how to import files easily and importing multiple transactions in one sheet
	Import File:Simple Multiple Orders from CSV	Sheet:'Simple Orders from CSV' Contains two sheets. The first imports three customers, and then an order for each customer in currencies. Demonstrates how to import files easily and importing multiple transactions in one sheet
Download Sage50FileImportPaymentDD.xlsx
	SR with or without currency	Sheet:'SR' This imports Invoices SR transactions, optionally with currency, to Audit Trial
Download Sage50cashbook.xlsx
	Cashbook Multi Company and Type	Sheet:'Cashbook Multi Company and Type' This imports Invoices Cashbook Multi Company and Type
Download Sage50FileImportPaymentDD.xlsx
	SC Sales Credit with or without currency	Sheet:'SCAT' This imports Invoices SC transactions, optionally with currency, to Audit Trial
	SA with or without currency	Sheet:'SA' This imports Invoices SA transactions, optionally with currency, to Audit Trial
	PR with or without currency	Sheet:'PR' This imports Invoices PR transactions, optionally with currency, to Audit Trial
	Import File:Direct Debit payments SI+SR	Sheet:'Direct Debit SI with Payment' This imports Invoices SI with matching Recipts to Audit Trial
Download Sage50FileImportPayment.xlsx
	Import File:Orders from CSV with Payment, Rounding and Extra Lines	Sheet:'Order and payment CSV' This imports orders, and overcomes rounding issues with external system. It also shows how to add addition lines in each row.
Download Sage50FileImportPaymentDD.xlsx
	Import File:Direct Debit payments SI+SR with Currency	Sheet:'Direct Debit SI Paymen Currency' This imports Invoices SI with matching Recipts to Audit Trial with Currency
Download Sage50_MultiAndEmail.xlsm
	Import Multiple Invoices\Orders NO Print or Price Lookup	Sheet:'Simple Orders No lookups' Demos importing SI, Credits, Discount and Sales Orders. But after import e-mails the transaction or prints it
	Import Multiple Invoices\Orders and e-mail/Print	Sheet:'Simple Orders email and Print' Demos importing SI, Credits, Discount and Sales Orders. But after import e-mails the transaction or prints it
Download Sage50trans Multicompany.xlsx
	Posting to Multiple Companies Multi Types Pro	Sheet:'Multi in one sheet' Inter-company transactions by posting to multiple companies Main page for Posting to multiple Companies
Download Sage50FileImportPaymentDD.xlsx
	PA with or without currency	Sheet:'PA' This imports Invoices PA transactions, optionally with currency, to Audit Trial
Transaction - Sales/Purchase Design:
Download Sage50trans.xlsx
	Message at Top and Bottom of transaction	Sheet:'Message' Adding a message at top and Bottom of transaction by specifying the header of Sage Line 50 Transaction Main page for Creating text message top/bottom of transacton
	Shows Customer Price List on header	Sheet:'PriceList' Adding a tag to header to show the name of the customer price list after selecting the account
	Posting Order in Currency Pro	Sheet:'Currency' How to post to different Currencies Main page for Posting Order in Currency
	Carriage Charge on Order	Sheet:'Carriage' How to specify Carriage Charge on an Order Main page for Specifying Carriage on an Order
	Apply discount to whole transaction	Sheet:'Discount' How to apply discount to the whole Sagel Line 50 transaction Main page for Apply discount to whole transaction
	Copy formula down sheet	Sheet:'Margin' Copy formula down sheet, after pressing `Clear` button, and when populating transaction using the `Extract` button Main page for Clear transaction copy formula
	Validating Transaction before posting	Sheet:'Validation' Stop PostTrans importing a transaction by using a formula to show message Main page for Validating before posting
Download Sage50InterCompany1.xlsx
	Transaction - Inter Company Nominal in 1 sheet Pro
Download Sage50InterCompany2.xlsx
	Transaction - Inter Company in 2 sheets using SO/PO Pro	Sheet:'Sales' Post a Sales Order into one company and a matching Purchase Order in a second company
Download Sage50InterCompany1.xlsx
	Transaction - Inter Company in 1 sheet Pro
Download Sage50InterCompany3.xlsx
	Transaction - Inter Company in 2 sheets using SI/PIA Pro	Sheet:'Sales' Post a Sales Invoice into one company and a matching PIA (Purchase Invoice and Stock Adjust IN) in a second company
Cashbook:
Download Sage50cashbook.xlsx
	BP Cashbook Bank Payment with or without currency	Sheet:'BP Bank Payment' This imports Invoices BP transactions, optionally with currency, to Audit Trial
	BR Cashbook Bank Receipt with or without currency	Sheet:'BR Bank Receipt' This imports Invoices BR transactions, optionally with currency, to Audit Trial
	VP Cashbook Visa Credit Payment with or without currency	Sheet:'VP Visa Payment' This imports Invoices VP transactions, optionally with currency, to Audit Trial
	VR Cashbook Visa Credit Receipts with or without currency	Sheet:'VR Visa Credit' This imports Invoices VR transactions, optionally with currency, to Audit Trial
	CP Cashbook Cash Payment with or without currency	Sheet:'CP Cash Payment' This imports Invoices CP transactions, optionally with currency, to Audit Trial
	CR Cashbook Cash Receipt with or without currency	Sheet:'CR Cash Receipt' This imports Invoices CR transactions, optionally with currency, to Audit Trial
Transaction - Chaity:
Download Sage50Charity.xlsx
	Shows Customer Price List on header	Sheet:'Invoice' Importing an SI to Charitable Fund
Transaction - Product Adjustment:
Download Sage50Prod.xlsx
	Product Adjustment	Sheet:'Product ADJ' Product Adjustment sheet Main page for Product Adjustment Help article for Product Adjustment
	Product Adjustment Goods OUT Simple (Code + Qty)	Sheet:'Product ADJ GO' Product Adjustment sheet with referance to SO for Goods Out Main page for Product Adjustment
	Product Adjustment Simple (Code + Qty)	Sheet:'Product ADJ Simple' Product Adjustment sheet Main page for Product Adjustment