English  /  Deutsch

Customer Login

Recover Password

WorkOrder TS Support Database

Bookmark: Root \ WorkOrder TS Guides \ How to mail merge OpenOffice Writer with WorkOrder TS

How to mail merge OpenOffice Writer with WorkOrder TS

Last Updated: 2007-09-30

As of version 0.4.3, WorkOrder TS supports new marketing features which allow you to make use of the WorkOrder TS customer tables to automatically fill out information stored in the WorkOrder TS database. This can be used to print out address labels or directly onto letter heads ... the choice is yours.

This guide is for Open Office Writer 2.2, you can find a Microsoft Word 2003 guide here.
This guide assumes that you have a MySQL ODBC connection configured. You can find a MySQL ODBC Windows Setup Guide here.

This guide explains how to create a basic promotional letter which automatically pulls customer names and addresses from the WorkOrder TS database. Write your letter once and print it for all your customers with just a few mouse clicks.....
Do you need 10,20,...1000,....10000 printouts? No Problem!

Let's get started, open an existing letter or start a new document, now click on File => Wizards => Address Data Source

This will open the "Address Book Data Source Wizard" which gives you the ability to link almost any kind of address book with OpenOffice. This guide will focus on a MySQL ODBC connection. Select "Other external address source", click on "Next" and then "Settings".

On the "Database Properties" window, select "MySQL (ODBC)" and click "Next". Now click on "Browse" and select "WorkOrderTS" as your data source, click on "OK" when done.

Enter your guest user account name into the "User name" field and check the "Password required" box. You can find the WorkOrder TS guest account information in the file config-guest.inc.php located inside the WorkOrder TS include directory.

Click on "Test Connection" to receive the password box for your ODBC connection, enter the password and click "OK" to receive the "The connection was established successfully" message box. Click "Next" and change the "Character set" to "Western Europe (Windows-1252/WinLatin1) and click "Finish"

Now you can select the table you want to use, the _customers table contains the billing address (default WorkOrder TS address) and the _customers_ship table contains the customer's shipping address. Select _customers and click "Next". Don't bother assigning the database fields ("Field Assignment") because the "Mail Merge Wizard" will force you do it again later. Click "Next".

Next give your data source a name and save it.

OpenOffice is now connected to the WorkOrder TS database, congratulations. Let's make the information appear in our letter....
Click on Tools => Mail Merge Wizard to open the wizard's dialog box. Click on "Next" until you get to "Insert Address Block".

At the "Insert Address Block" tab, click on "Select Different Address List". Select the WorkOrderTS address list, once you click on it a new window will open prompting you to select a source table. Select the _customers table and click on "OK".

Back on the "Mail Merge Wizard" check the box "This document shall contain an address book" and "Suppress lines with just empty fields" to avoid empty lines in your address block on your final printouts. You can click on "More" to select different address styles. When done click on "Match Fields" to tell Open Office which database field holds what kind of information. The map is below., click "OK when you are done.

WorkOrder TS database field map:

  • ID => Customer ID
  • BBus_name => Company Name
  • BBus_cont_title => Business Contact Name 1 Title mr/mrs
  • BBus_cont => Business Contact Name 1
  • BBus_cont_title => Business Contact Name 2 Title mr/mrs
  • BBus_cont => Business Contact Name 2
  • BTitle => Customer Title (non business)
  • BName_first => Customer's First Name
  • BName_last => Customer's Last Name
  • BAddress1 => Part 1 of Address, usually Street and house number
  • BAddress2 => Part 2 of Address, usually contains Unit number
  • BCity => Customer's City
  • BState => Customer's State
  • BZip => Customer's Zip Code
  • BCountry => Customer's County
  • BPhone1 => Customer's Home Phone Number 1
  • BPhone2 => Customer's Home Phone Number 2
  • BMobile1 => Customer's Mobile Phone Number 1
  • BMobile2 => Customer's Mobile Phone Number 2
  • BOffice1 => Customer's Office Phone Numbers 1
  • BOffice2 => Customer's Office Phone Numbers 2
  • BFax1 => Customer's Fax Number
  • BFax2 => Customer's Fax Number
  • Email1 => Customer's E-Mail Address1
  • Email2 => Customer's E-Mail Address2
  • Web => Customer's Website UR

The wizard will now give you the option to include a greetings line, just select the desired style for your promotional letter. Since the fields are still matched from the last step you can skip it this time. Click "Next" to continue until you reach "Edit Document"...

The "Edit Document" tab allows you to hand edit your document to include more database fields or text. The second screen shot shows a completed letter, the only information missing is the Customer ID

Adding additional database fields is pretty simple, just click on Insert => Fields => Other
Make sure that the new window is on the database tab and select "Type" as "Mail merge fields", now click on the + next to the WorkOrder TS _customers table to expand the table.

Once expanded you can select any database field you want, click on "Insert" to add the field to the document at the current cursor position. The second screen shot shows that the ID field has been added to the document. The document is now complete, click on "Return to Mail Merge Wizard" to start merging the entries in your database with your document.

Almost done ..... OpenOffice will try to merge all your letters to provide you a complete preview for step 7. This is fine for a few customers but it will take 20-30 minutes for 5000 customers. You can "Cancel" the Mail Merge if you don't need to edit individual letters. Click on "Save, print or send" to continue. Make sure you click on "Save starting document" to use your new document as a template for later.

To reopen a previously created document, open OpenOffice Writer, Click on Tools => Mail Merge Wizard
You can select a recent document from the "Start from a recently saved starting document" or via the option "Start from existing document".

That's how simple it is to use mail merge with OpenOffice Writer, now comes the hardest part ..... designing your promotional letter......
Please contact WorkOrder TS technical support regarding any issues when merging the WorkOrder TS database with your word processor.


User Comments:

No Comments yet .....


Add Your Comment:

Note: All posts require administrator approval. Please allow 24 hours for message approval. Enter your E-Mail address and you will be informed when your message is displayed.

Plain text only, less then 65 000 characters.

Which one of the following 4 words is a feeling? Words: rain love white green

Please answer the question above and type the answer into the text box below.