PostTrans XL Visual Importer: Training
Click here to download a demonstration version of PostTrans which will work with "Electrics R Us"
PostTrans Training School: Your 25 minute free personal training videos
PostTrans has 2 distinct types of templates for importing data into Enterprise. The following videos show the basics of how PostTrans works:
Static data, such as Customer, Supplier, Stock, Discounts, etc
Transactional data such SOR, SIN ,POR, TSH, WOR and multiple transactions
Then look at the blue menu on the left for more specific information on the type of import you require.
The download works out of the box with "Electrics R Us" for training/eval without unlock codes. Install notes
Tip: Having completed the training school, you should now have a clear understanding of PostTrans design and implementation.
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)
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