PostTrans XL Visual Importer: PostTrans Design
The following page, discusses the general design of the a PostTrans template spread sheet, and the various tabs in the PostTrans System settings
- Tags and General Design
- System Setup - General Tab
- System Setup - Trans Switches Tab
- System Setup - Conformation Tab
- System Setup - Default Values Tab
- System Setup - Transaction Header Tab
- System Setup - Transaction Header2 Tab
- System Setup - Transaction Header3 Tab
- System Setup - Printing Tab
- System Setup - Other Tab
- System Setup - Chain Posting Tab
- System Setup - Authorisation Tab
- System Setup - Payments Tab
- Deletion Markers to Clear Transactions, button on PostTrans Toolbar
- Problems importing into Excel .CSV files
- Extra Functions provided by PostTrans
- Importing many different types of data into Exchequer Enterprise
PostTrans Training Video:
What is PostTrans video and introduction training
The above video, which has several different chapters, explains how to:
- Import/amend customer records; the same principle is used for all other non-transactional data.
- Importing IRIS Exchequer Transactions
- Design of templates
- Installing PostTrans
Tip : Having completed the above video, you should have a clear understanding of PostTrans design and implementation.
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
Tags on other types of import
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 Exchequer Enterprise company to import into.
All PostTrans Import templates must have a "Defaults" spreadsheet within the workbook.
Design Tips
Here are some general design tips:
- Save Transaction Templates as Excel Templates (.XLT). This protects the spreadsheet from changes and enforced the user to name the sheet when saving it
- PostTrans can Print Transaction and automatically link it to spreadsheet? Click here for more information
- One spread sheet can be made to activate many others using macros. 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! Click here for more information
- PostTrans provides extra functions, similar to OLE, to help with design of templates, such as VAT rounding. Click here for more information
Cell Formatting
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
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
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
System Setup - General Tab -
button on PostTrans Toolbar
When the user presses the [Post Transaction] button, PostTrans uses the settings found in the “defaults” sheet, of the template, to import Transaction data into Exchequer. Although these settings are stored in the “defaults” sheet, pressing the [Setup] button, on the PostTrans Toolbar, allows easy access to all of the settings. You should never edit the “defaults” sheet directly.
When the user attempts to import for the first time PostTrans will force you to set the company to import into automatically.
|
Option |
Description |
Override with OLE CODE in Cell |
Use the OLE code specified in cell to determine the company PostTrans will import into. Also used by Authorise-e to get user. |
|
Transaction Tag Row |
This is the row where, when importing transactions, PostTrans will look for tags to identify Exchequer Enterprise fields |
|
Transaction Line Data start row |
This is the row where, when importing transactions, PostTrans will expect to find data |
|
Maximum Number of lines |
Maximum Number of lines, in spreadsheet PostTrans will read |
|
Delete Markers fount in row |
This is the row where, used to identify deletion markers. More on deletion markers |
System Setup - Trans Switches Tab -
button on PostTrans Toolbar

