Menu

Transactions

XML API  /  XML API for Sage 200  /  Transactions

PostTrans XML API for Sage 200 - Transactions

The PostTrans XML API for Sage 200 will import many kinds of transactions using a common format for all Sage 200 Transaction types, which is we hope easy to understand.   The PostTrans XML API will lookup and missing data, such default nominals, Prices, etc if not given similar to entering a transaction in Sage 200.

For example, each transaction has a <THTransType> tag which denoted the type of transaction.   “SI” is a Sales invoice, “PI” is a purchase invoice, “PO” Purchase Order, “SO” Sales Order, “SIM” is an invoice which goes to the Sage 200 Invoicing module.   Thus any of the examples below can be simply edited from say a Sales invoice, to a Purchase invoice.  Note some fields are applicable to Sales and Purchase orders (and Sales Invoice module) these are listed in the table at the bottom of the page.

Click here for an overview of the XML API for Sage 200

Demonstration Videos of XML API

 
 
   PostTrans XML API for Sage 200 Sales Invoice
  0:00 Where to get help
  1:00 Where to import from
  1:40 Open example and walk through Sales Invoice XML
  4:50 Different line types
  6:50 Transaction Types
  6:50 Post Sales Invoice into Sage 200
  6:10 The proof
  7:00 How to resubmit after an error
 
 

 
 
   PostTrans XML API for Sage 200 Sales Order from External System
  0:00 How it imports
  1:00 Where to import from
  1:20 Open example and walk through Sales Order XML
  3:50 Transaction Types
  3:20 How to store External GUID
  6:50 Post Sales Order into Sage 200
  6:30 The proof
  7:20 Failed or Imported
  8:50 Finding the traceability
 
 
Transaction Types:
  • SQ,Sales Quote *
  • SP,Proforma *
  • SO,Sales Order *
  • SSO,Sales Service Order *
  • SI,Sales Invoice
  • SIM,Sales Invoice Module *
  • SCM, Sales Credit (Invoice module *)
  • SIA,Sales Invoice And Adj OUT
  • SSI,Sales Service Invoice
  • SC,Sales Credit
  • SOB,Sales Opening Bal
  • SOC,Sales Opening Bal Credit
  • SR,Sales Receipt
  • PO,Purchase Order *
  • PSO,Purchase Service Order *
  • PI,Purchase Invoice
  • PIA,Purchase Invoice and Adj OUT
  • PC,Purchase Credit
  • POB,Purchase Open Balance
  • POC,Purchase Open Balance

 * = This transaction type does not support Manual VAT or Inclusive of VAT amount Tags

Each Transaction requires account code to be defined in tag THAccCode, and a line with a text or value.

Sage 200 API Validation

After creating a Sage 200 Transaction, the XML file is moved to a processed folder.   Or it could have been downloaded from an SFTP site or IMAPI e-mail address.   If the process fails validation, then the error/errors are appended to the top of the file, and the file is moved to a Failed folder.  A user can then easily edit the Transaction XML file and resubmit to PostTrans XML API for Sage 200.

Rounding

Rounding can often be a problem with importing from an external system, since the external system may not be using “Bankers” rounding or calculating the VAT slightly wrong.   Below are a few examples of how you can either import a Net figure plus VAT amount from the external system or using Inclusive of VAT.   Inclusive of VAT does not currently work with Sales/Purchase Orders and the Invoice Module.  But does work with the normal SI/PI transactions

 

Example – 100 SIM Net Value.xml

<?xml version="1.0" ?>
<sage200Data>
   <settings>  <!-- Optional overide settings/mappings -->
      <companyCode>DEMO03</companyCode>
      <mappings>
         mappingReset // Clear all settings XML_API_mappings.xml
         SW_TLReadStockDescriptionFromXML=1  //If off all descriptions relating to stock come from stock record
      </mappings>
   </settings>
   
  <TransactionHeader>  
      <THTransType>SIM</THTransType>  <!-- Sage 200 Sales Invoice Module -->
      <THAccCode>TEST02</THAccCode>   <!-- Currency and Tax will be set by account -->
      <THTransDate>2017-02-20</THTransDate> <!-- Due date will be set automatically -->
      <THCustRef>12345677</THCustRef>
      <THCustRef2>Another Ref</THCustRef2>
      <THDefaultLoc>WAREHOUSE</THDefaultLoc> <!-- Can be overidden in each line -->
   
      <!-- Sage 200 allows the follwoing line types for Sales Orders and Invoice sent to Invoice module -->
      <!-- manual VAT and Nominal overides not allowed for these types -->       
      <TransactionLine>  <!-- Comment Line -->
         <TLDesc>Please find invoice for 3 items and work done.  Thanks :</TLDesc>
      </TransactionLine>   
      <TransactionLine>
         <TLStock>ABBuiltIn/15/21/2</TLStock>
         <TLQty>1</TLQty>
         <TLUnitPrice>250</TLUnitPrice>  <!-- Tax will be added to this based on account defaults -->
      </TransactionLine>
      <TransactionLine>
         <TLStock>ABFSG/12/20/2</TLStock>
         <TLQty>2</TLQty>
         <TLUnitPrice>150</TLUnitPrice>   
      </TransactionLine>      
      <TransactionLine>
         <!-- Sage 200 Text Line -->
         <TLDesc>Fitting 7hrs</TLDesc>
         <TLNomCode>31100/LON/VAL</TLNomCode>  <!-- Nominal Account -->
         <TLQty>7</TLQty>
         <TLUnitPrice>60</TLUnitPrice>   
      </TransactionLine>   
      <TransactionLine>
         <!-- Apply an additional charge, whihc is a list in Sage 200 defines nominal etc. -->
         <TLAddChType>Carriage</TLAddChType>
         <TLDesc>Delivery to your door</TLDesc>
         <TLUnitPrice>14.95</TLUnitPrice>   
      </TransactionLine>   
      <TransactionLine>  <!-- Sage 200 Comment Line -->
         <TLDesc>Please pay soon!!!</TLDesc>
      </TransactionLine>   
     </TransactionHeader>
