Menu

Edit Sage 200 Transactions

DUE TO THE COMPLEXITY OF SAGE 200 EDITING TRANSACTIONS IS NOT SUPPORTED

This function will allow the user to select Single/Multiple Purchase Order, Sales Order, Purchase Order, Invoice (in Invoice Module), Pro Forma or Quote to edit if permissions/status allow.

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

 

Demonstration Video

 
 
   PostTrans for Sage 200 Edit Transactions
  0:00 Intro
  0:09 Opening Example Sheet
  0:23 Selecting the Sage 200 Company
  0:25 Extracting Sage 200 Transactions to Edit
  1:27 Edit Sage 200 Transaction in Excel
  2:00 Delete line
  2:30 Edit Transaction Heder
  3:20 Import Changes into Sage 200
  3:35 View In Sage 200 the data changed
 
 

Edit Transaction Tags for Sage 200 Entity 'TransactionHeader'

Tag Name

Data Type

Description

Important Fields:

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.

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:

Other Values:

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:

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

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:

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:

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

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:

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


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.