Copy Sage 200 Transactions in Excel

Copy Sage 200 Transactions

This function will allow the user to select Single/Multiple Purchase Order, Sales Order, Invoice (in Invoice Module), Pro Forma or Quote, optionally from a different Sage 200 company to create a copy.

Allowing back to back ordering in another company.

This requires the Extras module on top of the normal Standard or Pro import license

Demonstration Video

PostTrans for Sage 200 Copy Single Transactions SO/PO/SI/SQ/Proforma

	0:00	Intro
	0:30	Selecting the Sage 200 Companies
	1:05	Extracting Sage 200 Transaction to Copy into Second Company
	1:35	Edit Sage 200 Transaction in Excel
	2:00	Manipulate Data with the power of Excel formula
	2:30	Import the Sage 200 Copied Transaction

PostTrans for Sage 200 Copy Multiple Transactions SO/PO/SI/SQ/Proforma

	0:00	Intro
	0:13	Selecting the Sage 200 Companies
	1:45	Edit Sage 200 Transactions in Excel
	2:15	Manipulate Data with the power of Excel formula
	3:50	Edit Sage 200 Transaction Header data
	3:27	Import the Sage 200 Edited Transactions
	4:00	Import the Sage 200 Edited Transactions
	53:00	Extracting Sage 200 Transactions to copy

