Postcode Programming API  PostTrans XL Visual Importer: Import 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, WOR).   It uses a series of tags, placed in row 18, to identify the Transaction Line data to be Imported into Enterprise.   These tags can be easily added to a spreadsheet using the button, on the PostTrans Toolbar.  The data is read from row 20 onwards.  

  PostTrans Training School: Your 25 minute free personal training videos


We have developed a 25 minute training course to give you a complete understanding of how to create your own PostTrans templates to achieve your goals, using our example templates as a starting point.

  Transaction based Demonstration Videos:


Please watch one of the videos below for a specific topic demonstration. 

             Quickly import multiple transactions (3:19), or transactions and payments (2:36), from external systems

             Create Quick order pads for Telesales operations (4:12), including printing, faxing and e-mail

             Create Nominal Journals (0:43)

             Create Sales Order (2:34) and edit it after posting

             Sales Order with back to back Purchase Order (with auto pick when received)

             Create Works Orders (0:47)

             Create remote Timesheets (4:39), using popup lookups to helkp enter Job, Employee and Rate codes

             Create Stock Adjustments (1:35), with Serial/Batch/Multi-Bins (4:25)

             Stock Take for Serial/Batch/Multi-Bins

             Sales Quotes with revision history (5:33) using linked documents in Enterprise

             ADJ with Serial/Batch/Multi-Bins (4:25)

             Introduction to LIVE Lookups (3:50) allowing searching in templates

             LIVE Lookups Orderpad using telesales history (6:00)

             LIVE Lookups Orderpad using list defined in account discount matrix (4:28)

             Add to line to Transactions in Daybook, see "Add line to Transactions" in help

The examples template spreadsheets show the above in action.  more information on PostTrans examples



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


Transaction fields available for update:


Exchequer Enterprise Transaction Lines

PostTrans reads Row 18, which contains a series of special “Tags”, these Tags specify the data in each column.  A “Design” window is provided (Design Button button on toolbar) 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 and is used to detect the end of the data to import. Sales order in the above example.

Basically when PostTrans finds a blank description it stops importing data. 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 and can be used to display column titles for the user.

Cell C3 has been mapped to the Transaction Type.  Use the Setup Button setup button, on the PostTrans toolbar, to access these settings.  more information on system setup.  If this was simply changes to SIN then PostTrans will post a Sales Invoice.

In this example many other header fields have been mapped to transaction header fields.  Such as Transaction date, Due date, Your Ref, etc. All of the these fields can be overridden in the lien data, to allow many transaction to be imported in one go.

If we press press button, after a second or two a conformation window is displayed to show a summary of the data about to be imported into Exchequer:

Exchequer Enterprise Transaction Lines conformation

 

After posting the transaction it is the responsibility of the user to check the transaction in Exchequer.

Posting this will result in:

Transaction

The above example has the switch turned on, which ignores all lines with a zero qty. In our example only row 22 has a qty of 3, so we only have one line on the resulting transaction. This switch can be used to create a simple telesales list so user just enters a qty against the items to order.


Tags and General Design


Tags on Transaction based spreadsheets

PostTrans uses a series of tag, placed in row 18, to identify the data to be Imported/Extracted/Amended.  The data is read from row 20 onwards, which allows you to put user defined descriptions in row 19.   Note this row definition can be changed in system setup.  more info on Importing Transaction

Detail about importing stock description in transactions

Tags on "Static data" import (Customer, Supplier, Stock, Discounts, etc)

PostTrans uses a series of tag, placed in row 1, to identify the data to be Imported/Extracted/Amended.  The data is read from row 3 onwards, which allows you to put user defined descriptions in row 2.  Note Cell A1 is used to identify the type of data contained in spreadsheet.  For Example ACPOSTED tag is used to identify Customer/Supplier import.

Defaults Spreadsheet

In addition to the Tags, PostTrans also stores information in the "Defaults" spreadsheet, such as default settings and Enterprise company to import into.   

All PostTrans Import templates must have a "Defaults" spreadsheet within the workbook.  

The "Defaults" sheet is used to store the system settings for each PostTrans template.

Design Tips:


Here are some general design tips:

Template

  • Save Transaction Templates as Excel Templates (.XLT). This protects the spreadsheet from changes and enforced the user to name the sheet when saving it
  • Use one of PostTrans example spread sheets, which is a "best fit" to your requirements, and then customise that.
  • While developing your template, save revisions so you can go back to aversion which worked if need be.

PostTrans

  • PostTrans can Print, Fax, send XML or e-mail Transaction and automatically link it to spreadsheet? more about printing transactions
  • One spread sheet can be made to activate many others using macros (we call chaining). So the user can fill in one sheet with information then press one button to create a stock item, create a new account, and then post a sales order.  post multiple transaction using VBA code
  • PostTrans provides extra functions, similar to OLE, to help with design of templates, such as VAT rounding. more about extra functions and code

Use Excel functions

  • Use VLOOKUP, see:

Concise VLOOKUP example in Excel 2000 (for experts)

Very good VLOOKUP example

VLOOKUP Good video

Use Excel Validation

  • Use data validation lists, see:

Data Validation List in Excel 2010

Data Validation List Video in Excel 2010

Video Excel 2010 List select next list contents

 


Cell Formatting:


Format all as text

