Jump to content

Documentation/WollMux/Mail Merge/Datasource

From The Document Foundation Wiki

The data source

The data source (Calc table) contains all variable data (e.g. addresses) that you want to use in your mail merge. The structure of the data source has a significant influence on the creation of the source document or template and the options available to you there.

If possible, you should always use the fields that are already available in the source template or source document. This concerns especially the fields that are already included in the letterhead. We recommend not to change these fields - so not to rename "EmpfaengerZeile1" to "Salutation". It is much better and also more flexible to adjust the data source accordingly.

Basics

As data source you can use tables from spreadsheet documents like you create them with Calc. This manual primarily describes how to use WollMux's mail merge function with tables from table documents. Tables from text documents (Writer) cannot be used for mail merge. If you have used Word tables for mail merge in Word, they must be transferred to table documents (Calc).

Each data source consists of a table, e.g. a table of a Calc table document. This table in turn consists of columns and rows. It is necessary that each column has a heading. This heading explains, so to speak, what information must be entered in the corresponding column. The heading thus gives the column its field name. The field names are always in the first row of the table (see ). This is therefore also called the field name row. The field name corresponds to the name of the mail merge field in the source document. The other lines are the so-called records, in which the variable information of the letter is entered.

Typical structure of a data source
Typical structure of a data source

Creation of the data source

A new data source can be created in LibreOffice either via File → New → Table Document or via Tools → Mail Merge (WollMux) → New Calc Table....

In the first row, enter the field names you want to use later in your mail merge. Take at this time still no Consider possibly already existing fields in your source template or in your source document. So write for the field that should contain the salutation "Salutation" and not "EmpfaengerZeile1" or similar. This adaptation or extension will be done only in one of the next steps (see chapter Adapt data source to WollMux fields).

Starting with the second line, enter your data. When you have entered all records, save the table document. Later adjustments, such as adding or deleting records, are of course possible at any time (see chapter Post-processing of the data source).

Please make sure that your table meets the following quality criteria:

  • Field names should only consist of the following characters:
  • 0-9
  • a-z
  • A-Z
  • _
  • So no spaces, special characters, etc. in the field name.
  • Please do not make (manual) line breaks in the column headers, i.e. in the field names.
  • There should be no blank columns in the data source: The field name row must be completely filled in. There should be no column between the data columns that does not have a header.
  • The data source can also contain columns that are not to be used for mail merge. This means that you can also use Calc tables that you have already created for other purposes, but which already contain the fields that you want to use for mail merge. This way you do not need to create another table.
  • Identical information - Identical entry: Make sure that e.g. in a column "Salutation" the salutation is always entered in the same way; i.e. always "Mr." or always "Mr.", but never mix them. Check this with the Autofilter function in Calc. For more details on the autofilter, see the chapter Filter records for printout.
  • Use the same field name for the same content in different tables (data sources), for example: Take either always "Last Name" or always "Name" as the field name for the family name. If you write "Name" in one table and "Last Name" in another and "Family Name" in the third, you will have to adjust your form letters again and again and it will become confusing.

Post-processing of the data source

The data source can be post-processed at any time. The data is immediately available for mail merge. Just open the table document, make the changes and save the file. Or click on the Edit Table button in the sidebar. This will switch you to the data source.

Adapt data source to WollMux fields

If you look at a WollMux form with the external letterhead, you will notice that it already contains some fields that should also be populated with data from the data source.

Existing fields in external letterhead
Existing fields in external letterhead

For this, it is recommended to leave the WollMux document or template unchanged and expand the data source instead.

The most important fields will probably be the fields in the recipient field, i.e. EmpfaengerZeile1 to EmpfaengerZeile6. In the first step you have to decide which of the already existing fields should be entered into the receiver rows. Where should e.g. the salutation stand or in which place the street and the house number stand?

EmpfaengerZeile1

Salutation and title

EmpfaengerZeile2

First name and last name

EmpfaengerZeile3

Street and house number

EmpfaengerZeile4

Zip code and city

EmpfaengerZeile5

EmpfaengerZeile6

  1. In Calc, create the columns with the names "EmpfaengerZeile1" to "EmpfaengerZeile6". For example, if you don't need "EmpfaengerZeile6", you can omit it. Please pay attention to the correct spelling!
  2. It is strongly recommended to have the now inserted columns filled automatically, so that you always have to enter the data only in one place when you adjust the table. For this you need a suitable Calc formula, which transfers us e.g. the fields "First name" and "Last name" automatically into the column "EmpfaengerZeile2".
  3. For this the function "CONCATENATE" is suitable. With this function, the contents of cells can be linked or concatenated. For example, if C2 contains the first name and D2 contains the last name, the function would look like this: =CONCATENATE(C2; " "; D2).
  4. This function is entered in the column "EmpfaengerZeile2", in this case J2. This ensures that in J2 now first the content from C2 followed by a space (so that the entries do not directly "stick" to each other) and the content of D2 appear.
  5. If the cell J2 is now selected and dragged down, all fields are filled with the functions and the associated fields.
  6. With the other fields simply analogously one proceeds.