This PostTrans spreadsheet allows the user to easily Import/Extract/Amend Customer/Supplier/Stock Discount Matrix data in Exchequer Enterprise. It uses a series of tags, placed in row 1, to identify the data to be Imported/Extracted/Amended. These tags can be easily added to a spreadsheet using the [Design] button, on the PostTrans Toolbar. The data is read from row 3 onwards, which allows you to put user descriptions in row 2.

Click here for more Information on Designing templates.

Special Notes on Importing Customer Qty Breaks

Demonstration Videos

Account Discount Matrix

Stock Discount Matrix

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

Extracting Customer/ Supplier/Stock Discount Matrix Data from Exchequer Enterprise

If tag "CustCode" exists the in spreadsheet, then PostTrans will Import/Extract/Amend Customer/ Supplier Discount Matrix data in Exchequer Enterprise. If it does not exist then it will import/Extract/Amend Stock Discount Matrix data in Exchequer Enterprise.

Extracting Customer/ Supplier Discount Matrix Data from Exchequer Enterprise

If tag "CustCode" exists the in spreadsheet, to extract Customer/ Supplier Discount Matrix data press [Update Lookups], on the PostTrans Toolbar, this will allow you to add criteria to the lookup:

Exchequer IRIS Enterprise get Customer and Supplier

Here we are going to get all customers with "SAM" in user field 1. We then press [Clear and Lookup Values] or [Add Lookup Values] button to populate the spreadsheet with data Exchequer Enterprise Customer/Supplier records.

IMPORTANT : The [Add Lookup Values] will not detect potential duplicate records added to the spreadsheet.

Exchequer Enterprise Discount Customer Import

Extracting Stock Discount Matrix Data from Exchequer Enterprise

If tag "CustCode" does not exists the in spreadsheet, to extract Stock Discount Matrix data press [Update Lookups], on the PostTrans Toolbar, this will allow you to add criteria to the lookup:

Exchequer IRIS Enterprise get Stock

Here we are going to get all stock under the heading "CONTROLPANELS". We then press [Clear and Lookup Values] or [Add Lookup Values] button to populate the spreadsheet with data Exchequer Enterprise Stock records.

IMPORTANT : The [Add Lookup Values] will not detect potential duplicate records added to the spreadsheet.

Amending the Exchequer Enterprise Customer/ Supplier/Stock Discount Matrix Data

Having extracted data, the user can now simply edit the IRIS Enterprise data, before re-importing it back to Exchequer Enterprise.

Importing new Exchequer Enterprise Customer/ Supplier/Stock Discount Matrix Data

Simply add new lines to the spreadsheet from row 3 onwards. Then press [Post Transactions], on the PostTrans Toolbar, to import Exchequer Enterprise data.

Deleting Customer/ Supplier/Stock Discount Matrix Data

To delete a single matrix entry, simple enter "X" in "DeleteRecordFlg" column:

Exchequer Enterprise Delete Discount Matrix

Important : At present PostTrans cannot delete entries with effective dates, see below.

Customer/ Supplier/Stock Discount Matrix Required fields

To Import/Extract/Amend Exchequer Enterprise Customer/ Supplier/Stock Discount Matrix data, the following Tags/Fields must be specified listed in table below.

Cell A1 must equal DiscPosted

Type tag is used to detect end of spreadsheet. PostTrans will stop Importing/Amending, from row 3 onwards, when this field is empty.

Restrictions

Important : At present PostTrans cannot delete entries with effective dates correctly, it will:

	For Account matrix : delete all entries with the same "AccountCode"
	For Stock matrix : delete all entries with the same "StockCode"

Will not import export Account/Qty breakes

Exchequer Enterprise Customer/ Supplier/Stock Discount Matrix 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
Required (Prof version):
DiscPosted	Yes/No	Returns 'POSTED' if Trans line has been posted and will be ignored if 'POSTED' (required)
ErrorDesc	Text	Description of error posted back if record unsuccessfully posted
DeleteRecordFlg	DeleteFlag	Delete record if =X
CustCode	Text 6	Customer Code if Customer Matrix If present then Customer/Supplier discount, else Stock Qty
StockCode	Text 16	Stock Code if Stock Matrix
Type	Text 1	Discount Type B = Band Price (Req:SalesBand),P = Special Price (Req:SPCurrency and SPrice),M = Margin % (Req:DiscMar),U = Markup % (Req:DiscMar),Q = Quantity Break (Req:QtyFr and QtyTo)
SalesBand	Text 1	Sales Band A-H
Currency	Long	Currency 1-89 (defaults=1)
Price	Double	Price if a Special Price Record
DiscPercent	Double	Discount Percent if Discount, Margin or Markup
DiscAmount	Double	Discount Amount if Discount
DiscMargin	Double	Discount Percent if Margin or Markup
Stock Qty Discount:
QtyFrom	Double	Qty FROM if Stock Qty break, see Special Notes on Importing Customer Qty Breaks
QtyTo	Double	Qty TO if Stock Qty break, see Special Notes on Importing Customer Qty Breaks
Effective dates:
EffectiveDateStart	Date	Start date of price
EffectiveDateEnd	Date	End date of price
Additional Lookups:
StockDescription	Text 35	Stock description from Stock record (Read Only)
AccountName	Text 35	Account Name (Read Only)

Special Notes on Importing Customer Qty Breaks

Customer Qty breaks can easily be imported using PostTrans.

For example:

EffectiveDateStart, EffectiveDateEnd can also be used, but not shown in above example.

IMPORTANT:

The lines MUST BE grouped by CustCode and StockCode before importing. Or in other words the sheet should be ordered by CustCode, then StockCode.

The QtyFrom must be more than 0, even though Enterprise will except 0

If you change the QtyTo or QtyFrom breaks figures, then you need to delete the entire Qty break first to remove old entries. Then re import data.

The effective dates columns should be formatted as TEXT, as with all other date cells when using PostTrans. This is because on certain conditions Excel will use American date format if the date entered is before the 13th, and English on and after the 13th. Enter date as dd/mm/yy.

PostTrans: PostTrans for Exchequer Software Prices

[ More about Exchequer Software ] [ IRIS Enterprise Software ] [ WebRangle Linking ]

Last modified: April 28, 2010