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

Exchequer Enterprise Transaction defaults

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

Exchequer IRIS Enterprise Cells

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

Exchequer IRIS Enterprise Cells ref mapping

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:
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
Link1/2	Filename of file including path (84 characters)
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

PostTrans Design

Tags on Transaction based spreadsheets

Tags on other types of import

Defaults Spreadsheet

Design Tips

Cell Formatting

System Setup - General button on PostTrans Toolbar

System Setup - Trans Switches button on PostTrans Toolbar

System Setup - Conformation button on PostTrans Toolbar

System Setup - Default Values button on PostTrans Toolbar

System Setup - Transaction Header Fields button on PostTrans Toolbar

System Setup - Transaction Header Fields button on PostTrans Toolbar

System Setup - Trans Header Cells3 button on PostTrans Toolbar

System Setup - Printing