This page describes how to export product data into a word document, e.g. to construct a purchase order. There is a simple example of how to do this provided in the elink manuals, the version provided here is rather more complete. However, it is designed to allow simple customisation appropriate for many applications.
The main document comprises normal template fields, such as the contact, company, document and project references. These are inserted in the normal manner. However, there is a special bookmark defining where the product table is to be placed.
The product table is generated using a word macro called beforeMailMerge. This macro is responsible for reading data from the elink database into the word document. The main thing to be customised is the list of fields to be shown in the final document table. Obviously, this list will vary according to the specific needs and custom fields in the elink database. Adding a custom field to the list to be exported is done as part of the field definition for the template.
Within the word macro, there is a section which looks like:
‘ the following columns are the product data, not required here
intCol = declare_column(column_list, intCol, “Reference”, “Products_Reference”, False, “TEXT”, False)
‘intCol = declare_column(column_list, intCol, “Supplier’s Code”, “Products_Suppliers_Code_Supplier_Data_Imported_Data_”, False, “TEXT”, False)
intCol = declare_column(column_list, intCol, “Supplier’s Code”, “Products_Suppliers_Code_Purchasing_General_”, False, “TEXT”, False)
‘intCol = declare_column(column_list, intCol, “Name”, “Products_Name”, False, “Standard”, False)
intCol = declare_column(column_list, intCol, “Name”, “Products_Name_Purchasing_General_”, False, “Standard”, False)
intCol = declare_column(column_list, intCol, “Quantity”, “Products_Quantity”, True, “Standard”, False)
‘intCol = declare_column(column_list, intCol, “Units”, “Products_Units_of_Supply_Purchasing_General_”, False, “TEXT”, False)
‘intCol = declare_column(column_list, intCol, “Units”, “Products_Units_of_Supply_Supplier_Data_Imported_Data_”, False, “TEXT”, False)
intCol = declare_column(column_list, intCol, “Units”, “Products_Units_of_Supply_Purchasing_General_”, False, “TEXT”, False)
‘intCol = declare_column(column_list, intCol, “Supplier Discount”, “Products_Discount_Supplier_Data_Imported_Data_”,true,”Standard”,false)
intCol = declare_column(column_list, intCol, “Price Each (£)”, “Products_Price”, True, “Standard”, False)
‘intCol = declare_column(column_list, intCol, “Discount”, “Products_Discount”,true,”Standard”,false)
‘intCol = declare_column(column_list, intCol, “Category”, “Products_Category”, False, “TEXT”, False)
‘intCol = declare_column(column_list, intCol, “Type”, “Products_Type”, False, “TEXT”, False)
‘intCol = declare_column(column_list, intCol, “VAT Rate (%)”, “Products_VAT_Rate_Supplier_Data_Imported_Data_”, True, “Standard”, True)
‘intCol = declare_column(column_list, intCol, “VAT Rate (%)”, “VAT Rate”, True, “Standard”, False)
‘intCol = declare_column(column_list, intCol, “Total Ex VAT (£)”, “Products_Total”, True, “Standard”, False)
Essentially, this list defines the set of columns to be included in the generated word table. The statements are a series of calls to declare_column, the parameters to declare column() are :
Column_list – this is an object which contains the final column list
Column_number – counts the columns in order
heading – the text used as the heading for that column in document table
column_name – the elink name of that field
right_align – true = right align, false = centre (used for currency fields)
format -Standard or TEXT
Sum – obsolete?
The main difficulty is determining the name of the custom fields as they appear in this macro – the names used for the column_names in the above list.
The best way of getting this exactly right is to uncomment the stop statement:
Set rst = conn.Execute(“SELECT * FROM TblMerge;”)
‘Stop
Then run the macro in the normal manner, It will stop executing at this stop statement. It is then possible to inspect the content of the rst variable, which contains a list of fields associated with the incoming data from elink. By inspecting the fields, you can check that all the required custom fields are being imported correctly, and determine the appopriate names. Once this macro has been customised, the stop statement can be commented out again.