Generally format all cells as TEXT. This stops Excel dropping leading zeros and changing number format, but remember to apply format General to any cells containing formulas.

Date Fields:

Date fields should be formatted to "dd mmm yyyy", ie. 18 Mar 2005. Other formats cause problems since Excel seems to get confused between American and English date formats. Please test all sheets using a date of 1/10/2005, and make sure you get the desired results

Don't use format cells

Do not use Excel formatting to manipulate data, since PostTrans will read the data in a cell. For Example. If 2334, is formatted to look like 23-34, PostTrans will import 2334


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 within one sheet :


Video: Quickly import multiple transactions, or transactions and payments, from external systems into Exchequer Enterprise

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

Select the “SIN2” spreadsheet, on the "Transaction Example" Example Spreadsheet.

Exchequer Enterprise Multiple Transactions

 

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.


Nominal Journals:

Demo Video: Create 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 Enterprise Nominal Transactions

Timesheets:


Demo Video: Create remote Timesheets, using popup lookups to helkp enter Job, Employee and Rate codes

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


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


Back to Back Order :


Video: Sales Order with back to back Purchase Order (with auto pick when received)

This functionality posts a SOR, with a matching POR to order stock to for fill the sales order. But the two transactions are linked in Enterprise so when the stock is received the stock is picked on the Sales Order automatically, so is process for dispatch.


Restrictions/Limitations:


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


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 OurRef' if Trans line has been posted and will be ignored if cell starts with 'POSTED' (required)

Description

Text 55

Description (required) 

See extra notes on description

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

ItemNo

Text 3

Item No for NON Stock Systems

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 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 …should be created to match amount on current line

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.  Handy to ignore a blank description.  Or use “.” In description.

PostBlankDescription

Text

Post Transaction line with blank Description, use to override End Of File

THForceSeparateTrans

Text

If not blank then force a new transaction from this line onwards.  Useful if posting multiple transactions with same account and date, which would be imported as one transaction.   In this case it would force a new transaction

Line User Fields:

TLUser1-4

Text 30

Transaction Line User1-4

TLUser5-10

Text 30

Transaction Line User5-10 (V6.9+)

Line Timesheet Fields:

TSHEmployeeCode

Text 6

Employee Code (Job Costing).  Ignores after “,”

TSHRateCode

Text 10

TimeSheet Rate code (Optional).  Ignores after “,”

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

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.  After posting the transactions, PostTrans will then process the Serial/Batch information using this column to stamp the status of posting process

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:

 

This section allows lines to override the Header Values of a transaction, thus these are used when importing multiple transactions from the line data. See video import multiple transactions, or transactions and payments,

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-4

Text 30

Transaction Header User3 Value

THUser5-10

Text 30

Transaction Header User5-10 Value (V6.9+)

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.  Ignores after “,”

THAnalCode

Text 10

Transaction Header Job Costing Analysis code.  Ignores after “,”

THCurrency

Integer

Transaction Header 0-29,1=Sterling.  Ignores after “,”

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

THDelPostcode

Text 30

Delivery Address Postcode

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

The Transaction status, 1 is Query...   V5.7.6

THAddMatch

Text 

This was added to add lines to Invoices and Orders

 

If this cell contains a value.  PostTrans will attempt to find a transaction in daybook by the OurRef,YourRef,AltRef (set under "Payments" tab in system setup).  Then edit the transaction and add an aditional line to it.  V6.9+

 

Fields available are:AnalCode, CostCentre, CostPrice, Currency, Department, Description, Discount, GLCode, JobCode, LineDate, LineType, MultiStockLoc, NetValue/Amount, QTY, QtyMul, QtyPicked, QTYWrittenOff, Stockcode, TLUser1, TLUser2, TLUser3, TLUser4, TLUser5, TLUser6, TLUser7, TLUser8, TLUser9, TLUser10, VATAmount, VATCode, Weight

 

Tag PBTHAddMatch writes back the OurRef of transaction edited see settings

see example "Transaction Add Lines"

 

Only for SIN,SOR,PIN,POR transactions

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

PBTHAddMatch

Text

Writes back the OurRef of transaction edited using THAddMatch

PBStockDesc6

Text

Post Back Stock Discount line 6

Notes:

THGNotes

Memo

Transaction Notes

chr$(10) or "<br>" gives new line

Other:

QtyWrittenOff

QtyPicked

B2B Updates: Available V4.2 Jan 2014

These tags allow stock records to be updated as a transaction is posted, for B2B transactions, if the value differs from value in Exchequer. Empty cells are ignored.

StkPreferredSupp

Text

Update stock record with Preferred supplier

StkReOrderPrice

Double

ReOrder Price on stock record

StkReOrderCur

Integer

ReOrder Currency on stock record

StkCostPrice

Double

Cost Price on stock record

StkCostCur

Integer

Cost Currency on stock record

StkUser1...10

Text

Update Stock User1...10

Lookup Tags:

CustomerSearch

Allow column to search for Customer, when using the search

Used inconjunction to the Lookup XLA

SupplierSearch

Allow column to search for Supplier, when using the search

Used inconjunction to the Lookup XLA

We have now been developing a version of PostTrans for Excel into Sage 50 or Sage 200. We have used our 18 years of experience to implement the best Sage 50 Import module, or Sage 200, available.