</sage200Data>

Example – 105 SCM Credit Net Value.xml

<?xml version="1.0" ?>
<sage200Data>
   <settings>   <!-- Optional overide settings/mappings -->
      <companyCode>DEMO03</companyCode>
      <mappings>
         mappingReset // Clear all settings XML_API_mappings.xml
         SW_TLReadStockDescriptionFromXML=1  //If off all descriptions relating to stock come from stock record
      </mappings>
   </settings>
   
  <TransactionHeader>  
      <THTransType>SCM</THTransType>  <!-- Sage 200 Sales Credit to Invoice Module -->
      <THAccCode>TEST02</THAccCode>   <!-- Currency and Tax will be set by account -->
      <THTransDate>2017-02-20</THTransDate> <!-- Due date will be set automatically -->
      <THCustRef>12345677</THCustRef>
      <THCustRef2>Another Ref</THCustRef2>
      <THDefaultLoc>WAREHOUSE</THDefaultLoc> <!-- Can be overidden in each line -->
   
      <!-- Sage 200 allows the follwoing line types for Sales Orders and Invoice sent to Invoice module -->
      <TransactionLine>
         <TLStock>ABFSG/12/20/2</TLStock>
         <TLQty>2</TLQty>
         <TLUnitPrice>150</TLUnitPrice>   
      </TransactionLine>      
   
      <TransactionLine>  <!-- Comment Line -->
         <TLDesc>Please pay soon!!!</TLDesc>
      </TransactionLine>   
     </TransactionHeader>
</sage200Data>

Example – 110 SI Net Value +VAT.xml

<?xml version="1.0" ?>
<sage200Data> 
   <settings>  <!-- Optional overide settings/mappings -->
      <companyCode>DEMO03</companyCode>
      <mappings>
         mappingReset // Clear all settings XML_API_mappings.xml
         SW_TLReadStockDescriptionFromXML=1  //If off all descriptions relating to stock come from stock record
      </mappings>
   </settings>
   
  <TransactionHeader>  
      <THTransType>SI</THTransType>  <!-- Sage 200 Sales Invoice to ledger -->
      <THAccCode>TEST02</THAccCode>   <!-- Currency and Tax will be set by account -->
      <THTransDate>2017-02-20</THTransDate> <!-- Due date will be set automatically -->
      <THCustRef>12345677</THCustRef>
      <THCustRef2>Another Ref</THCustRef2>
      <THDefaultLoc>WAREHOUSE</THDefaultLoc> <!-- Can be overidden in each line -->
   
      <!-- Sage 200 Sales Invoice to ledger  --> 
      <TransactionLine>
         <TLDesc>Charges for work</TLDesc>
         <TLUnitPrice>200</TLUnitPrice>  <!-- Tax will be added to this based on account defaults -->
         <TLTaxCode>1</TLTaxCode>   <!-- Tax code for Standard rate -->
         <TLNomCode>31100/LON/VAL</TLNomCode>  <!-- Overide Nominal Account -->
      </TransactionLine>
            <TransactionLine>
         <TLDesc>Materials</TLDesc>
         <TLUnitPrice>100</TLUnitPrice>  <!-- Tax will be added to this based on account defaults -->
         <TLTaxCode>1</TLTaxCode>   <!-- Tax code for Standard rate -->
         <TLNomCode>31100/SAL/BES</TLNomCode>  <!-- Overide Nominal Account -->
      </TransactionLine>

     </TransactionHeader>
</sage200Data>

Example – 113 SI Net Value + Manual VAT.xml

