Postcode Programming API  PostTrans XL Visual Importer: Import Stock/Customer/Supplier Discount Matrix records


This is only available in the Professional version of PostTrans

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 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.  more information on PostTrans examples


Features:


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 button, on the PostTrans Toolbar, this will allow you to add criteria to the lookup:

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

Exchequer Enterprise Discount Stock Import

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

Having extracted data, the user can now simply edit the 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

 

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

On the Pervaive version of Exchequer it is advised NOT to use PostTrans to delete Qty breaks.

Account/Qty breakes

Account/Qty breakes have various restrictions due to the functionality of the COM Toolkit:

  • Account/Qty breakes cannot be deleted (Toolkit does not provide this functionality). In MS SQL we have by passed this problem.
  • Account/Qty breakes cannot be updated (Bug ABSEXCH-14208 and ABSEXCH-14207)
  • Effective dates are not supported.

In summary, PostTrans cannot be used to update Account/Qty breakes so as a work around:

1. Export current customer discounts using Post Trans
2. Amend export to be what you really want
3. Use PostTrans to delete entries (MS SQL version only)
4. Use PostTrans to import discounts back onto the customer



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