Comtek Accounts : Exchequer Software Enterprise Reseller for Accounting, Telesales, CRM Goldmine, ACT, MicroSoft CRM, Exchequer Plug-ins and Addons
 

         Full list of Exchequer Add-ons
         Interactive Remote Support
 
 
 
 

         Import Bank Rec  

       Edit Line/Header in daybook 
         
 
 
 
 
 
 
 
 
 

IRIS Enterprise - Import Transactions using PostTrans

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

Exchequer IRIS Enterprise Multiple Transactions with payments


 

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