<?xml version="1.0" ?>
<sage200Data>
   <settings>  <!-- Optional overide settings/mappings -->
      <companyCode>DEMO03</companyCode>
      <mappings>
         mappingReset // Clear all settings XML_API_mappings.xml
         SW_TLReadStockDescriptionFromXML=1  //If off all descriptions relating to stock come from stock record
      </mappings>
   </settings>
   
  <TransactionHeader>  
      <THTransType>SI</THTransType>  <!-- Sage 200 Sales Invoice to ledger -->
      <THAccCode>TEST02</THAccCode>   <!-- Currency and Tax will be set by account -->
      <THTransDate>2017-02-20</THTransDate> <!-- Due date will be set automatically -->
      <THCustRef>12345677</THCustRef>
      <THCustRef2>Another Ref</THCustRef2>
      <THDefaultLoc>WAREHOUSE</THDefaultLoc> <!-- Can be overidden in each line -->
   
      <!-- Sage 200 Sales Invoice to ledger are very simple 
         VAT rounding can often be a problem, this example overides normal calulations
         using TLTaxManTotAmount --> 
      <TransactionLine>
         <TLDesc>Charges for work</TLDesc>
         <TLUnitPrice>200</TLUnitPrice>  <!-- Tax will be added to this based on account defaults -->
         <TLTaxManTotAmount>41</TLTaxManTotAmount>  <!-- Overide Sage 200 Tax calculation -->
               <!-- Important that YOU have calulated this correctly -->
         <TLTaxCode>1</TLTaxCode>   <!-- Tax code for Standard rate -->
         <TLNomCode>31100/LON/VAL</TLNomCode>  <!-- Overide Nominal Account -->
      </TransactionLine>

     </TransactionHeader>
</sage200Data>

Example – 117 SI Inclusive Of VAT.xml

Rounding can be a problem when bringing in figures from an external system.  This overcomes such problems, but is only available for Invoices sent to financial ledger

<?xml version="1.0" ?>
<sage200Data>
   <settings>  <!-- Optional overide settings/mappings -->
      <companyCode>DEMO03</companyCode>
      <mappings>
         mappingReset // Clear all settings XML_API_mappings.xml
         SW_TLReadStockDescriptionFromXML=1  //If off all descriptions relating to stock come from stock record
      </mappings>
   </settings>
   
  <TransactionHeader>  
      <THTransType>SI</THTransType>  <!-- Sage 200 Sales Invoice to ledger -->
      <THAccCode>TEST02</THAccCode>   <!-- Currency and Tax will be set by account -->
      <THTransDate>2017-02-20</THTransDate> <!-- Due date will be set automatically -->
      <THCustRef>12345677</THCustRef>
      <THCustRef2>Another Ref</THCustRef2>
      <THDefaultLoc>WAREHOUSE</THDefaultLoc> <!-- Can be overidden in each line -->
   
      <!-- Sage 200 Sales Invoice to ledger are very simple 
         VAT rounding can often be a problem, this example calcs the Net and VAT from 
         Inclusive of VAT value         --> 
      <TransactionLine>
         <TLDesc>Charges for work</TLDesc>
         <TLTotIncluOfTax>240</TLTotIncluOfTax>  <!-- Gross amount  -->
         <TLTaxCode>1</TLTaxCode>   <!-- Tax code for Standard rate -->
         <TLNomCode>31100/LON/VAL</TLNomCode>  <!-- Overide Nominal Account -->
      </TransactionLine>

     </TransactionHeader>
</sage200Data>

Example – 150 SO Net Value with Delivery Address.xml 

