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 ...except WOR and batches). It uses a series of tags, placed in row 18, to identify the Transaction Line data to be Imported into IRIS Enterprise. These tags can be easily added to a spreadsheet using the [Design] button, on the PostTrans Toolbar. The data is read from row 20 onwards. These settings can be changed in system set-up. More Information on Design and Tags.

Demonstration Videos

This functionality allows the designer to: (Click on links to see demonstration video)

	Quickly import data from external systems, Demo 1, Demo 2 in to Exchequer Enterprise
	Create Quick order pads for Telesales operations, including printing
	Automate the entry of data entry of IRIS Enterprise data
	Create Nominal Journals
	Create Sales Orders
	Create remote Timesheets, using popup lookups
	Create Stock Adjustments, with Serial/Batch/Multi-Bins
	Stock Take for Serial/Batch/Multi-Bins
	Sales Quotes with revision history

PostTrans allows Serial/Batch and Multi-Bins on SIN, PIN, SOR, POR and ADJ Transactions

This functionality allows the designer to: (Click on links to see demonstration video)

ADJ with Serial/Batch/Multi-Bins

The "bins_serial_batch" Example shows this in action. Click here for more information on PostTrans examples

Transaction Lines

Exchequer IRIS Enterprise Transaction Lines

Row 18 contains a series of �Tags�, these Tags specify the data in each column (see back of manual for list). A �Design� window is provided ([Design] button) 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. If �*� is entered in the Description field, then PostTrans will lookup the stock description from Exchequer. PostTrans uses it to detect the end of an import. Basically when it finds a blank description it stops importing data. In this example cell C27 is blank so it will stop there. 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 (rows 18 and 20 were specified in the first page of system set-up).

Exchequer IRIS Enterprise Transaction Lines conformation Lets press [Post Transaction], 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:

Exchequer IRIS Enterprise Result Transaction Lines

Try altering one of the GL codes. Make it invalid and re-import the transaction. Yup may need to delete the �POSTED� entries in column �A�. PostTrans will inform you of the error and move the cursor to the position of the error.

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 with in one sheet

Show me integration with external systems video

Show me more Integration with external systems video

The "Other Transactions" Example shows this in action. Click here for more information on PostTrans examples

Select the �SIN2� spreadsheet, on the "Transaction Example" Example Spreadsheet.

Exchequer IRIS Enterprise Multiple Transactions

Exchequer IRIS Enterprise Multiple Transactions
This example Imports single lines as a whole transaction

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.

Purchase Payments from an external system (PPI)

Select the �PPI� spreadsheet, of the Example Spreadsheet.

Press the [Import File] button, and then press [OK] button. Then browse to the �PostTrans� directory (sub directory of Exchequer). Double click on �Trans_18061999.CSV� file. PostTrans will now read this file into row 20 onwards and rename the file to .OLD. The name of the file is written to cell C8 (Alt Ref field of the Transaction header).

This data has come from an external system, which produces its own payments. These are exported via the CSV file we have just opened and imported in to Exchequer for accounting purposes:

Again we have overridden the account code with �THAccountCode�. We now also use a tag called �PaymentLine� which denotes which line is the payment side of the transaction.

If the �PaymentLine� tag is not specified, then PostTrans will automatically calculate the payment side of the transaction and add it to the transaction. It only does this for �PPI� and �SRI� transactions.

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.

Exchequer IRIS Enterprise Nominal Transactions

Timesheets

The "TimeSheets" Example shows this in action. Click here for more information on PostTrans examples

Exchequer IRIS Enterprise Timesheet Transactions
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.

Restrictions/Limitations

Note moving stock into a batch, where number already exists in DB, creates a duplicate entry. Including Stock Take.

Does not lookup Credit status of account

Allows negative stock

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' if Trans line has been posted and will be ignored if 'POSTED' (required)
Description	Text 55	Description (required) Used to detect end of sheet, except when importing Serial/Batch/Bins. For Serial/Batch/Bins, StockCode or Serial/Batch/Bins code is used to detect end of sheet A "." in this cell will make PostTrans skip the line A "*" in this cell will get the description from IRIS Enterprise New feature:V6.0.202 FOR STOCK CODE LINE ONLY: If the Line goes over 55 chars then word wraps the text on transaction automatically If next line has no Qty or Value then will associate the text with stock line above Therefore it is possible to have extra long descriptions on a transaction!!! New switch: "Don't get extra stock lines from stock record" was also added, to stop multiple lines being added from stock record, so this should be ticked when using this new functionality Click here for more info FOR NON STOCK CODE ITEMS: Set up a description only stock item and push text in against that stock item
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)
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
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
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 �
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
PostBlankDescription	Text	Post Transaction with blank Description line, use to override End Of File
THForceSeparateTrans	Text	If not blank then force a new transaction from this line onwards
Line User Fields:
TLUser1	Text 30	Transaction Line User1
TLUser2	Text 30	Transaction Line User2
TLUser3	Text 30	Transaction Line User3
TLUser4	Text 30	Transaction Line User4
Line Timesheet Fields:
TSHEmployeeCode	Text 6	Employee Code (Job Costing)
TSHRateCode	Text 10	TimeSheet Rate code (Optional)
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	A
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 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:
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	Text 30	Transaction Header User3 Value
THUser4	Text 30	Transaction Header User4 Value
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
THAnalCode	Text 10	Transaction Header Job Costing Analysis code
THCurrency	Integer	Transaction Header 0-29,1=Sterling
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
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 THStatusTHStatus		The Transaction status, 1 is Query... V5.7.6
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
Notes:
THGNotes	Memo	Transaction Notes chr$(10) or "<br>" gives new line
Other:
QtyWrittenOff
QtyPicked
Lookup Tags:
CustomerSearch		Allow column to search for Customer, when using the search add-ons
SupplierSearch		Allow column to search for Supplier, when using the search add-ons

PostTrans: PostTrans for IRIS Enterprise Prices

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

Last modified: March 04, 2009