PostTrans XL Visual Importer: Import Exchequer Stock Records
This PostTrans spreadsheet allows the user to easily Import/Extract/Amend Stock 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. More Information on Design and Tags.
Demonstration Videos:
Demonstration Video
0:20 How to open examples |
The "Customers and Stock" Example shows this in action. more information on PostTrans examples
Import Exchequer Stock Records Features:
Extracting Stock Data from Exchequer Enterprise
To extract Stock data press 
button, on the PostTrans Toolbar, this will allow you to add criteria to the lookup:
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 Stock Data
Having extracted data, the user can now simply edit the Enterprise data, before re-importing it back to Exchequer Enterprise. Note only changed records will be updated in Enterprise.
Importing new Exchequer Enterprise Stock 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.
Stock Required fields
To Import/Extract/Amend Exchequer Enterprise Stock Data, the following Tags/Fields must be specified:
STKPosted, ErrorDesc, StockCode, ParentStockCode, Desc1, StockType, StockValType, SalesGLCode, CostOfSalesGLCode, WriteOffStockGLCode,StockValueGLCode, WIPGLCode
Cell A1 must equal STKPosted
StockCode is used to detect end of spreadsheet. PostTrans will stop Importing/Amending, from row 3 onwards, when this field is empty.
Restrictions
WOPProdTimeDays, WOPProdTimeHours, WOPProdTimeMins do not update at present
StockType cannot be changed, once imported. It can only be changed to X=Discontinued.
Import Exchequer Stock 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.
|
Field Name |
Data Type Length |
Description |
|
Required: |
|
|
|
STKPosted |
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 (required) |
|
StockCode |
Text 16 |
Stock identifier code (required) used to identify stock in Exchequer Enterprise |
|
ParentStockCode |
Text 16 |
Code of parent on tree (required) in Exchequer Enterprise |
|
Desc1 |
Text 35 |
Stock description (required) |
|
StockType |
Text 1 |
G =Group,P =Product,D =Description,M =Bill of Materials,X =Discontinued (required) StockType cannot be changed, once imported, apart from setting to Discontinued |
|
StockValType |
Text 1 |
R =Serial/Batch,V=Serial/Batch Avg,C =Last Cost,L =LIFO,F =FIFO,S =Standard,A =Average (required) |
|
Required GL: |
|
|
|
SalesGLCode |
Long Integer |
Sales Nominal code (required) |
|
CostOfSalesGLCode |
Long Integer |
Cost of Sales Nominal code (required) |
|
WriteOffStockGLCode |
Long Integer |
P&L Closing Stock Nominal code (Write off) (required) |
|
StockValueGLCode |
Long Integer |
Balance Sheet Stock Value Nominal code (required) |
|
FinishedGoodsGLCode |
Long Integer |
Work In Progress Nominal code (required) |
|
Description: |
|
|
|
AltStockCode |
Text 16 |
Optional code for stock searching |
|
Desc2 |
Text 35 |
Stock description 2 |
|
Desc3 |
Text 35 |
Stock description 3 |
|
Desc4 |
Text 35 |
Stock description 4 |
|
Desc5 |
Text 35 |
Stock description 5 |
|
Desc6 |
Text 35 |
Stock description 6 |
|
Price: |
|
|
|
PriceByStockUnit |
Integer |
Price by Stock Unit 0, Sales Unit 1, Split pack 2 |
|
CurrencyBandA |
Byte |
Currency for Price Band, 0-29,1=Sterling |
|
UnitPriceA |
Double |
Price for Price Band |
|
CurrencyBandB |
Byte |
Currency for Price Band, 0-29,1=Sterling |
|
UnitPriceB |
Double |
Price for Price Band |
|
CurrencyBandC |
Byte |
Currency for Price Band, 0-29,1=Sterling |
|
UnitPriceC |
Double |
Price for Price Band |
|
CurrencyBandD |
Byte |
Currency for Price Band, 0-29,1=Sterling |
|
UnitPriceD |
Double |
Price for Price Band |
|
CurrencyBandE |
Byte |
Currency for Price Band, 0-29,1=Sterling |
|
UnitPriceE |
Double |
Price for Price Band |
|
CurrencyBandF |
Byte |
Currency for Price Band, 0-29,1=Sterling |
|
UnitPriceF |
Double |
Price for Price Band |
|
CurrencyBandG |
Byte |
Currency for Price Band, 0-29,1=Sterling |
|
UnitPriceG |
Double |
Price for Price Band |
|
CurrencyBandH |
Byte |
Currency for Price Band, 0-29,1=Sterling |
|
UnitPriceH |
Double |
Price for Price Band |
|
Cost: |
|
|
|
CostPriceCurrency |
Byte |
0-29,1=Sterling |
|
LastCostPrice |
Double |
The last cost price of Stock Item |
|
PORCurrency |
Byte |
POR Reorder Currency, 0-29,1=Sterling |
|
PORCostPrice |
Double |
POR Reorder Price |
|
ReOrder Defaults: |
|
|
|
PrefSupplierCode |
Text 6 |
Master supplier Ref. |
|
ReOderCostCentre |
Text 3 |
|
|
ReOrderDepartment |
Text 3 |
|
|
Stock Qty: |
|
|
|
UsesMultiBinsFlg |
Yes/No |
Use multi bins |
|
QtyMin |
Double |
|
|
QtyMax |
Double |
|
|
ShowAsSalesPacksFlg |
Yes/No |
Show stock levels as sales units (Packs) |
|
Defaults: |
|
|
|
Department |
Text 3 |
|
|
CostCentre |
Text 3 |
|
|
VATCode |
Text 1 |
S,Z,E,1,2,3,4,5,6,7,8,9,A,D,T,X IS,IZ,... for inclusive VAT |
|
DefaultLineType |
Integer |
Default Line type 0-5 (Normal,Labaour…) |
|
JobAnalysisCode |
Text 10 |
Default Job Analysis code |
|
Stock Qty Multiplier: |
|
|
|
DescStockingUnitQty |
Text 10 |
Descriptive Stocking Unit Qty |
|
DescSellingQty |
Text 10 |
Descriptive Selling Unit Qty |
|
DescPurchaseQty |
Text 10 |
Descriptive Purchase Unit Qty |
|
SellQtyMul |
Double |
Number of items in case |
|
PurchaseQtyMul |
Double |
Number of items in case |
|
IntraStat Settings: |
|
|
|
IntraStatVATCommodityCode |
Text 10 |
Intrastat value fill in if turned on |
|
IntraStatUnitDesc |
Text 10 |
Intrastat value fill in if turned on |
|
IntraStatStockUnit |
Double |
Intrastat value fill in if turned on |
|
IntraStatSalesWeight |
Double |
Intrastat value fill in if turned on |
|
IntraStatPurchaseWeight |
Double |
Intrastat value fill in if turned on |
|
IntraStatCountry |
Text 5 |
Intrastat value fill in if turned on |
|
Works Orders: |
|
|
|
WOPProdTimeDays |
Integer |
Time taken to produce in days |
|
WOPProdTimeHours |
Integer |
Time taken to produce in hours |
|
WOPProdTimeMins |
Integer |
Time taken to produce in mins |
|
WOPROLeadTime |
Integer |
Lead time in days |
|
WOPAutoCalcProdTime |
Yes/No |
Auto Calc Prod Time |
|
WOPMinEcQty |
Integer |
Min Economic build qty |
|
WOPIssuedWIPGL |
Integer |
WIP GL code to use on WOP |
|
User Fields: |
|
|
|
User1 |
Text 20 |
User defined field 1 |
|
User2 |
Text 20 |
User defined field 2 |
|
User3-4 |
Text 30 |
User defined field 3-4 |
|
User5-10 |
Text 30 |
User defined field 5-10 (V6.9+) |
|
Web: |
|
|
|
WebLiveCat |
Text 20 |
|
|
WebPrevCat |
Text 20 |
|
|
WebInclude |
Yes/No |
|
|
ImageFile |
Text 30 |
Image file for web image |
|
Location: |
|
|
|
LocationCode |
Text 3 |
Relates to Location record |
|
BinLocation |
Text 8 |
Location of Bin if multi-Location turned on |
|
BarCode |
Text 20 |
|
|
BOM: |
|
|
|
ShowBOMKitOnSales |
Yes/No |
Include BOM items on Sales Transactions |
|
ShowBOMKitOnPurchases |
Yes/No |
Include BOM items on Purchase Transactions |
|
Read Only: |
|
|
|
FolioNumber |
Long Integer |
Internal ID (Read only) |
|
LastUsed |
Date |
Date last used (Read only) |
|
Returns: |
||
|
PurchaseReturnGL |
Long Integer |
Return GL |
|
SalesReturnGL |
Long Integer |
Return GL |
|
RestockCharge |
Double |
Stock Recharge can be a percent |
|
ManufactureWarranty |
Long Integer |
Warranty Period |
|
SalesWarranty |
Long Integer |
Warranty Period |
|
ManufactureWarrantyPeriod |
Text |
Day,Week,Month,Year |
|
SalesWarrantyPeriod |
Text |
Day,Week,Month,Year |
|
Notes: |
|
|
|
GNotes |
Memo |
General Notes (Deletes any existing Notes in General and Dated Notes) chr$(10) or "<br>" gives new line |