<?xml version="1.0" ?>
<sage200Data>

   <settings>  <!-- Optional overide settings/mappings -->
      <companyCode>DEMO03</companyCode>
      <mappings>
         mappingReset // Clear all settings XML_API_mappings.xml
         SW_TLReadStockDescriptionFromXML=1  //If off all descriptions relating to stock come from stock record
      </mappings>
   </settings>
   
  <TransactionHeader>  
      <THTransType>SO</THTransType>  <!-- Sage 200 Sales Order -->
      <THAccCode>TEST02</THAccCode>   <!-- Currency and Tax will be set by account -->
      <THTransDate>2020-03-20</THTransDate> <!-- Due date will be set automatically -->
      <THCustRef>12345677</THCustRef>
      <THCustRef2>Another Ref</THCustRef2>
      <THDefaultLoc>WAREHOUSE</THDefaultLoc> <!-- Can be overidden in each line -->
      
      
      <THPostalName>Mr R.J SMith</THPostalName>  <!-- All Text 60 -->
      <THDelAdd1>34 appleby Rd</THDelAdd1>   
      <THDelAdd2>West Mead</THDelAdd2>  
      <THDelAdd3>Kingston</THDelAdd3>  
      <THDelAdd4>London</THDelAdd4>   
      <THDelPostcode>SG1 4YU</THDelPostcode>   
     
     <THDelContact>R.J SMith</THDelContact>  
     <THDelTelNo>0201286082</THDelTelNo>  
      <THDelEmail>ss@asExample.com</THDelEmail>   <!-- Text 255 -->
   
      <!-- Sage 200 allows the follwoing line types for Sales Orders and Invoice sent to Invoice module -->
      <!-- manual VAT and Nominal overides not allowed for these types -->       
      <TransactionLine>  <!-- Comment Line -->
         <TLDesc>Please find invoice for 3 items and work done.  Thanks :</TLDesc>
      </TransactionLine>   
      <TransactionLine>
         <TLStock>ABBuiltIn/15/21/2</TLStock>
         <TLQty>1</TLQty>
         <TLUnitPrice>250</TLUnitPrice>  <!-- Tax will be added to this based on account defaults -->
      </TransactionLine>
      <TransactionLine>
         <TLStock>ABFSG/12/20/2</TLStock>
         <TLQty>2</TLQty>
         <TLUnitPrice>150</TLUnitPrice>   
      </TransactionLine>      
      <TransactionLine>
         <!-- Sage 200 Text Line -->
         <TLDesc>Fitting 7hrs</TLDesc>
         <TLNomCode>31100/LON/VAL</TLNomCode>  <!-- Nominal Account -->
         <TLQty>7</TLQty>
         <TLUnitPrice>60</TLUnitPrice>   
      </TransactionLine>   
      <TransactionLine>
         <!-- Apply an additional charge, whihc is a list in sage 200 defines nominal etc. -->
         <TLAddChType>Carriage</TLAddChType>
         <TLDesc>Delivery to your door</TLDesc>
         <TLUnitPrice>14.95</TLUnitPrice>   
      </TransactionLine>   
      <TransactionLine>  <!-- Sage 200 Comment Line -->
         <TLDesc>Please pay soon!!!</TLDesc>
      </TransactionLine>   
     </TransactionHeader>
</sage200Data>

Example – 158 SO Net Value External Acc Code.xml

 

<?xml version="1.0" ?>
<sage200Data>

   <settings>  <!-- Optional overide settings/mappings -->
      <companyCode>DEMO03</companyCode>
      <mappings>
         mappingReset // Clear all settings XML_API_mappings.xml
         
         //Make system use and external GUID stored in Sage 200 user field on customer record called GUID, and refured to as GUID in XML
         mappingCustomerCodeCounter("_","CuSpareText1") // Use CuSpareText1 to store, and identify account code.  
         //CuRef/THAccCode will be a counter prefixed with "_", eg "_0000001" - MUST BE SPECIFIED
         //The XML Value of "GUID" will no identify the account
         //   Renamed by THAccCode=GUID and CuRef=GUID later
         //If auto generate account code is ON in Sage 200 then will be created by Sage 200
         //If not Counter is stored in CustomerCounter.xml in settings folder
         THAccCode=GUID
         CuRef=GUID
         
         CuCreditLimit,onlyOnCreate  //Do not set if account already exisits
         CuDefTaxCode,onlyOnCreate
         CuUseDefTax,onlyOnCreate
         CuCurrency,onlyOnCreate
      </mappings>
   </settings>
  
   <!-- Update  or Create customer identified by external ref "GUID" -->
     <Customer>  
         <GUID>123945688212532</GUID>
         <CuName>Comtek Accounts</CuName>
         <CuMainAdd1>Venture House</CuMainAdd1>
         <CuMainAdd2>Venture Court</CuMainAdd2>
         <CuMainAdd3>Bolness Rd.</CuMainAdd3>
         <CuMainAdd4>Wisbech</CuMainAdd4>
         <CuMainPostcode>PE13 2QL</CuMainPostcode>
         
         <CuSalutation>Mr</CuSalutation>
         <CuFirstName>Sam</CuFirstName>
         <CuMiddleName></CuMiddleName>
         <CuLastName>Smith</CuLastName>
         <CuMPhCo>+44</CuMPhCo>
         <CuMPhAr>1612</CuMPhAr>
         <CuMPhNo>4762377</CuMPhNo>
         <CuEmail1>ss@ss.com</CuEmail1>
         
         <CuCreditLimit>1000</CuCreditLimit>
         <CuCurrency>GBP</CuCurrency>   <!-- GBP,USD,... ISO 4217:2015 -->

         <CuDefTaxCode>1</CuDefTaxCode>  <!-- 0=0%,1=Standard Rate, etc.-->
         <CuUseDefTax>False</CuUseDefTax>
         <CuTaxRegCode>612 5749 32</CuTaxRegCode>  <!-- Will fail if not formated -->

         <CuAccOpened>2020-02-20</CuAccOpened>
         <CuTermsAgreed>True</CuTermsAgreed>
     </Customer>

     
  <TransactionHeader>  
      <THTransType>SO</THTransType>  <!-- Sage 200 Sales Order -->
      <GUID>123945688212532</GUID>    <!-- Lookup account in "CuSpareText1" on account record, and book order to this -->
            <!-- Currency and Tax will be set by account -->
      <THTransDate>2020-03-20</THTransDate> <!-- Due date will be set automatically -->
      <THCustRef>12345677</THCustRef>
      <THCustRef2>Another Ref</THCustRef2>
      <THDefaultLoc>WAREHOUSE</THDefaultLoc> <!-- Can be overidden in each line -->
      
   
      <!-- Sage 200 allows the follwoing line types for Sales Orders -->
      <!-- and Invoice Module -->       
      <TransactionLine>  <!-- Comment Line -->
         <TLDesc>Please find invoice for 3 items and work done.  Thanks :</TLDesc>
      </TransactionLine>   
      <TransactionLine>
         <TLStock>ABBuiltIn/15/21/2</TLStock>
         <TLQty>1</TLQty>
         <TLUnitPrice>250</TLUnitPrice>  <!-- Tax will be added to this based on account defaults -->
      </TransactionLine>
      <TransactionLine>
         <TLStock>ABFSG/12/20/2</TLStock>
         <TLQty>2</TLQty>
         <TLUnitPrice>150</TLUnitPrice>   
      </TransactionLine>      
      <TransactionLine>
         <!-- Text Line -->
         <TLDesc>Fitting 7hrs</TLDesc>
         <TLNomCode>31100/LON/VAL</TLNomCode>  <!-- Nominal Account -->
         <TLQty>7</TLQty>
         <TLUnitPrice>60</TLUnitPrice>   
      </TransactionLine>   
      <TransactionLine>
         <!-- Apply an additional charge, whihc is a list in sage 200 defines nominal etc. -->
         <TLAddChType>Carriage</TLAddChType>
         <TLDesc>Delivery to your door</TLDesc>
         <TLUnitPrice>14.95</TLUnitPrice>   
      </TransactionLine>   
      <TransactionLine>  <!-- Comment Line -->
         <TLDesc>Please pay soon!!!</TLDesc>
      </TransactionLine>   
     </TransactionHeader>
