Save time updating
PostTrans Online Training
Insert Tags
The following video shows how design PostTrans Templates to Extract, Amend and Import Static data, as discussed in this section.
Importing Static Data, customers, stock/products etc.
PostTrans for Sage toolbar
Button Functions:
Import - For static data, Customer, Products, etc Import data in row 4 onwards. Transaction template use row 30 onwards. See below
Extract - Extract data from sage for particular type of import static data. See How to Filter Extracted Data
On a transaction this allows you to populate the sheet with Products, so when using in conjunction with Ignore ZERO qty, allows you to create an Sage Order Pad for quick order taking. Pro
Clear - Clears the header cells and data in rows. On transaction templates it also copies down any other values and formula expressed in row 27. See Clear transaction and copy down formula
Tags - Gives access to the Tags to define the data mapping between Sage and Excel in columns, or Header fields in a Transaction. From this window you can add new Tags, or edit the properties of current cell mapping.
Setup - Access to Registration, Sage Company selection and system switches.
Help - Access links to these help pages and prepared examples. Sage 50 List, Sage 200 List
File - Optional - Allows user to import a text file into template. This is hidden unless enabled. Importing a text file
[Find] - Optional - Allows the search of Quotes/Oders in the PostTrans register, for easy retriveal. PRO
Static Data template layout
When we refer to "Static data" we are talking about the Customer, Supplier, Product, Departments, etc. Records which don't change much when compared to transaction tables.
The diagram below shows the layout of a Static Data PostTrans template
The Tags, or data columns, can be in order as long as the tag in cell A1 remains in position. PostTrans uses this tag to know what data the template relates to. In the above example "Sage50cust" defines the template as being for Sage Line 50 Customer Extract/Amend/Import template. "Sage50Prod" would define as Product Extract/Amend/Import template.
The tag "CuErrorDesc" is required, to enable the PostTrans to report back any errors or warnings when importing data. PostTrans also uses this column to write a CRC number for the row. The CRC number is calculated on the contents of the row, to enable PostTrans to detect changes in the data, and thus only import data which has changed. Deleting this number forces the row to be updated in Sage.
End of File
One Tag in each type of import will be designated as EOF (End of File). PostTrans uses this to detect the end of the import. In the above example, for customers, it will be "CuCode" as all customer records have to have a code and the last record will be the empty one.
Filter
When extracting data, filter criteria can be added in row 2. How to filter Sage Line 50 extracted data.
Some tags end with ^. This denotes the have a code search function allowing the user to quickly find a code. Such as Account, Nominal code, etc.
PostTrans Training Video - Static Data Template design
| PostTrans Training Video - Static Data Template design | |||
| 0:00 | Video Introduction | ||
| 1:00 | Creating a new Template | ||
| 2:00 | Structure of a Static Template | ||
| 4:00 | Extracting data from sage line 50 | ||
| 5:15 | Altering the data columns using Tags | ||
| 8:00 | In cell searching | ||
| 9:30 | Importing data | ||
| 10:30 | Errors and warnings | ||
| 12:00 | Copy data | ||
| 12:30 | Adding formula to copy down Extracted Data | ||
| 13:40 | Clear Button | ||
| 14:06 | Help button to get Example templates | ||
| 15:00 | Filter Extracted Data | ||
Added Filtering for large set of data
| 0:00 | Introduction | |
| 0:22 | The Search Column | |
| 0:48 | Extract a Single Trader Price list | |
| 1:16 | Extract a Trader Price list from list of codes | |
| 2:00 | Extract Filter by Stock Code List | |
| 2:20 | Extract Filter by Stock Code List and Trader List | |
| 2:48 | Other Areas : Special Prices |
| 0:00 | Introduction | |
| 0:19 | Extract All Price Data | |
| 1:53 | Apply Auto Filter | |
| 2:00 | Edit Prices and Import |
Transaction template layout
The diagram below shows the basic layout of a Transaction based PostTrans template:
In the above example "Sage50trans" defines the template as being for Sage Line 50 and a Transaction template. The cell A1 can be hidden, and protected, by simply hiding the row.
Please note: The demo/training videos say a Tag must exist in Cell A1, for transaction type imports. Later in development PostTrans was altered to look at the tag value in the comment of cell A1. Thus the tag text in cell A1 can be deleted.
The Tags button, on the PostTrans toolbar, gives access to the Tags which define the data within a PostTrans template. Tags beginning with TH can be placed in the Header of the transaction, row 1-26, or in row 28 to denote the data will be entered in the lines from row 30 onwards. Some tags have to be placed in the header only, but not many.
Tags beginning with TL must be placed in row 28 to define the data in the column below row 30 as being transaction line data.
Some of the TH Tags, if defined in row 28 will force a new transaction. See below example in next section...
Current Tag Properties
Clicking on the "Current Tag Properties" displays information about the mapping to sage for the current Excel Cell. Depending on the Tag used, optionas can be turned ON/OFF.
The values in row 27, enable the Clear button to delete and copy down formula. The "On [Clear] button..." section easily allow you to set these values.
Some tags end with ^. This denotes the have a code search function allowing the user to quickly find a code. Such as Account, Nominal code, etc.
Transactional templates are for Sales/Purchase Orders/Invoices, Adjustment, Nominal Journals
How PostTrans reads the template design
When the Import button is pressed, PostTrans scans the first 26 rows looking for cell comments. If the first line of a cell comment matches the name of a PostTrans tag, then it knows that cell should contain Sage data to import. It then remembers any of the values in this header section. Thus deleting a cell comment removes the PostTrans field definition.
PostTrans then scans row 28, the Tag row, which defines the data to import in the lines section of the template. Row 30 onwards.
PostTrans will then scan down the template, reading the lines of data as it goes. Thus when posted:
Multiple Transactions
If a TH* tag is defined in row 28, then those values in the lines will override the values specified in the header section, thus multiple transactions can be specified in the template. PostTrans will start a new transaction if the data under a certain tags change.
Example:
The following examples has three line on each transaction, and four transactions are listed in row 30 onwards:
Within a Transaction template, if tag THTransDate, THTransType, THCustRef or THAccCode are specified, then a change in value will force PostTrans to start a new transaction.
PostTrans Training Video - Transaction Template design
| PostTrans Training Video - Transaction Template design | |||
| 0:00 | Video Introduction | ||
| 0:45 | The Template structure - Sales Order | ||
| 3:30 | The Header section of a transaction | ||
| 10:00 | Reprinting a Transaction, to e-mail or printer | ||
| 10:30 | Clearing a Transaction | ||
| 11:00 | Postcode Address lookup in Delivery Address | ||
| 11:40 | Customisation Adding extra fields, Currency, Price list, Message at top of Transaction | ||
| 12:00 | How to get Help on Tags | ||
| 14:30 | Blogs relating to tags | ||
| 16:48 | Clear button | ||
| 17:30 | Help button to access blog examples | ||
| 18:57 | Posting Multiple Transactions | ||
| 24:30 | System settings | ||