The
"bins_serial_batch" Example shows this in action. Click
here for more information on PostTrans examples
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).
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.
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.
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.
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.
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.
This
journal has Credit and Debit columns. “NOM2” show using a single
column for Credits and Debits.
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 |
|
|
|