PostTrans XL Visual Importer: Import IRIS Exchequer Enterprise Transactions
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:
- Quickly import data from external systems, Demo 1, Demo 2 in to Exchequer Enterprise
- Create Quick order pads for Telesales operations, including printing
- 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
- ADJ with Serial/Batch/Multi-Bins
The examples spreadsheets show the above in action. more information on PostTrans examples
Import IRIS Exchequer Enterprise Transactions Features:
Our Pension contributions are prepared at external sites in excel and we use PostTrans to import the contributions straight in to Exchequer Enterprise saving valuable time and effort." says Laurence Dobney of Caterpillar Pensions Trust Ltd.
Alternate Stock Code Database fields available for update:
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. more information on PostTrans examples
Select the “SIN2” spreadsheet, on the "Transaction Example" Example Spreadsheet.
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. 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
IRIS 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 "#BLANK" will import a line with NO description V6.7.5 FOR STOCK CODE LINE ONLY:
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) If left blank then will look up price for customer, WITH any discount applied. |
|
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 |
|
TLPurchaseUplift |
Double | Uplift on Purchase transaction (V6.2.12) |
|
ECService |
Boolean Yes/No | Turns on/off EC Service. See the "Trans Header Cells2" to map the Intra stat header fields. Although these will default to values similar to IRIS Enterprise would |
|
ECServiceStartDate |
Date | Start Date of EC Service |
|
ECServiceEndDate |
Date | End Date of EC Service |
|
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 |
|
SupplierSearch |
|
Allow column to search for Supplier, when using the search |