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)

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

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

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:

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.

 
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.

Timesheets  

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

 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.