</sage200Data>

Example – 160 PO Net Value.xml

<?xml version="1.0" ?>
<sage200Data>

   <settings>  <!-- Optional overide settings/mappings -->
      <companyCode>DEMO03</companyCode>
      <mappings>
         mappingReset // Clear all settings XML_API_mappings.xml
         SW_TLReadStockDescriptionFromXML=1  //If off all descriptions relating to stock come from stock record
      </mappings>
   </settings>
   
  <TransactionHeader>  
      <THTransType>PO</THTransType>  <!-- Sage 200 Purchase Order -->
      <THAccCode>SUPPL02</THAccCode>   <!-- Currency and Tax will be set by account -->
      <THTransDate>2020-03-20</THTransDate> <!-- Due date will be set automatically -->
      <THCustRef>12345677</THCustRef>
      <THCustRef2>Another Ref</THCustRef2>
      <THDefaultLoc>WAREHOUSE</THDefaultLoc> <!-- Can be overidden in each line -->
      
      
      <THPostalName>Mr R.J SMith</THPostalName>  <!-- All Text 60 -->
      <THDelAdd1>34 appleby Rd</THDelAdd1>   
      <THDelAdd2>West Mead</THDelAdd2>  
      <THDelAdd3>Kingston</THDelAdd3>  
      <THDelAdd4>London</THDelAdd4>   
      <THDelPostcode>SG1 4YU</THDelPostcode>   
     
     <THDelContact>R.J SMith</THDelContact>  
     <THDelTelNo>0201286082</THDelTelNo>  
      <THDelEmail>ss@asExample.com</THDelEmail>   <!-- Text 255 -->
   
      <!-- Sage 200 allows the follwoing line types for Purchase Orders -->   
      <TransactionLine>  <!-- Comment Line -->
         <TLDesc>Please find invoice for 3 items and work done.  Thanks :</TLDesc>
      </TransactionLine>   
      <TransactionLine>
         <TLStock>ABBuiltIn/15/21/2</TLStock>
         <TLQty>1</TLQty>
         <TLUnitPrice>250</TLUnitPrice>  <!-- Tax will be added to this based on account defaults -->
      </TransactionLine>
      <TransactionLine>
         <TLStock>ABFSG/12/20/2</TLStock>
         <TLQty>2</TLQty>
         <TLUnitPrice>150</TLUnitPrice>   
      </TransactionLine>      
      <TransactionLine>
         <!-- Text Line -->
         <TLDesc>Fitting 7hrs</TLDesc>
         <TLNomCode>31100/LON/VAL</TLNomCode>  <!-- Nominal Account -->
         <TLQty>7</TLQty>
         <TLUnitPrice>60</TLUnitPrice>   
      </TransactionLine>   
      <TransactionLine>
         <!-- Apply an additional charge, whihc is a list in sage 200 defines nominal etc. -->
         <TLAddChType>Carriage</TLAddChType>
         <TLDesc>Delivery to your door</TLDesc>
         <TLUnitPrice>14.95</TLUnitPrice>   
      </TransactionLine>   
      <TransactionLine>  <!-- Comment Line -->
         <TLDesc>Please pay soon!!!</TLDesc>
      </TransactionLine>   
     </TransactionHeader>