Under this tab, various settings can be altered when importing Transactions:
|
Option |
Description |
|
Use Account 'Alt Search Ref' to match accounts instead of code |
When ON PostTrans will convert account "Alt Ref" code to account codes when importing data |
|
Don't enforce Job codes in Job Code Cells |
If on then makes user fill in all job codes specified Turn on for timesheets, etc |
|
Manually Calc VAT (by-pass Exchequer VAT Verification |
VAT will be specified on spread sheet, and manually calc flag turn on for each transactions. Use with care! |
|
Allow Manual VAT adjustment buttons on Confirmation Window |
Displays buttons to allow user to adjust VAT by +/- 0.01 pence on confirmation window. This allows slight adjustment when importing from external systems which use different rounding rules.
Note it adjusts whatever VAT code has the highest value. |
|
Ignore 0 qty stock values on Transactions and Zero value on NOMs |
This allows a template containing a list of stock codes to be used. The user can simply enter qty against stock codes they wish to order/stock adjust, PostTrans will ignore any lines with 0 qty. Used for Simple Order pad using excel Also works on NOMs for zero credit/Debit values and TSH hours = 0 Note: This is automatically turned on for ADJ's The "Quick Order Pad", "ADJ", "Timesheet" Examples shows this in action. Click here for more information on PostTrans examples |
|
Discount as % |
Any Discount specified, will be a percentage, else an amount |
|
Write back lookups and defaults |
Write back any values looked from Exchequer Enterprise database. For Example when importing Timesheets PostTrans will fill in Cost and Charge out rates in spreadsheet if not specified when [Update Lookups] button is pressed |
|
Default Stock location |
If Locations is turned on, then use this location if not specified on Transactions. |
to stop multiple lines being added from stock record, when using multiple lines on a transaction import Click here |
|
and break on "," |
If ticked then lines are broken up using "," in description line on Transactions Also effects Stock import too, so Desc1 becomes the 6 lines, separated by "," |
|
[Import CSV File] button Defaults: |
|
|
Import file directory |
When press [Import File] button, use this directory as default. Click here for more information |
|
Post Filename back to cell |
After import post filename to cell. This can be the Alt Ref field so the transaction can be linked to originating file |
|
File Delimited by |
File is delimited by "," |
|
Fixed Length Format (Specified in rows 200 onwards in Defaults Sheet) |
If file is fixed length format, format can be specified in "Default" sheet, row 200 onwards |
System Setup - Conformation Tab -
button on PostTrans Toolbar
Under this tab, conformation settings can be altered, which are shown when importing Transactions:

|
Option |
Description |
|
Before Posting Transaction: |
|
|
Show Confirmation before posting transaction |
Turn ON/OFF conformation window. You may want to suppress this window if importing data which contains multiple Transactions from an external system. Note : Credit Check will not be applied if this is turned OFF |
|
Show Cost Centre/Department break down |
Turn ON/OFF |
|
Show Job Costing break down |
Turn ON/OFF |
|
Show Stock Code break down |
Turn ON/OFF |
|
Apply Credit Check |
Warn user if over account over credit limit, on stop, see notes, closed. User can still import transaction but will be on "Credit hold" status Note : Will only check this if Confirmation is enabled |
Do NOT allow negative Stock |
If ON will stop user posting stock QTY which will make system go negative |
|
Allow overwrite transaction |
Allows user to amend transaction and repost into Exchequer When posted, PostTrans deletes all existing lines on Transaction, then adds the lines again Note : It does not clear notes lines or links when being overwritten Will not update TSH/ADJ/SRI/PPY/NOM transactions For single Transactions only
The "SQU" Example shows this in action. Click here for more information on PostTrans examples |
|
If Account Code on Transaction is un-recgonised: |
|
|
Allow User to change Account Code |
Allow User to change Account Code, from within the |
|
Allow user to create account called 'UNKOWN' |
If Account does not exist in Exchequer Enterprise, then create an account using specified account code, but give name "UNKOWN" |
|
Warn before creating new 'UNKOWN' Account |
Warn user before creating above account |
System Setup - Default Values Tab -
button on PostTrans Toolbar
When this window is closed PostTrans will validate default GL/Cost Centre codes entered in the setup window. If these are invalid please set the appropriate values in:

|
Option |
Description |
|
Operator |
When importing a Transaction, stamp with this operator name |
else in Cell Ref |
Enter here the cell where PostTrans will find Exchequer Operator name |
|
Currency |
When importing a Transaction, use this as default currency 1-89 |
|
Sales GL Code |
When importing a Transaction, use this default GL |
|
Cost of Sale GL Code |
When importing a Transaction, use this default GL |
|
Payment GL Code |
When importing a Transaction, use this default GL |
|
VAT code |
When importing a Transaction, use this default VAT code |
|
Due Days |
When importing a Transaction, if not specified then due date is Transaction date + |
|
Cost Centre |
When importing a Transaction, use this default |
|
Department |
When importing a Transaction, use this default |
Rules |
"PostTrans Default", "Stock, Account, PostTrans Default" or "Account, Stock, PostTrans Default" |
|
Status |
When importing a Transaction, give status 0=Post,1=On Query,2=Unitl Aloc,3=Authorise |
|
Default Stock location |
If Locations is turned on, then use this location if not specified on Transactions. |
Do NOT set costs automatically on SOR |
Read the label on the tin! (This options was removed in V6.3, as it fills in costs if not specified) |
System Setup - Transaction Header Tab -
button on PostTrans Toolbar
When importing Transactions, we have to set various Transaction Header fields as well as specifying transaction lines such as AccountCode, TransaDate, Duedate, etc. Shown in red below:
Where we position these fields are specified in the System Setup under the "Trans Header Cell Refs" and "Trans Header Cell2":
|
Option |
Description |
|
Transaction Type |
SIN,PIN,SOR,ADJ... |
|
Customer/Supplier Account: |
Customer/Supplier account code. Ignores anything after "," |
|
Transaction Date |
Date of Transaction |
|
Due Date |
Due Date if applicable |
|
Your Ref |
Subject or description of Fax/e-mail |
|
Alt Ref.
|
Alt Ref. or Description on TSH/NOM/ADJ Transactions
|
|
Unique
|
This tick box ensures a transaction has not been posted before by checking that the select ref has not been posted against the account before
|
Ctrl GL Code |
Transaction Control GL Code |
|
Year |
Normally set automatically |
|
Period |
Normally set automatically |
User 1-4 |
Transaction Header User fields 1-4 |
Timesheet Wk/Month |
Timesheet Wk no |
Timesheet Employee Code |
Timesheet Employee Code |
|
Job Code |
Default Job Code for all lines. |
|
Analysis code |
Default Analysis Code for all lines. Ignores anything after "," |
System Setup - Transaction Header2 Tab -
button on PostTrans Toolbar
Unwanted fields can be left blank. Note you must be careful when adding columns to the template. For example if we add a column between D and E then “Delivery address will no longer be in cell H3-H7 and Post Trans will see the first lines as “Add1:”.

|
Option |
Description |
|
Default Currency |
1-89 (if multi currency system) Ignores anything after "," |
|
e-mail, if email in cell |
2 cell references where the e-mail address will be found in spreadsheet |
|
When posting ADJ, post opposite side to this location When using Multi-Bins, by using Tag 'BinContraCode' you can specify the new Bin location to move stock IN When using Serial/Batch items, the serial/batch number will be moved IN to this location |
|
|
Discount Days |
Apply settlement discount after x days |
|
Discount % |
Apply settlement discount percentage |
|
Tag No |
Tag no of transaction |
|
Intra Stat ... |
Intra stat fields. Intra stat process defaults to N for normal |
|
Daily Exchange rate |
Exchange rate of transaction |
|
Delivery Address 1...5 |
Transaction Delivery address lines 1...5 |
System Setup - Transaction Header3 Tab -
button on PostTrans Toolbar
Notes can be easily added to a Transaction using these settings:

|
Option |
Description |
|
Line 1...25 |
These cell references allow up to 25 lines to specified which import General Notes. All of these lines word wrap, so each can be really long. (chr$(10) or "<br>" gives new line) Notes line 1 can be overridden on each line of transaction using in Tag "THGNotes" The "Other Transactions >> SOR" Example shows this in action. Click here for more information on PostTrans examples |
|
Import Blank Lines |
When OFF PostTrans skips lines which are blank. When ON PostTrans will import lines even if blank, this allows extra information to be posted with a transaction, and printed on an Exchequer Invoice/Order, since we always know the line number of the extra information. |
|
Transaction Header Alarmed Dated Notes |
|
|
Lines |
Line of alarm note The "Other Transactions >> SOR" Example shows this in action. Click here for more information on PostTrans examples |
|
User |
User for alarm. |
|
Date |
Date for Alarm. If nothing is specified then today's date will be used |
|
Links to external document: |
|
|
Link1/2 |
Filename of file including path (84 characters), which is posted as a linked document into IRIS Enterprise. |
|
Link1/2 Description |
Description of file |
|
Header Payment details for SRI |
|
|
Payment GL |
Payment line GL code Note : If this cell is has contents then attempts to use this information for payment line The "Other Transactions >> SRI" Example shows this in action. Click here for more information on PostTrans examples |
|
Cheque No |
Cheque No for payment line |
|
Pay Ref |
Payment ref, as appears in bank rec |
System Setup - Printing Tab -
button on PostTrans Toolbar
This is only available in the Professional version of PostTrans
Show me a video on Quick Order Pad/Telesales including Printing
This Tab under system setup, allows you print a transaction, after it is posted to IRIS Enterprise:

|
Option |
Description |
|
Always to ask to print after posting |
If ON, then PostTrans will prompt for printing after transaction has been posted, using IRIS Enterprise form The "SQU" Example shows this in action. Click here for more information on PostTrans examples |
|
or e-mail, if email in cell |
2 cell references where the e-mail address will be found in spreadsheet |
|
Fax, if fax no in cell |
2 cell references where the Fax number will be found in spreadsheet |
|
Tick "Print Hard copy" by default |
If ON, then defaults to printing a hard copy |
|
Don't Tick "e-mail" by default |
If ON, then printing is un-ticked by default |
|
Subject or Description |
Subject or description of Fax/e-mail |
|
Message |
Cell reference to body of e-mail text |
|
Override Print/email form |
Cell reference, to override IRIS Enterprise default form, when printing and e-mailing |
|
Print to XML writer, if C/S set to eBix |
Will write the Transactions to XML file, for IRIS eBis module to send to Customer or Supplier if set to "XML eBis" on the Customer/Supplier record |
|
or if email in cell |
If cell contains "YES" the create XML transaction. this overrides above if YES |
|
XML written to directory |
Write XML transactions to directory. This should be the same directory the eBis module is using to auto send. |
|
Override Fax form |
Cell reference, to override IRIS Enterprise default form, when faxing |
|
Printer User |
This needs to be a valid Exchequer user |
Thus with the above settings we can create a Sales order entry spreadsheet which faxes or e-mails, up to two different

After posting the user is prompted with:

System Setup - Other Tab -
button on PostTrans Toolbar

|
Option |
Description |
|
Auto save spreadsheet: |
Save before and after posting a Transaction |
|
Always force a save of sheet if .xlt |
If current spreadsheet is a template (.xlt) then save as: PATH & FILENAME & "_00.xls" before importing. This option can be used in conjunction with "Auto Link "PostTrans Template"", to link the transaction to current sheet. The "SQU" Example shows this in action. Click here for more information on PostTrans examples |
|
if file exists then ask to save revision |
If file already exists then ask user to save a revision. PATH & FILENAME & "_xx.xls" before importing. where xx is a count. |
|
PATH |
Points to cell containing path to save transaction |
|
FILENAME |
Points to cell containing Filename of sheet. Use a unique reference number |
| Auto Link "PostTrans Template" |
This automatically links current spreadsheet to Transaction when posted, under name "PostTrans Template". If the spreadsheet is saved as a .XLT (Template, thus protecting the design) then PostTrans will ask the user to save file before posting transactions. Note the filename, including path should not exceed 84 characters (limit in Exchequer). PostTrans will validate this. The "SQU" Example shows this in action. Click here for more information on PostTrans examples |
|
Lookup price for cheapest supplier from Alt DB |
When turned on this function looks up prices from Alternative DB for cheapest supplier The "Cheapest Supplier" Example shows this in action. Click here for more information on PostTrans examples More information on ordering from the cheapest supplier in IRIS Enterprise |
|
Job Costing Chargeout: |
|
|
Set Charge Out Rate to Cost Price for Labour |
If ticked, then will set Job costing Chargeout rate to NetValue for all lines with a Labour analysis code has Line Type 1 (Labour by default) |
|
Bank GL for Bank Recs: |
|
|
Bank Account GL |
Default Bank Account GL when importing Back reconciliation data. Click here for more information
The "GL and bank rec" Example shows this in action. Click here for more information on PostTrans examples |
|
Update Lines: |
|
|
Use LineNo instead of StockCode |
Normally the update lines procedure updates lines by matching by Stockcode, when on it will match by LineNo. |
System Setup - Chain Posting Tab -
button on PostTrans Toolbar
This allows the developer to chain post two PostTrans sheets, having pressed the [Post Transaction] button.
"Transacton BackToBack ..." examples demo this

|
Option |
Description |
|
Chain Postings |
|
|
Warning Validation |
If the cell contains text, then PostTrans displays the text as message. The contents of the cell must be cleared before posting can be made. The text in cell can be the result of a formula. |
|
Sheet to Post Next |
The name of the next sheet to post, after attempting the first one. The first sheet must be posted before posting the second sheet. |
|
Write OurRef after Posting |
After posting PostTrans writes back the OurRef back to this cell. This allows the designer to use this ref when posting POR's, to allow "Auto picked when purchase order received" |
|
And add sideways drill link |
|
|
Other Transaction Ref |
This links a POR to the SOR transaction ref. Thus linking in the sideways drill |
|
Name to put against |
Name put against the sideways linking |
Auto picked when purchase order received |
Sets the Auto pick when the POR is received. Note : This function has to match off stock codes in SOR & POR. To allow duplicate stock codes in a Transaction, it also uses TLUser3 to match off relates lines in SOR -> POR. So map something unique to TLUser3, e.g. Line number, which is mapped on the SOR and POR. |
Update Another Transaction after Posting |
|
if cell x has OurRef in it |
This allows a previously posted transaction, to have the referance of the transaction just posted, put in THYourRef, THAltRef, THUser 1-4. Note: THYourRef, THAltRef cannot be updated if Trasaction is a SRI, since it is allocated and the COM Toolkit does not allow update. Any attempt to update these fields will be ignored. |
Save In |
Where to put on transaction |
System Setup - Authorisation Tab -
button on PostTrans Toolbar
PostTrans will respect the IRIS Enterprise Authoris-e™ module. An additional download is required, see download page for download
The cell ref, under tab “Tran Defaults”, “Or in cell ref” cell ref is used to supply the Operator/Username name for requesting the Authorisation/Floor limit.
Note: PostTrans cannot submit the Transaction to the Authoris-e™ module. Therefore any that need submitting, to the Authoris-e™ module, are imported "On Query". A user should then submit all transactions by highlighting the transaction, then press the [Authorise] button.
Holding down ALT, at the start of a posting will show the Authorisation/Floor limit, retrieved from IRIS Enterprise. If not then the DLL is not in correct place.
How PostTrans Authorisation works

|
Option |
Description |
|
Allow printing of unauthorised transactions |
Allow printing of unauthorised transactions |
|
POR Authorisation |
If set to cell ref, then get the Authorisation limit from cell* |
|
POR Floor |
If set to cell ref, then get the Floor limit from cell* |
|
* Setting either of these, will stop PostTrans calling the authorisation module |
|
System Setup - Payments Tab -
button on PostTrans Toolbar
This allows the developer to import a list of payments, which are matched by THOurRef, THYourRef or THAltRef from an external system. PostTrans finds the SIN (imported by a previous process) by the external reference stored in THYourRef or THAltRef, creates a Sales Receipt and writes matching information. more about Match Payment import
Thus the original invoice is paid off, in IRIS without having to know the SIN ref, just the unique external reference.

|
Option |
Description |
|
Match By |
matched by THOurRef, THYourRef or THAltRef |
|
Bank GL Code |
Bank GL to put on SRC |
|
Control GL Code |
Deletion Markers
button on PostTrans Toolbar
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.
Problems importing into Excel .CSV files :
Problems can be experienced when importing text files in to Excel, dropping leading zeros and converting dates, when the user simple opens the .CSV file. To specify the exact format of a text file, thus removing this problem, go to the “Data >> Get External Data >> Import Text File” menu. This option will import virtually any kind of file, and allows the user to specify the exact format of each column within the file.
The [Import File] button has been provided to allow the user to import a file easer then the standard Excel functionality. Click here for more information
One spread sheet can be made to activate many others using macros. 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
Extra Functions provided by PostTrans:
PostTrans provides the following extra user-functions (similar to EntFuncs.xla):
Rounding Values using Exchequer Rounding
This function allows you to use the rounding as applied in Enterprise
Public Function ZEntRound(Value#, DecimalPlaces&) As Double
' Round value using Enterprise rounding to x Places
Extracting single lines from Memo fields
Function that extracts a single line of text from Memo fields
Public Function ZEntGetLine(TextMemo As Variant, LineNo%, Optional BreakChr$ = "")
' Extract a single line from text (line%)
' Where BreakChr$ is used to identify the end of the line
' Defaults the chr$(10)
For Example:
= ZEntGetLine (C4,1)
Will return line one of a memo field (Multiple line data) contained in cell C4.
Stripping Hidden Characters from text
PostTrans trims each text field before importing. It may be necessary to strip hidden characters out of text occasionally.
Public Function ZEntStripHidden(Text$) As String
' Strips hidden chars out of text (Anything less than Chr$(32)
Removing a code from Lookup value
It may be necessary to remove a code from a look value.
For Example:
AAA, MAIN Location
A function is provided to strip "AAA" from the above:
Public Function ZExtractCode(Text)
Importing many different types of data into Exchequer Enterprise :
One spread sheet can be made to activate many others using macros. 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!
For example, put the following code on button:
Private Sub CommandButton2_Click()
'Remember current sheet name
'Activate CUST sheet to import new customer
'Call PostTrans to create new customer
'Switch back to first sheet, and inform user
'Activate SOR sheet to import new customer
'Call PostTrans to create new SOR
'Switch back to first sheet, and inform user
End Sub
Posting multiple sheets using VBA code
The following post two sheets from a single button.
Function PostTransactions()
' Post Transactions in CustomerOrderPad and SupplierOrderPad sheet
Dim CustomerPostedOK%, SupplierPostedOK%
'Post Transaction
CustomerPostedOK = PostCustomerOrder()
SupplierPostedOK = PostSupplierOrder()
Sheets("CustomerOrderPad").Select
If CustomerPostedOK And SupplierPostedOK Then
MsgBox "Finished!"
Else
MsgBox "Error occured in ons sheet!"
End If
End Function
Function PostCustomerOrder()
Sheets("CustomerOrderPad").Select
Application.Run macro:="PostTransaction"
If HasOrderPosted() Then
'Have Posted OK
PostCustomerOrder = True
Else
MsgBox "Sales side has not posed due to an error. Please resolve and try again!"
End If
End Function
Function PostSupplierOrder()
Sheets("SupplierOrderPad").Select
Application.Run macro:="PostTransaction"
If HasOrderPosted() Then
'Have Posted OK
PostSupplierOrder = True
Else
MsgBox "Purchase side has not posed due to an error. Please resolve and try again!"
End If
End Function
Function HasOrderPosted() As Integer
' Return true if posted
HasOrderPosted = Left$(ActiveSheet.Range("A62").Value, 6) = "POSTED"
End Function