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 Information on System Setup.

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
	Did you know you can Print Transaction and automatically link it to spreadsheet? Click here for more information
	Did you know 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
	Did you know 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 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 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 Video : Create Quick order pads for Telesales operations
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.
Don't get extra stock lines from stock record	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 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 Video : Sales Quotes with revision history
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 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
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!

System Setup - Transaction Header Fields 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:

Exchequer IRIS Enterprise Cells Refs

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
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 Header Fields 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
IN/OUT Location	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
Daily Exchange rate	Exchange rate of transaction
Delivery Address 1...5	Transaction Delivery address lines 1...5

System Setup - Trans Header Cells3 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 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 Video : Sales Quotes with revision history
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

Exchequer IRIS Enterprise Printing example

After posting the user is prompted with:

Exchequer IRIS Enterprise Prining Conformation

System Setup - Other 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 Video : Sales Quotes with revision history
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 Video : Sales Quotes with revision history
POR Cheapest Supplier:
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. See Edit Picked Qty and other Fields on Transaction Line

Chain Posting

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.

Authorisation

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

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
	See: FAQ section of for more info on Authorisation Module

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.

Exchequer IRIS Enterprise Delete Markers

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

Did you know 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

[ More about Exchequer Software ] [ IRIS Enterprise Software ] [ WebRangle Linking ]

Last modified: May 18, 2009