</sage200Data>

XML API Tags for Sage 200 Entity 'TransactionHeader'

Tag Name

Data Type

Description

Important Fields:

TLStock^

Text 30 O

Stock Code
    In Cell Searching values in table ProdCodeNoDesc

TLDesc

Text 60 IO

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.

TLQty^

Double O

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 O

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.
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLUnitPrice^

Currency IO

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.
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLFulfilment^

Integer 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 O

Sales:Additional Discount Percent to be applied to TLUnitPrice. Note TLUnitPrice has already had any system discount applied on account record.
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLLoc^

Text 20 O

Location. Ignored if warehouses are off
    In Cell Searching values in table Loc

Nom Analysis:

TLNomCode^

Text 16 IO

Line Nominal, set to stock or account defaults if obmitted. Can be NOM/CC/DEP
    In Cell Searching values in table Nom

UOM:

TLQtyMul^

Double O

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 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:

TLCostPrice

Currency O

Stock Cost Price. Standard, Average Cost. If Serial Batch/FIFO then returns Average.

TLTotCost

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

TLTotIncluOfTax

Currency 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 O

Additional Charge Type, if not specified then set to "Carriage"
    In Cell Searching values in table OPAddCharge

TLLineType^

Text 2 O

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

TLPickListComment

Text 160 O

Comment on Picking List

TLDispatchListComment

Text 160 O

Comment on Dispatch

Tax:

TLTaxManTotAmount

Currency I

Tax Amount, for line, if calculated manually. Use with Caution as you are responsible for calculating Tax correctly.

TLTaxCode^

Text 2 IO

Tax Code:0=0%,1=Standard Rate, etc
    In Cell Searching values in table Tax

Serial Batch:

TLBatSerial^

Text 30 O

Batch Ref or for Serial then a comma seperated list or list with each on new line. SO only
    In Cell Searching values in table TranTLBatSerial

TLSellByDate

Date O

Sell by date of Batch

TLUseByDate

Date O

Use by date of Batch

TLBatSerRef

Text 30 O

Additional ref for Batch

Important Header Fields:

THTransType^

Text 3 Head IO

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
                  PO, Purchase Order
                  PSO, Purchase Service Order
                  PI, Purchase Invoice
                  PIA, Purchase Invoice and Adj OUT
                  PC, Purchase Credit
                  POB, Purchase Open Balance
                  POC, Purchase Open Balance

THManDocRef

Text 30 Head O

Manual ref if manual Reference in sage 200 is ON

THAccCode^

Text 8 Head IO

Transaction Account Code. Customer or Supplier depending on Transaction Type.
    In Cell Searching values in table THAccCode

THTransDate

Date Head IO

Transaction Date

THDueDelDate

Date Head IO

Delivery Date on Order, Expire Date on Quote.

THPromDelDate

Date Head IO

Promised Delivery date on SO,SQ, SP

THRoundToGross

Double Head 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 IO

Suppliers/Customer`s Order Number, 10 chars long on PI/SI

THCustRef2

Text 30 Head I

Customer`s Order Number, 10 chars long on PI/SI

THDefaultLoc^

Text 20 Head O

Default Location for all entered lines. Ignored if warehouses are off
    In Cell Searching values in table Loc

THOrderPriority

Text 1 Head O

Order Priority A-Z

CASH Invoice Address:

THInvName

Text 60 Head O

Invoice name if Cash account

THInvAdd1

Text 60 Head O

Invoice Add1 if Cash account

THInvAdd2

Text 60 Head O

Invoice Add2 if Cash account

THInvAdd3

Text 60 Head O

Invoice Add3 if Cash account

THInvAdd4

Text 60 Head O

Invoice Add4 if Cash account

THInvPostcode^

Text 60 Head O

Invoice Postcode if Cash account
    In Cell Searching values in table SimplyPostcode

CASH Segmented Address:

THInvCity

Text 60 Head O

Invoice City if Cash account

THInvCounty

Text 60 Head O

Invoice County if Cash account

THInvCountry^

Text 60 Head O

Invoice Country if Cash account
    In Cell Searching values in table CountryCode

Currency:

THCurExch

Double Head IO

Currency Exchange Rate. Normally uses System Exchange Rate, unless option changed in PostTrans system settins

THSetDisc

Double Head IO

Settlement Disc

THSetDays

Integer Head IO

Settlement Days

Delivery Address:

THDelContact

Text 60 Head O

Delivery Address Contact Name. Double click to change delivery address

THPostalName

Text 60 Head O

Delivery PostalName

THDelAdd1

Text 60 Head O

Delivery Address Line 1. Double click to change delivery address. On PO is the direct delivery address

THDelAdd2

Text 60 Head O

Delivery Address Line 2. Double click to change delivery address

