PostTrans XL Visual Importer: Import Exchequer Enterprise Transactions
This PostTrans spreadsheet allows the user to easily Import almost any kind of transaction in to Exchequer Enterprise (NOM, PIN, SIN ,PQU ,SQU ,ADJ ,TSH ,SOR ,POR ,SRI, WOR). It uses a series of tags, placed in row 18, to identify the Transaction Line data to be Imported into Enterprise. These tags can be easily added to a spreadsheet using the 
button, on the PostTrans Toolbar. The data is read from row 20 onwards.
PostTrans Training School: Your 25 minute free personal training videos
We have developed a 25 minute training course to give you a complete understanding of how to create your own PostTrans templates to achieve your goals, using our example templates as a starting point.
Transaction based Demonstration Videos:
Please watch one of the videos below for a specific topic demonstration.
The examples template spreadsheets show the above in action. more information on PostTrans examples
In-Cell Searching:
The PRO version of PostTrans allows the user to easily lookup associated codes and values, from within Excel. See In-Cell Searching
Import Exchequer Enterprise Transactions Features:
Our Pension contributions are prepared at external sites in excel and we use PostTrans to import the contributions straight in to Exchequer Enterprise saving valuable time and effort." says Laurence Dobney of Caterpillar Pensions Trust Ltd.
Transaction fields available for update:
PostTrans reads Row 18, which contains a series of special “Tags”, these Tags specify the data in each column. A “Design” window is provided ( button on toolbar) to list the Tags available, double clicking on the tags lists writes the tag name to current cell. Column “A” should always have “TLPosted”, when the transaction is imported PostTrans will mark each line as having been imported. This stops the user importing data twice.
The other tags can be in any order, but some are required. “Description” is required and is used to detect the end of the data to import. Sales order in the above example.
Basically when PostTrans finds a blank description it stops importing data. Putting a “.” in the description makes PostTrans ignore the line altogether, this may be useful if you want it to skip lines which contain total etc.
In this example the actual transaction line data starts at row 20, row 19 is ignored and can be used to display column titles for the user.
Cell C3 has been mapped to the Transaction Type. Use the 
setup button, on the PostTrans toolbar, to access these settings. more information on system setup. If this was simply changes to SIN then PostTrans will post a Sales Invoice.
In this example many other header fields have been mapped to transaction header fields. Such as Transaction date, Due date, Your Ref, etc. All of the these fields can be overridden in the lien data, to allow many transaction to be imported in one go.
If we press press 
button, after a second or two a conformation window is displayed to show a summary of the data about to be imported into Exchequer:
After posting the transaction it is the responsibility of the user to check the transaction in Exchequer.
Posting this will result in:
The above example has the switch turned on, which ignores all lines with a zero qty. In our example only row 22 has a qty of 3, so we only have one line on the resulting transaction. This switch can be used to create a simple telesales list so user just enters a qty against the items to order.
Tags and General Design
Tags on Transaction based spreadsheets
PostTrans uses a series of tag, placed in row 18, to identify the data to be Imported/Extracted/Amended. The data is read from row 20 onwards, which allows you to put user defined descriptions in row 19. Note this row definition can be changed in system setup. more info on Importing Transaction
Detail about importing stock description in transactions
Tags on "Static data" import (Customer, Supplier, Stock, Discounts, etc)
PostTrans uses a series of tag, placed in row 1, to identify the data to be Imported/Extracted/Amended. The data is read from row 3 onwards, which allows you to put user defined descriptions in row 2. Note Cell A1 is used to identify the type of data contained in spreadsheet. For Example ACPOSTED tag is used to identify Customer/Supplier import.
Defaults Spreadsheet
In addition to the Tags, PostTrans also stores information in the "Defaults" spreadsheet, such as default settings and Enterprise company to import into.
All PostTrans Import templates must have a "Defaults" spreadsheet within the workbook.
The "Defaults" sheet is used to store the system settings for each PostTrans template.
Design Tips:
Here are some general design tips:
Template
- Save Transaction Templates as Excel Templates (.XLT). This protects the spreadsheet from changes and enforced the user to name the sheet when saving it
- Use one of PostTrans example spread sheets, which is a "best fit" to your requirements, and then customise that.
- While developing your template, save revisions so you can go back to aversion which worked if need be.
PostTrans
- PostTrans can Print, Fax, send XML or e-mail Transaction and automatically link it to spreadsheet? more about printing transactions
- One spread sheet can be made to activate many others using macros (we call chaining). So the user can fill in one sheet with information then press one button to create a stock item, create a new account, and then post a sales order. post multiple transaction using VBA code
- PostTrans provides extra functions, similar to OLE, to help with design of templates, such as VAT rounding. more about extra functions and code
Use Excel functions
- Use VLOOKUP, see:
Concise VLOOKUP example in Excel 2000 (for experts)
Use Excel Validation
- Use data validation lists, see:
Data Validation List in Excel 2010
Data Validation List Video in Excel 2010
Video Excel 2010 List select next list contents
Cell Formatting:
Format all as text
Generally format all cells as TEXT. This stops Excel dropping leading zeros and changing number format, but remember to apply format General to any cells containing formulas.
Date Fields:
Date fields should be formatted to "dd mmm yyyy", ie. 18 Mar 2005. Other formats cause problems since Excel seems to get confused between American and English date formats. Please test all sheets using a date of 1/10/2005, and make sure you get the desired results
Don't use format cells
Do not use Excel formatting to manipulate data, since PostTrans will read the data in a cell. For Example. If 2334, is formatted to look like 23-34, PostTrans will import 2334
Clear Transaction button: 
After an import, the user may press the [Clear Transaction] button, on the PostTrans Toolbar, to clear the contents of the template. PostTrans uses the markers in row 17 to clear down the transaction lines. “X” denotes clear contents; any other value is copied down the sheet. Note: This is also done automatically before reading in a Text file.
Importing Multiple Transactions from within one sheet :
Video: Quickly import multiple transactions, or transactions and payments, from external systems into Exchequer Enterprise
The "Other Transactions" Example shows this in action. more information on PostTrans examples
Select the “SIN2” spreadsheet, on the "Transaction Example" Example Spreadsheet.
We can see that in the tag row we are overriding the Transaction Header values using the Tags “THTransDate” and “THAccountCode”. In this case PostTrans reads down the lines and makes a note of the values of this column (others include “THAltRef” and “THYourRef”). If the values change on the next line, PostTrans treats it as the start of a new transaction.
In our example the first two lines are for the same account, but the Transaction dates are different so these are treated as different transactions. If the dates were the same it would have imported one transaction with two lines on it. The tag “THForceSeparateTrans” can be used to force separate transaction lines.
Post Back fields:
The sections on the right, in yellow are, “Post Back fields”. Being prefixed with “PB” denotes all these. PostTrans actually writes information back to these cells, such as “PBTLTotal” Line total and “PBAccountName” Account Name. Exchequer OLE could also be used to enhance the functionality.
Nominal Journals:
Demo Video: Create Nominal Journals
The "NOM" Example shows this in action. Click here for more information on PostTrans examples
This journal has Credit and Debit columns. “NOM2” show using a single column for Credits and Debits.
Timesheets:
Demo Video: Create remote Timesheets, using popup lookups to helkp enter Job, Employee and Rate codes
The "TimeSheets" Example shows this in action. more information on PostTrans examples
This imports timesheets into Exchequer Job Costing. This time we use tags beginning with “TSH” for fields specific to timesheets. In this example we have specified the charge out and cost charges. These are not required. Try deleting the contents of these cells and then press [Update Lookups] button. PostTrans will then fill in the values based on the Rate set-up in Exchequer.
Another file called “Timesheet.xls” is provided for entering a weekly summary. As the user moves the cursor through the sheet, they are prompted for the Job codes, etc. It uses lookup list (sheet called “Lookup”) to help the user enter the correct codes.
Back to Back Order :
Video: Sales Order with back to back Purchase Order (with auto pick when received)
This functionality posts a SOR, with a matching POR to order stock to for fill the sales order. But the two transactions are linked in Enterprise so when the stock is received the stock is picked on the Sales Order automatically, so is process for dispatch.
Restrictions/Limitations:
Note moving stock into a batch, where number already exists in DB, creates a duplicate entry. Including Stock Take.
Exchequer Enterprise Transaction Database fields available for update:
The following Tags are used to identify the corresponding fields in Exchequer Enterprise. Use the [Design] button (or Ctrl+Shift+D), on the PostTrans Toolbar, to access these tags. Click here for more Information on Designing templates.
|
Tag Name |
Data Type Length |
Description |
|
Lookups ON/OFF (Prof version): |
||
|
TransLookup |
Text |
Must be placed in cell A1 to enable the Code Lookups |
|
Required: |
||
|
TLPosted |
|
Returns 'POSTED OurRef' if Trans line has been posted and will be ignored if cell starts with 'POSTED' (required) |
|
Description |
Text 55 |
|
|
Line Amount: |
||
|
AmountCr |
Double |
Amount Credit for Nom's If "Ignore Zero Qty" is on in System Setup, then will skip line if zero (NOM's only) |
|
AmountDr |
Double |
Amount Debit for Nom's If "Ignore Zero Qty" is on in System Setup, then will skip line if zero (NOM's only) |
|
NetValue |
Double |
NettValue or Amount for each line (Price for 1 stock item) For ADJ's this is the Cost Price on the IN side of the ADJ Transaction line |
|
Amount |
Double |
NettValue or Amount for each line (Price for 1 stock item) If left blank then will look up price for customer, WITH any discount applied. |
|
Line Qty: |
||
|
QTY |
Double |
Qty of line (defaults to 1 if an amount is entered) Nom journals with Qty=0 will appear in Ledger If "Ignore Zero Qty" is on in System Setup, then will skip line if zero |
|
QtyMul |
Double |
Qty Multiplier |
|
ADJQtyIn |
Double |
Qty in |
|
ADJQtyOut |
Double |
Qty Out |
|
ADJBuild |
Boolean Yes/No |
Build BOM |
|
QtyPicked |
Quantity picked on an Order V5.7.6 |
|
|
QTYWrittenOff |
Quantity written off on an Order V5.7.6 |
|
|
Line Identifiers: |
||
|
StockCode |
Text 16 |
Stock code |
|
MultiStockLoc |
Text 3 |
Location of Stock |
|
GLCode |
Integer |
Nominal code |
|
Currency |
Integer |
For Nominal journals only. 0-29,1=Sterling |
|
CostCentre |
Text 3 |
Cost Centre |
|
Department |
Text 3 |
Department |
|
JobCode |
Text 10 |
Job Costing code |
|
AnalCode |
Text 10 |
Job Costing Analysis code |
|
NOMGLContra |
Integer |
Nominal code causes Contra line in NOM only |
ItemNo |
Text 3 |
Item No for NON Stock Systems |
|
Line VAT: |
||
|
VATAmount |
Double |
Vat Amount for Transaction (Note : This is validated before posting) |
|
VATCode |
Text 1 |
VAT Code for transaction (S,Z,E,1,2,3,4,5,6,7,8,9,A,D,T,X ) |
|
Line Other : |
||
|
Discount |
Double |
Discount applied to line (this depends on the 'Discount expressed as %' setting in set-up |
|
CostPrice |
Double |
Cost price for line |
|
Weight |
Double |
Weight of Line |
|
LineDate |
Date |
Transaction Line Date |
|
LineType |
Text 1 |
0 = Normal 1 = Freight etc |
|
TLExchangeRate |
Double |
Daily Exchange Rate for Line |
|
TLPurchaseUplift |
Double | Uplift on Purchase transaction (V6.2.12) |
|
ECService |
Boolean Yes/No | Turns on/off EC Service. See the "Trans Header Cells2" to map the Intra stat header fields. Although these will default to values similar to Enterprise would |
|
ECServiceStartDate |
Date | Start Date of EC Service |
|
ECServiceEndDate |
Date | End Date of EC Service |
|
Line Payment: |
||
|
PaymentLine |
Boolean Yes/No |
Set to 'Yes' to denote separate payment Line on SRI,PRI … |
|
PaymentLineAsWell |
Boolean Yes/No |
Set to 'Yes' to post matching payment for this line on SRI,PRI …should be created to match amount on current line |
|
PayRef |
Text 16 |
Cheque Ref. for Payment Line |
|
PaymentGLCode |
Integer |
Nominal code of bank account. ONLY USED WITH 'PaymentLineAsWell' |
|
PaymentChequeNumber |
Text 55 |
Payment Cheque used for reconciliation |
|
Line Special Controls: |
||
|
SkipLine |
Boolean Yes/No |
If yes then ignore line. Handy to ignore a blank description. Or use “.” In description. |
|
PostBlankDescription |
Text |
Post Transaction line with blank Description, use to override End Of File |
|
THForceSeparateTrans |
Text |
If not blank then force a new transaction from this line onwards. Useful if posting multiple transactions with same account and date, which would be imported as one transaction. In this case it would force a new transaction |
|
Line User Fields: |
||
|
TLUser1-4 |
Text 30 |
Transaction Line User1-4 |
|
TLUser5-10 |
Text 30 |
Transaction Line User5-10 (V6.9+) |
|
Line Timesheet Fields: |
||
|
TSHEmployeeCode |
Text 6 |
Employee Code (Job Costing). Ignores after “,” |
|
TSHRateCode |
Text 10 |
TimeSheet Rate code (Optional). Ignores after “,” |
|
TSHHours |
Double |
Timesheet hours |
|
TSHCostPerHour |
Double |
Not set to 'Yes' if line is not posted (used in conjunction with fields marked with '*') |
|
TSHChargeOut |
Double |
Timesheet charge out rate |
|
TSHChargeCurrency |
Integer |
Charge Currency for Job Costing Time Sheet 0-29,1=Sterling |
|
TSHWeekMonthRef |
Integer |
Timesheet Week/Month Header Reference |
|
OverideJobChargeOut |
Double |
|
|
Line Multi-Bins (Prof version): |
||
|
BinCodeList |
Text List |
Bin Code list, comma delimited If "Ignore Zero Qty" is on in System Setup, a value in this cell will be treated as a valid line if contains a value. This functionality allows for easy selection from Batch/Serial/Bin |
|
BinQtyList |
Double List |
Bin Qty List, to specify the qty for each bin optional |
|
BinSellByDateList |
Date List |
Date list, comma delimited |
|
BinPickPriorityList |
Text List |
Picking Priority list, comma delimited |
|
BinTagNoList |
Text List |
Tag no of item list, comma delimited |
|
BinUOM |
Text 10 |
Unit of measure text of all bins |
|
BinAutoPickMode |
Integer |
Default 0,Lock Bin 1, Quarantine Bin 2, Tag no 3, of all bins |
|
BinCapacityList |
Double List |
Capacity of Bin List, if not specified = 0 |
|
BinContraCode |
Text 10 | If a IN location is specified, in header on an ADJ via system settings, then will move stock IN to this Bin in location specified |
|
Line Serial/Batch (Prof version):
|
Note : On a ADJ the Serial/Batch number will be moved to location, if specified in header via system settings |
|
|
SBBinPosted |
Text |
Used to track the posting status Batch/Serial/Bin information. After posting the transactions, PostTrans will then process the Serial/Batch information using this column to stamp the status of posting process This tag must be included for any template which includes Batch/Serial/Multi-Bin numbers |
|
SBSerialNoList |
List |
Serials number List of Product, comma delimited If "Ignore Zero Qty" is on in System Setup, a value in this cell will be treated as a valid line if contains a value. This functionality allows for easy selection from Batch/Serial/Multi-Bin |
|
SBBatchNo |
Double |
Batch number If "Ignore Zero Qty" is on in System Setup, a value in this cell will be treated as a valid line if contains a value. This functionality allows for easy selection from Batch/Serial/Multi-Bin |
|
SBSellByDate |
Date |
Sell by date of all Serial/Batch entries |
|
SBBinNo |
Text 10 |
Bin number on Serial/Batch record |
|
SBBinActualFree |
Double |
Free Qty in Serial/Batch/Multi-Bin This tag must be included for any template which includes Batch/Serial/Multi-Bin numbers |
|
Transaction Override Header:
|
This section allows lines to override the Header Values of a transaction, thus these are used when importing multiple transactions from the line data. See video import multiple transactions, or transactions and payments, |
|
|
THTransDate |
Date |
Override Transaction Header Trans Date Value |
|
THUser1 |
Text 20 |
Override Transaction Header User1 Value |
|
THUser2 |
Text 20 |
Override Transaction Header User2 Value |
|
THUser3-4 |
Text 30 |
Transaction Header User3 Value |
|
THUser5-10 |
Text 30 |
Transaction Header User5-10 Value (V6.9+) |
|
THYourRef |
Text 10 |
Override Transaction Header Your Ref. Value |
|
THAltRef |
Text 20 |
Override Transaction Header Alt Ref. Value |
|
THDueDate |
Date |
Override Transaction Header Due Date Value |
|
THAccountCode |
Text 6 |
Account Code (a separate transaction will be posted for each discrete value) |
|
THJobCode |
Text 10 |
Transaction Header Job Costing code. Ignores after “,” |
|
THAnalCode |
Text 10 |
Transaction Header Job Costing Analysis code. Ignores after “,” |
|
THCurrency |
Integer |
Transaction Header 0-29,1=Sterling. Ignores after “,” |
|
THDaysDue |
Integer |
Day Due for invoice |
|
THYear |
Long |
Overide Year. Note : must specify Year in header to make PostTrans use Year specified e.g.1999 |
|
THPeriod |
Integer |
Overide Period. Note : must specify Period in header to make PostTrans use Period specified e.g.7 |
|
THDelAdd1 |
Text 30 |
Delivery Address line 1 |
|
THDelAdd2 |
Text 30 |
Delivery Address line 2 |
|
THDelAdd3 |
Text 30 |
Delivery Address line 3 |
|
THDelAdd4 |
Text 30 |
Delivery Address line 4 |
|
THDelAdd5 |
Text 30 |
Delivery Address line 5 |
THDelPostcode |
Text 30 |
Delivery Address Postcode |
|
THTag |
Long Integer |
Tag Number |
|
THTransType |
Text |
This will set the type of transaction, so SIN, PIN, SCR can be mixed on one sheet |
|
THStatus |
The Transaction status, 1 is Query... V5.7.6 |
|
Text |
This was added to add lines to Invoices and Orders
If this cell contains a value. PostTrans will attempt to find a transaction in daybook by the OurRef,YourRef,AltRef (set under "Payments" tab in system setup). Then edit the transaction and add an aditional line to it. V6.9+
Fields available are:AnalCode, CostCentre, CostPrice, Currency, Department, Description, Discount, GLCode, JobCode, LineDate, LineType, MultiStockLoc, NetValue/Amount, QTY, QtyMul, QtyPicked, QTYWrittenOff, Stockcode, TLUser1, TLUser2, TLUser3, TLUser4, TLUser5, TLUser6, TLUser7, TLUser8, TLUser9, TLUser10, VATAmount, VATCode, Weight
Tag PBTHAddMatch writes back the OurRef of transaction edited see settings see example "Transaction Add Lines"
Only for SIN,SOR,PIN,POR transactions |
|
|
THOLECode |
Text 6 |
Use this to change the company when importing transactions It is suggested to test the behavior of this functionality before using it in a LIVE |
|
Post Back information: |
||
|
PBTSHRateName |
Text |
Post Back Time Rate Name |
|
PBTSHEmployeeName |
Text |
Post Back Employee Name |
|
PBTSHTotalCost |
Text |
Post Back Timesheet line total cost |
|
PBTSHTotalCharge |
Text |
Post Back Timesheet line total charge |
|
PBCostCentreName |
Text |
Post Back Cost Centre Name |
|
PBDepartmentName |
Text |
Post Back Department Name |
|
PBTLTotal |
Text |
Post Back Transaction line total |
|
PBTLTotalVAT |
Text |
Post Back Transaction line total VAT for line |
|
PBTLVATAmount |
Text |
Post Back Transaction line VAT amount (used if you use import to calc VAT) |
|
PBVATRate |
Text |
Post Back VAT rate |
|
PBJobName |
Text |
Post Back Job Name |
|
PBAnalysis |
Text |
Post Back Analysis Name |
|
PBGLName |
Text |
Post Back GL Name |
|
PBCurrencyName |
Text |
Post Back Currency Name |
|
PBAccountName |
Text |
Post Back Account Name |
|
PBAccountCode |
Text |
Post Back Account Code |
|
PBCostPrice |
Text |
Post Back Sales Price |
|
PBSalesPrice |
Text |
Post Back Cost Price |
|
PBStockDesc1 |
Text |
Post Back Stock Discount line 1 |
|
PBStockDesc2 |
Text |
Post Back Stock Discount line 2 |
|
PBStockDesc3 |
Text |
Post Back Stock Discount line 3 |
|
PBStockDesc4 |
Text |
Post Back Stock Discount line 4 |
|
PBStockDesc5 |
Text |
Post Back Stock Discount line 5 |
|
PBStockDesc6 |
Text |
Post Back Stock Discount line 6 |
PBTHAddMatch |
Text |
Writes back the OurRef of transaction edited using THAddMatch |
|
PBStockDesc6 |
Text |
Post Back Stock Discount line 6 |
|
Notes: |
||
|
THGNotes |
Memo |
Transaction Notes chr$(10) or "<br>" gives new line |
|
Other: |
||
|
QtyWrittenOff |
||
|
QtyPicked |
||
|
B2B Updates: Available V4.2 Jan 2014 These tags allow stock records to be updated as a transaction is posted, for B2B transactions, if the value differs from value in Exchequer. Empty cells are ignored. |
||
|
StkPreferredSupp |
Text |
Update stock record with Preferred supplier |
|
StkReOrderPrice |
Double |
ReOrder Price on stock record |
|
StkReOrderCur |
Integer |
ReOrder Currency on stock record |
|
StkCostPrice |
Double |
Cost Price on stock record |
|
StkCostCur |
Integer |
Cost Currency on stock record |
|
StkUser1...10 |
Text |
Update Stock User1...10 |
|
Lookup Tags: |
||
|
CustomerSearch |
|
Allow column to search for Customer, when using the search Used inconjunction to the Lookup XLA |
|
SupplierSearch |
|
Allow column to search for Supplier, when using the search Used inconjunction to the Lookup XLA |
We have now been developing a version of PostTrans for Excel into Sage 50 or Sage 200. We have used our 18 years of experience to implement the best Sage 50 Import module, or Sage 200, available.