PostTrans XL Visual Importer: Import IRIS 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 [Design] 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:
The "Customers and Stock" Example shows this in action. more information on PostTrans examples
Import IRIS Exchequer Stock Records Features:
Extracting Stock Data from Exchequer Enterprise
To extract Stock data press [Update Lookups], 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 IRIS Enterprise data, before re-importing it back to Exchequer Enterprise. Note only changed records will be updated in IRIS 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
Import IRIS 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 Currency, 0-29,1=Sterling |
|
PORCostPrice |
Double |
POR Cost 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 |
Text 30 |
User defined field 3 |
|
User4 |
Text 30 |
User defined field 4 |
|
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 |