Copy Transaction Tags for Sage 200 Entity 'TransactionHeader'

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 OXU	Stock Code In Cell Searching values in table ProdCodeNoDesc
TLDesc	Text 60 EOF IOXU	Product Description. PostTrans stops importing after finding 5 consecutive blank cells. How Descriptions are Imported
TLQty^	Double 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 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 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.
Other:
TLDiscPerc^	Double 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 OXU	Location. Ignored if warehouses are off. DoubleClick to see Stock Levels In Cell Searching values in table Loc
TLReqDelDate	Date IOXU	Requested Delivery Date on Order, Expire Date on Quote. Overides the date set on Header in THDueDate
TLPromDate	Date SOXU	Date Promised. SOP Only
Nom Analysis:
TLNomCode^	Text 16 IOXU	Nominal Code, if not specified then uses default How Nom/CC/Dep are specified In Cell Searching values in table Nom
UOM:
TLQtyMul^	Double OX	Qty Multiplier set by unit of meaure 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.
TLCostPrice	Currency Read OXU	Stock Cost Price. Standard, Average Cost. If Serial Batch/FIFO then returns Average.
Line Type:
TLAddChType^	Text 20 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 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 OXU	Comment on Dispatch
TLPickListComment	Text 160 OXU	Comment on Picking List
Tax:
TLTaxCode^	Text 2 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
Serial Batch:
After Stock Selection:
Stock Supplier Record:
Important Header Fields:
THTransType^	Text 4 Head Chg 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
THAccCode^	Text 8 Head Chg 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 IOXU	Transaction Date
THDueDelDate	Date Head IOXU	Delivery Date on Order, Expire Date on Quote. PO it’s the Requested Date
THPromDelDate	Date Head IOXU	Promised Delivery date on SO,SQ, SP
Header Text:
THCustRef	Text 30 Head Chg IOXU	Suppliers/Customer`s Order Number, 10 chars long on PI/SI
THCustRef2	Text 30 Head Chg IXU	Customer`s Order Number, 10 chars long on PI/SI
THDefaultLoc^	Text 20 Head OX	Default Location for all entered lines. Ignored if warehouses are off In Cell Searching values in table Loc
THOrderPriority	Text 1 Head OXU	Order Priority A-Z
CASH Invoice Address:
THInvAdd1	Text 60 Head OXU	Invoice Add1 if Cash account
THInvAdd2	Text 60 Head OXU	Invoice Add2 if Cash account
THInvAdd3	Text 60 Head OXU	Invoice Add3 if Cash account
THInvAdd4	Text 60 Head OXU	Invoice Add4 if Cash account
THInvPostcode^	Text 60 Head 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 OXU	Invoice City if Cash account
THInvCounty	Text 60 Head OXU	Invoice County if Cash account
THInvCountry^	Text 60 Head Read 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
THCurExch	Double Head IOXU	Currency Exchange Rate. Normally uses System Exchange Rate, unless option changed in PostTrans system settins
Delivery Address:
THDelContact	Text 60 Head OXU	Delivery Address Contact Name. Double click to change delivery address
THPostalName	Text 60 Head OXU	Delivery PostalName
THDelAdd1	Text 60 Head OXU	Delivery Address Line 1. Double click to change delivery address. On PO is the direct delivery address
THDelAdd2	Text 60 Head OXU	Delivery Address Line 2. Double click to change delivery address
THDelAdd3	Text 60 Head OXU	Delivery Address Line 3. Double click to change delivery address. On PO is the direct delivery address
THDelAdd4	Text 60 Head OXU	Delivery Address Line 4. Double click to change delivery address
THDelPostcode^	Text 60 Head 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 OXU	Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order. emailing Sage 200 transaction
THDelTelNo	Text 60 Head OXU	Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order.
THDelFaxNo	Text 60 Head OXU	Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order.
Delivery Address Segmented Address:
THDelCounty	Text 60 Head OXU	Delivery Address County. Double click to change delivery address
THDelCountry^	Text 60 Head 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 OXU	Delivery Address City. Double click to change delivery address
Project (Pro):
Project on Line:
Header Discount:
Header Payment:
THPayAmt	Double Head 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 IOX	Payment Reference more about with payment
THPayMethod^	Text 30 Head IOX	Payment Method more about with payment In Cell Searching values in table PaymentMethod
Header:Add Message:
Additional Charge:
Header Printing:
Header:Before Posting:
Header - After Posting:
Header - After Save:
Import File:
After Account Selection:
Registered Address:
Contact Information:
Credit Dates:
Pricing:
Credit Control - Terms:
Contact Delivery Address:
Header Analysis:
THAnal1^	Text 60 Head 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 IOXU	SpareTxt1 also used in XML to allocate Sales Receipt information
THSpareTxt2	Text 60 Head IOXU	SpareTxt2
THSpareTxt3	Text 60 Head IOXU	SpareTxt3
THSpareBit1	Yes/NoTF Head IOXU	SpareBit1
THSpareBit2	Yes/NoTF Head IOXU	SpareBit2
THSpareBit3	Yes/NoTF Head IOXU	SpareBit3
THSpareNo1	Integer Head IOXU	SpareNo1
THSpareNo2	Integer Head IOXU	SpareNo2
THSpareNo3	Integer Head IOXU	SpareNo3
THSpareDate1	Date Head IOXU	SpareDate1
THSpareDate2	Date Head IOXU	SpareDate2
THSpareDate3	Date Head IOXU	SpareDate3
Line Analysis:
TLAnal1^	Text 60 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 IOXU	SpareTxt1
TLSpareTxt2	Text 60 IOXU	SpareTxt2
TLSpareTxt3	Text 60 IOXU	SpareTxt3
TLSpareBit1	Yes/NoTF IOXU	SpareBit1
TLSpareBit2	Yes/NoTF IOXU	SpareBit2
TLSpareBit3	Yes/NoTF IOXU	SpareBit3
TLSpareNo1	Integer IOXU	SpareNo1
TLSpareNo2	Integer IOXU	SpareNo2
TLSpareNo3	Integer IOXU	SpareNo3
TLSpareDate1	Date IOXU	SpareDate1
TLSpareDate2	Date IOXU	SpareDate2
TLSpareDate3	Date IOXU	SpareDate3
Ac Anal:
Post Extra Stock Line 1:
Post Extra Add Charge Line 1:
Post Extra Add Charge Line 2:
Post Extra Add Charge Line 3:
Post Extra Add Charge Line 4:
ObjectStore Mappings Lines:
ObjectStore Mappings:
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
TLEdCommand	Text Pro IOXU	X=Delete,C=Cancel for PO X=Delete,C=Cancel,L=Cancel create line,R=Cancel and reject SOR lines,B=Cancel create line and reject SOR lines

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

Pro = PostTrans Pro version needed to use this field Read = Read Only

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

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

O Tags for Orders only. I Tags for Invoices only

X Tags for EXtract Data. U Tags for Update Transaction field

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.