THDelAdd3

Text 60 Head O

Delivery Address Line 3. Double click to change delivery address. On PO is the direct delivery address

THDelAdd4

Text 60 Head O

Delivery Address Line 4. Double click to change delivery address

THDelPostcode^

Text 60 Head O

Delivery Postcode. PRO -If Postcode Search is enabled in ‘Setup >> Lookup >> Address Lookup’ then enter Space or Space + Postcode to search for an address.
    In Cell Searching values in table SimplyPostcode

THDelEmail

Text 255 Head O

Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order.

THDelTelNo

Text 60 Head O

Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order.

THDelFaxNo

Text 60 Head O

Delivery email address. PRO - Can be mapped it e-mail Quote/Invoice or Order.

Delivery Address Segmented Address:

THDelCountry^

Text 60 Head O

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 O

Delivery Address City. Double click to change delivery address

THDelCounty

Text 60 Head O

Delivery Address County. Double click to change delivery address

Project on Line:

TLProject^

Text 20 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

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 IO

Cost Code, overides THCostCode
    In Cell Searching values in table ProjectCostCode

Header Discount:

Header Payment:

THPayAmt

Double Head IO

Amount Prepaid. If used in lines then Sum is posted. Option in `Setup >> Switches’, can stop posting if UNDER or OVER paid

THPayRef

Text 30 Head IO

Payment Reference

THBankAccList

Text HeadOnly

When posting Payments a List of Currency Code, Bank Account to use. E.g ‘GBP,1,EUR,2,USD,3’

THPayMethod^

Text 30 Head IO

Payment Method
    In Cell Searching values in table PaymentMethod

Header Analysis:

THAnal1

Text 60 Head IO

Header Analysis 1
    Sage V2015 onwards

THAnal2

Text 60 Head IO

Header Analysis 2
    Sage V2015 onwards

THAnal3

Text 60 Head IO

Header Analysis 3
    Sage V2015 onwards

THAnal4

Text 60 Head IO

Header Analysis 4
    Sage V2015 onwards

THAnal5

Text 60 Head IO

Header Analysis 5
    Sage V2015 onwards

THAnal6

Text 60 Head IO

Header Analysis 6
    Sage V2015 onwards

THAnal7

Text 60 Head IO

Header Analysis 7
    Sage V2015 onwards

THAnal8

Text 60 Head IO

Header Analysis 8
    Sage V2015 onwards

THAnal9

Text 60 Head IO

Header Analysis 9
    Sage V2015 onwards

THAnal10

Text 60 Head IO

Header Analysis 10
    Sage V2015 onwards

THAnal11

Text 60 Head IO

Header Analysis 11
    Sage V2015 onwards

THAnal12

Text 60 Head IO

Header Analysis 12
    Sage V2015 onwards

THAnal13

Text 60 Head IO

Header Analysis 13
    Sage V2015 onwards

THAnal14

Text 60 Head IO

Header Analysis 14
    Sage V2015 onwards

THAnal15

Text 60 Head IO

Header Analysis 15
    Sage V2015 onwards

THAnal16

Text 60 Head IO

Header Analysis 16
    Sage V2015 onwards

THAnal17

Text 60 Head IO

Header Analysis 17
    Sage V2015 onwards

THAnal18

Text 60 Head IO

Header Analysis 18
    Sage V2015 onwards

THAnal19

Text 60 Head IO

Header Analysis 19
    Sage V2015 onwards

THAnal20

Text 60 Head IO

Header Analysis 20
    Sage V2015 onwards

Spare:

THSpareTxt1

Text 60 Head IO

SpareTxt1

THSpareTxt2

Text 60 Head IO

SpareTxt2

THSpareTxt3

Text 60 Head IO

SpareTxt3

THSpareBit1

Yes/NoTF Head IO

SpareBit1

THSpareBit2

Yes/NoTF Head IO

SpareBit2

THSpareBit3

Yes/NoTF Head IO

SpareBit3

THSpareNo1

Integer Head IO

SpareNo1

THSpareNo2

Integer Head IO

SpareNo2

THSpareNo3

Integer Head IO

SpareNo3

THSpareDate1

Date Head IO

SpareDate1

THSpareDate2

Date Head IO

SpareDate2

THSpareDate3

Date Head IO

SpareDate3

Line Analysis:

TLAnal1

Text 60 IO

Header Analysis 1
    Sage V2015 onwards

TLAnal2

Text 60 IO

Header Analysis 2
    Sage V2015 onwards

TLAnal3

Text 60 IO

Header Analysis 3
    Sage V2015 onwards

TLAnal4

Text 60 IO

Header Analysis 4
    Sage V2015 onwards

TLAnal5

Text 60 IO

Header Analysis 5
    Sage V2015 onwards

TLAnal6

Text 60 IO

Header Analysis 6
    Sage V2015 onwards

TLAnal7

Text 60 IO

Header Analysis 7
    Sage V2015 onwards

TLAnal8

Text 60 IO

Header Analysis 8
    Sage V2015 onwards

TLAnal9

Text 60 IO

Header Analysis 9
    Sage V2015 onwards

TLAnal10

Text 60 IO

Header Analysis 10
    Sage V2015 onwards

TLAnal11

Text 60 IO

Header Analysis 11
    Sage V2015 onwards

TLAnal12

Text 60 IO

Header Analysis 12
    Sage V2015 onwards

TLAnal13

Text 60 IO

Header Analysis 13
    Sage V2015 onwards

TLAnal14

Text 60 IO

Header Analysis 14
    Sage V2015 onwards

TLAnal15

Text 60 IO

Header Analysis 15
    Sage V2015 onwards

TLAnal16

Text 60 IO

Header Analysis 16
    Sage V2015 onwards

TLAnal17

Text 60 IO

Header Analysis 17
    Sage V2015 onwards

TLAnal18

Text 60 IO

Header Analysis 18
    Sage V2015 onwards

TLAnal19

Text 60 IO

Header Analysis 19
    Sage V2015 onwards

TLAnal20

Text 60 IO

Header Analysis 20
    Sage V2015 onwards

Line Spare:

TLSpareTxt1

Text 60 IO

SpareTxt1

TLSpareTxt2

Text 60 IO

SpareTxt2

TLSpareTxt3

Text 60 IO

SpareTxt3

TLSpareBit1

Yes/NoTF IO

SpareBit1

TLSpareBit2

Yes/NoTF IO

SpareBit2

TLSpareBit3

Yes/NoTF IO

SpareBit3

TLSpareNo1

Integer IO

SpareNo1

TLSpareNo2

Integer IO

SpareNo2

TLSpareNo3

Integer IO

SpareNo3

TLSpareDate1

Date IO

SpareDate1

TLSpareDate2

Date IO

SpareDate2

TLSpareDate3

Date IO

SpareDate3

ObjectStore Mappings Lines:

ObjectStore Mappings:

THUser1

Text 30 Head IO

10 user defined fields which can be mapped to Sage ObjectStore Builder customisations

THUser2

Text 30 Head IO

User2

THUser3

Text 30 Head IO

User3

THUser4

Text 30 Head IO

User4

THUser5

Text 30 Head IO

User5

THUser6

Text 30 Head IO

User6

THUser7

Text 30 Head IO

User7

THUser8

Text 30 Head IO

User8

THUser9

Text 30 Head IO

User9

THUser10

Text 30 Head IO

User10

TLUser1

Text 30 IO

10 user defined fields which can be mapped to Sage ObjectStore Builder customisations for SO/PO Standard Lines and SI/PI lines

TLUser2

Text 30 IO

User2

TLUser3

Text 30 IO

User3

TLUser4

Text 30 IO

User4

TLUser5

Text 30 IO

User5

TLUser6

Text 30 IO

User6

TLUser7

Text 30 IO

User7

TLUser8

Text 30 IO

User8

TLUser9

Text 30 IO

User9

TLUser10

Text 30 IO

User10

TLUser11

Text 30 IO

10 user defined fields which can be mapped to Sage ObjectStore Builder customisations for SO/PO Free Text Lines

TLUser12

Text 30 IO

TLUser12

TLUser13

Text 30 IO

TLUser13

TLUser14

Text 30 IO

TLUser14

TLUser15

Text 30 IO

TLUser15

TLUser16

Text 30 IO

TLUser16

TLUser17

Text 30 IO

TLUser17

TLUser18

Text 30 IO

TLUser18

TLUser19

Text 30 IO

TLUser19

TLUser20

Text 30 IO

TLUser20

TLUser21

Text 30 IO

10 user defined fields which can be mapped to Sage ObjectStore Builder customisations for SO/PO Aditional Charge

TLUser22

Text 30 IO

TLUser22

TLUser23

Text 30 IO

TLUser23

TLUser24

Text 30 IO

TLUser24

TLUser25

Text 30 IO

TLUser25

TLUser26

Text 30 IO

TLUser26

TLUser27

Text 30 IO

TLUser27

TLUser28

Text 30 IO

TLUser28

TLUser29

Text 30 IO

TLUser29

TLUser30

Text 30 IO

TLUser30

TLUser31

Text 30 IO

10 user defined fields which can be mapped to Sage ObjectStore Builder customisations for SO/PO Comment Line

TLUser32

Text 30 IO

TLUser32

TLUser33

Text 30 IO

TLUser33

TLUser34

Text 30 IO

TLUser34

TLUser35

Text 30 IO

TLUser35

TLUser36

Text 30 IO

TLUser36

TLUser37

Text 30 IO

TLUser37

TLUser38

Text 30 IO

TLUser38

TLUser39

Text 30 IO

TLUser39

TLUser40

Text 30 IO

TLUser40

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

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

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

O  Tags for Orders only

I  Tags for Invoices only

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

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

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