This document describes the creation of a word template [doc] that will be used to generate invoices and reminder letters. We present a generic template, which adapts its output to the input data. For this purpose, we will use Word functionalities of merging input data from a source Excel file to import data into our template, and code field formulas (fields) to calculate variables making a dynamic document structure.

The following table presents downloadable PDF samples of all combinations of letters we wish to generate. Each reminder level (from 1^st to 4^th reminder) must have different text, reminder fee, etc. The printed letter must also change depending on whether the customer’s phone account is active or blocked. There is no active/blocked distinction for reminder level 4 and regular paper invoice. We have 8 letter templates:

Reminder level	Line is blocked
Reminder level	No	Yes
1	pdf	pdf
2	pdf	pdf
3	pdf	pdf
4	pdf
0 (invoice)	pdf

The final Word template and sample Excel input data are available for download. Next sections of this document will show how the template is constructed to fit the input data. If we look at the field codes of the first page of the final document, we observe that most parts of the text appearing in the samples are not present directly but are constructed elsewhere using formulas:

General notes on MS Word formulas

This section provides general indications on the usage of MS Word “fields” (insertion, formatting, edition, etc.). Field codes are active text areas within the Word document, in which formulas can be entered. Such fields are delimited by brackets ({ field content }), and are highlighted in grey when selected.

Formula insertion

To insert a field code, use the Ctrl-F9 short key combination. It inserts special curved brackets, inside which you can simply type the formula:

Alternative way

Fields can also be inserted via the Insert -> Field toolbox, which shows the list of available fields. The formulas presented in this document can be entered using any of these ways. For instance, adding a { = } field:

Freshly inserted formula gives the following result. The error appears because the formula is for now empty:

To display the inner code of the field instead of its value, select the field, right click on it and choose Toggle Field Codes. Below is a toggled code of an empty formula (the \* MERGEFORMAT parameter is not a part of the active formula and will be described in section Formulas formatting):

Once the field is “toggled”, you can edit the text between the brackets to enter your formulas.

More details on formulas usage are presented here: http://unappel.ch/public/080916-ms-word-field-formulas/ and here: http://www.gmayor.com/formatting_word_fields.htm

Setting and displaying a field

Here we show the primary usage we will make of the fields: setting, displaying and using variables. The set statement defines a variable that can later be displayed or used in the rest of the document. This variable can contain any component of a standard Word document (number, formatted text, table, etc.). If the variable is a number, we can perform arithmetic operations on it, as shown in the following example:

After updating the field values, we obtain the following result:

Showing all fields contents at once

You can toggle between field values and formulas (select the field or Ctrl-A) by choosing Toggle Field Codes in right-click pop-up menu. Fields become hidden again when copy-pasting a formula or updating records. We can add View Field Codes button in the Word menu toolbar permitting to toggle all fields in the document at once, and keep them toggled even when copy-pasting. Right click on a toolbar and choose Customize, then go to Commands and View. The button to add is View Field Codes:

Drag and drop it in the Word menu toolbar. Pressing the button will toggle all field codes in the document at once. The same effect is obtained with the Alt-F9 short key combination.

SET formulas and bookmarks

Bookmarks are references to a place within the Word document. When using formulas, each SET statement defines a bookmark corresponding to the variable name.

By default, bookmarks are not visible when editing a document, but the list of defined bookmarks can be obtained with Insert -> Bookmark. For instance, we see that our variable x has an assigned bookmark somewhere in the document:

Bookmarks can also be marked in the text, which allows seeing their position. To obtain this, enable the following option in the Tools -> Options -> View toolbox:

If we return to our previous example, we now see the position of the bookmark corresponding to the x variable, marked with the  symbol:

Remarks:

When the value of variable is updated with SET field code in several places, only the last update of the bookmark will be marked by symbol .
Bookmarks are not deleted when the SET statement is removed. They must be manually deleted from the Insert -> Bookmark list.
Deleting a bookmark does not affect the corresponding variable. The bookmark will be recreated the next time the SET field of the corresponding variable is updated. But doing so can cause the formatting of variables to be lost.

Table of variables presents all variables in the document with their corresponding bookmark.

Syntax of the IF field code

The syntax of the IF statement is particular. The syntax and result changes depending on the context in which the statement is used:

“conditional IF”: { IF { value } = 1 "result if true" "result if false" } In this case, spaces are required before and after the comparison operator. There is no return value. This syntax is used e.g. for inserting text portions under a certain condition.
“arithmetic IF”: { =if(value=1,"value if true","value if false") } No spaces are required around the comparison operator. If the condition is satisfied the statement returns 1, otherwise it returns 0. This syntax is used for arithmetic calculations.

Formulas formatting

When displaying a field that was declared with a SET statement, we can choose whether to use the format defined at variable declaration, or the format which is set when the variable is displayed. This behavior is controlled by the MERGEFORMAT parameter which can be set for any field.

The following example shows the effect of the MERGEFORMAT output specifier. We set two variables: a, declared with format bold+red, and b, declared with format bold+blue. For variable a, we add the \* MERGEFORMAT output specifier, and variable b is displayed without specifier:

As result, formatting of variable a is lost when the variable is displayed with the specifier. Even if it is declared as bold+red in the SET statement, the value of the variable is shown according to the formatting of the place where it is inserted (here, normal formatting). On the contrary, variable b is displayed with the same formatting as it was declared (bold+blue):

The MERGEFORMAT parameter can also be set by checking Preserve formatting during updates in the Insert -> Field toolbox. This box is checked by default:

Number rounding

We often need to display float values rounded to two digits. This is done by specifying the number format of the variable in the display field code. For example, we declare a variable x and display it with and without formatting options (we use “\# 0.00” specifier, for 2 digits rounding):

After updating the field codes, the output looks as follows:

Remark: The amount that will be printed on the payment slip is already rounded in the source Excel file, according to increments of 0.05 CHF and is supplied as text where the decimal dot is replaced by space (for printing on payment slip).

Date format

In Word, all dates are internally stored in US format (mm/dd/yyyy). At display time, the date output format can be specified with the \@ <format> parameter. The example shows the date field that prints the current date in four different formats:

The following output is obtained:

Remark:

Display of an unformatted date depends on the computer regional settings. In our example, the default yyyy-mm-dd, to Format1.

When the day or month name is displayed, the display language depends on the language settings of the text area (the language set e.g. for spell checking) in which the formula is displayed. To obtain a French display (août 2008 instead of August 2008), we must set the language of the region where the date is displayed to French. To do this select the text, and in the main menu open the language selection panel (Tools -> Language -> Set Language):

Structure of the reminders template

Our Word template will be structured as follows:

Page 1 contains the general structure of the document. Only formatting and the minimal number of formulas are present. Constant text that does not depend on the reminder level (letter issue date, greetings, etc.) is also there.
All other formulas and variables are “hidden” in following pages of the document, in the following order:

Input
Formulas used to compute the dates
Formulas which construct the different text portions
The “graphical” elements (payments table, etc.)

Grouping the fields outside the main document allows easier separation and editing. It also minimizes the risk of deleting a formula by mistake when editing the first page, and allows adding visible comments for accompanying the formulas. We additionally mark the beginning of lines containing formulas with a blue arrow (→) to make sure they will not be deleted by mistake.

Sections of formulas of the Word template correspond to the main sections of this document. In each section a green table shows the result of the formulas for preview. This allows entering and checking all formulas before starting the formatting and generating the final texts of the first page. After it will be finished, our template will look like:

Of course, only the first page of the document will be printed. We start entering the formulas on page two, leaving the first page blank for now.

Input data (merge fields)

We start by checking our input data. Input is provided by an Excel file [xls] containing all required customer information (address, invoices, payments, reminder level, etc.) that will be read from Word during merging. The setup for merging Word and Excel documents is presented here: http://office.microsoft.com/en-us/help/HA010349201033.aspx. The provided sample Excel file contains 9 records representing 5 fictive customers, one being at each reminder level and having the line once active (records 1 to 4: [p1] [p2] [p3] [p4]), once blocked (records 6 to 9: [p6] [p7] [p8] [p9]). Record 5 ([p5]) represents a customer receiving a normal paper invoice. The sample letters of section Introduction are generated using this Excel file.

The following table shows all input fields (Excel columns) that will be used in our template:

Merge Field Name	Description
company	Company name of the customer
firstname	Firstname of the customer
lastname	Lastname of the customer
address1	Address of the customer (first line)
address2	Address of the customer (second line)
zip	ZIP code of the customer
city	City of the customer
sip	Customer phone number (02x-550-xxxx)
balanceold0m	Balance of the last invoice
balanceold1m	Balance of the 1 month old invoice
balanceold2m	Balance of the 2 months old invoice
balanceold3m	Balance of the 3 months old invoice
balanceold4m	Balance of the 4 months old invoice
paidold1m	Payments received for the 1 month old invoice
paidold2m	Payments received for the 2 month old invoice
paidold3m	Payments received for the 3 month old invoice
paidold4m	Payments received for the 4 month old invoice
reminder	Reminder level (1 to 4)
blocked	Indicates if the customer phone line is blocked (Y/N)
send	Indicates if the reminder must be sent or not
bvchf	Due amount of the last invoice, preformatted for printing on the payment slip
date	Date when the reminders input Excel was generated
subscriptions	Customer’s subscription fees of the last month (paper invoice only)
calls	Customer’s call charges of the last month (paper invoice only)
manual_charges	Customer’s manual charges of the last month (paper invoice only)

Remarks:

The file contains additional columns that are not described in the previous table. These are control values that will not be used during the merging process.
All merge fields and variables are described in Table of variables

Often the input values are simply displayed using merge fields. Merge fields are references to the records of the source Excel file, each field being named from the column’s first cell. Merge fields are field codes like the others. Their syntax is { MERGEFIELD field_name }. Merge fields can be directly inserted in the Word document with the Insert Merge Field button. For example, to print the address of the customer in the address table, we will insert the following merge fields:

This results in the following display once data is merged from the input Excel file:

Remark: The MERGEFIELD keyword can be omitted. Merge fields are treated almost as other variables, but they can be also highlighted via the View Merged Data button. Removing the MERGEFIELD keyword can cause the variable not to be correctly displayed on document opening, but only after formulas have been updated once.

The date value read from the Excel file will not be displayed directly. It will be decomposed as presented in section Retrieving the date from the Excel file and then used for further calculations.

For control, we start by adding a table that displays all input data. As described in General structure, we start on page 2 of the template. Once the document is merged with the source Excel file, the following result appears in the control table (for the 1^st record of the sample Excel file [xls], the date in the Excel file is the 15^th of September):

Field codes, formulas and parameters of the document

This section presents the formulas that will be used in our template. We enter all formulas step by step. The values are validated at the end of each step. The final page of the document is constructed only when all formulas are entered. All the variables that will be defined in this document are presented in Table of variables.

Skipped records

Some customers are excluded from the reminders or from postal sending. This concerns customers having a wrong postal address, or having paid their invoice late but in full (a reminder would be issued from the invoices data, but is annihilated by a subsequent payment). For these customers the column send does not contain the value send. Processing of record of such customers is skipped in the Word template with the following SKIPIF statement:

Remark: Records are skipped only at printing time. They will appear when using Previous/Next Record buttons.

Dates and periods

In this section, we present the formulas generating all dates and periods.

Retrieving the date from the Excel file

The following formulas read the date column from the Excel file and decompose it into three separate components: year, month and day. Word uses the US format (MM/DD/YYYY) to store dates internally, so we use the same in our source Excel file.

First we store the merge field value in a new variable today. The date is then split into three new variables year, month and day through formatting options (Word directly recognizes the date retrieved from Excel):

These three values will be reused for printing and further date calculations.

Displayed dates

From the split input date, we create new date values required in different parts of the document (texts, payments table, etc.). These dates will be the current month minus 0 to 4 months, to reflect the payment history of the customer. The derived date values will be calculated in Word using arithmetic functions.

The following dates are generated:

issueold0m	issue date (1^st of the month) of the last invoice
issueold1m	issue date (1^st of the month) of the 1 month old invoice
issueold2m	issue date (1^st of the month) of the 2 months old invoice
issueold3m	issue date (1^st of the month) of the 3 months old invoice
issueold4m	issue date (1^st of the month) of the 4 months old invoice
dueold0m	due date (25^th of the month) of the last invoice
dueold1m	due date (25^th of the month) of the 1 month old invoice

The date values of the above table are computed as follows:

Compute the new year and month values (input month minus the number of months to subtract, input year-1 if the interval overlaps two years) and store it in a new variable
The day of the month is fixed. It is either the 1^st or 25^th
The resulting date is created within a QUOTE function, to concatenate text and formula results
The created dates are stored in the MM/DD/YYYY format, corresponding to the internal format of MS Word

Below are the field codes and formulas for setting the date variables of the above table:

Reminder ID

The reminder ID is a unique identifier for every generated reminder. It has the form YYMM01-XXX-XXX-XXXX-N, where YYMM01 is the first day of the month the reminders are generated, XXX-XXX-XXXX is the customer’s phone number, and N is the reminder level (1 to 4). We use the already computed issueold0m date. We only have to display it with format YYMMDD. The values are concatenated within a QUOTE to form the reminder ID. It is stored in the reminderid variable. We also store the reminder merge field value into a new reminder variable:

Last paid invoice

We need the date of the last invoice that was paid in full, (reminder+1) months ago, as it is the first day of the current billing period. It is calculated and stored in variable lastpaid, similarly as shown in section Displayed dates :

Yesterday (last day of previous month)

For the billing period, we will need the last day of the previous month. As Word does not offer date operations directly, calculation is done using arithmetic operations:

The value is stored as MM/DD/YYYY in the variable yesterday.

Remarks:

Variables yestyear, yestleap, yestmonth and yestday correspond to the year, leap/not leap, month and day values of the yesterday’s date.
The following links give more information on the calculation:

http://unappel.ch/public/080920-word-field-yesterday-date-leap-years/ (formulas used in this document)

http://unappel.ch/public/080916-ms-word-field-formulas/ (older version)

http://switzernet.com/public/080904-word-field-process-dates/ [mirror] (older version)

http://unappel.ch/public/080904-word-field-date-calc/ (older version)

Billing period

This is the billing period concerned by the reminder. It corresponds to the period from the issue date of the last invoice paid in full to the day before the last issued invoice. The billing period is formatted as 01.mm.yyyy-DD.MM.YYYY, where:

01.mm.yyyy	issue date of the last fully paid invoice (variable lastpaid)
DD.MM.YYYY	last day of the previous month (variable yesterday)

The billing period is then the concatenation of lastpaid and yesterday, stored in the period variable:

Control table

After all the formulas of this section have been entered, the following result appears in the control table (for the 1^st record of the sample Excel file [xls]):

Building the output document layout

As described in section Introduction, we have two different logics to build the output document, depending on the letter type. Reminders and paper invoices will use specific text portions. Other text portions are the same for both letter types. Formatting is also the same for both types. The following table shows which text portions are used to build each type of letter:

Invoice

Reminder

Why a reminder is issued [intro]

Details of the last invoice [invoices]

Summary of unpaid invoices [invoices]

Wire payment instructions [wire]

Reminder settlement instructions [settlement]

Conditions of the current and next reminder levels [textXr]

The account can be precredited [precredit]

The text portions will be described in the next sections of this document. In the above table, each portion points to the section where the corresponding field code is defined.

Text portions

Reminder rules

Rules describe the conditions of each reminder level (reminder fees, modifications of service, etc.). They are stored as text in the rulesX variable (X=1…5). Rules 1-4 are the conditions of each reminder level. Rule 5 describes what happens if the invoice is not paid after the 4^th reminder, and appears only at the 4^th reminder level. For rules 1-4, the text of the same rule appears in two different cases. Rule n appears as a warning when the customer reaches the n-1 reminder level, and also as a notification when he reaches the n reminder level. The wording of the rule is the same in both cases. The text of the rule changes depending whether the line of the customer is active or not. The line status is read from the blocked merge field.

After all the formulas of this subsection have been entered, the following result appears in the control table (rules are the same for all records of the sample Excel file [xls]):

Reminder texts

The structure of the four reminder texts is stored in the textX variable (X=1…4). Each level text has the same form:

“Because of this reminder, the following standard conditions apply”: rules of the current reminder level
“In case of next reminder, the following standard conditions would apply”: rules of the next reminder level

The rules of each reminder level being the reminder fees, modifications of service, etc. (see Reminder rules):

Remarks:

One of these 4 text templates is used in the main output page depending on the reminder level (see Text body).
The normal paper invoice does not use any of these texts (see Building document introduction)

Invoice/reminder reference

This text contains the reference numbers of the invoice or reminder.

For the paper invoice:

Invoice number
Customer’s phone number
Billing period
Issue date

For the reminders:

Reminder ID
Customer’s phone number

Control table

After all the formulas of this section have been entered, the following result appears in the control table of this section (for the 1^st and 5^th records of the sample Excel file [xls]):

This text appears in the letterhead of the main output document (see Text body).

Intro, settlement, wire and precredit

Four remaining texts are declared in variables intro, settlement, wire and precredit. The first is an introduction on why a reminder has been issued, the second gives explanations on the payments table (displayed for all reminder levels). The third contains our payment information for the paper invoice. The fourth informs that the account can be credited in advance (not displayed for the 4^th reminder):

Control table

After all the formulas of this section have been entered, the following result appears in the two control tables of this section (for the 1^st and 5^th records of the sample Excel file [xls]):

Intro:

Settlement, textXr, wire and precredit:

Invoices table

This table summarizes the balance and payment history of the customer. For the normal paper invoice (reminder=0), we create a table which displays details of the monthly usage. For the reminders, one row appears for each unsettled invoice. The reminder table is a regular Word table having the form:

Issue date of invoice	Balance	Payments of the month following the invoice
01.mm.yyyy	XXX CHF	XXX CHF
01.mm.yyyy	XXX CHF	Due date (25.mm.yyyy)

The last line always contains the data of the last issued invoice. Reminders apply only on previous unpaid invoices and do not apply on the last invoice which is not due yet. The number of lines of the table (number of unpaid invoices) depends on the reminder level. The table with the corresponding number of rows is defined as a function of the reminder level. The table is stored in the invoices variable. IF statements are used to assign to the variable invoices a properly sited table:

Remark: We were unable to build in MS Word a dynamically-sized table.

Control table

After definition of the invoices table, the control table of this section shows the following output (for the 1^st and 5^th records of the sample Excel file [xls]):

Warning image

For reminder levels 3 and 4, a warning text is displayed with the logo of the collection agency that will be mandated to collect the missing amounts in case of non payment. The text and image are stored in the variable warning:

Control table

The following result must appear in the control table of this section for the 3^rd record of the sample Excel file. The table must be empty for records 1 and 2:

This text appears in the warning table of the main output document (see Text body).

Contents of the main output page (first page)

In this section, we present the final layout of the first page of the template. Since all formulas and text portions have already been defined, this page will only regroup all the information to compose the printed letter.

Formatting

This subsection presents the page formatting of our template. The first page of the final document will be printed, folded, and inserted into envelopes with address window. The contents of the first page will be entered after formatting is complete.

Paper and margins

We set the paper size to A4 (default is Letter), with all margins at 1cm of page edges:

Tables

Header table

This table represents the top portion of the page, containing the company logo and contact information. This part is already printed on the paper, here we only set the table for reserving the space. It is a simple table, with one row and two columns. We start by adding the table in the document, and then position it at 0.5cm from page top and page left (right click on the table, then Table Properties):

Row height: 2.5cm, first column width: 10cm, second column width: 10.5cm:

The table must not be visible, so we remove the borders:

The result is as follows:

Information table

The first row of this table will contain the reference numbers of the reminder/invoice. The second row will contain an optional warning displayed for customers reaching the 3^rd and 4^th reminder levels. This table is created in the same way as the header table.

Position: 0.82cm horizontal, 3.5cm vertical

Dimensions: 9.68cm width, 1cm and 3.2cm for the rows heights:

Address table

This table is placed so to be visible in the address window of a B5 envelope. The first column will contain the “P.P.” symbol, the company zip code and city for postal sending (vertical text). The second column will contain the customer address. This table is created in the same way as the header table.

Position: 11.2cm horizontal, 5cm vertical

Dimensions: 3.7cm height, 2cm and 6.5cm for the columns widths:

Bottom table (payment slip)

The bottom of the page contains the red payment slip printed on the reminder letters. Its creation is described here: http://switzernet.com/public/080902-layout-red-slip/.

For this template, some additional information is printed on the slip:

Customer’s phone number	merge field sip
Customer’s address	several merge fields
Reminder summary	several merge fields and variables
Amount due	merge field bvchf

All this information is available as Excel merge fields, or was calculated with the formulas presented in the previous sections. Variables are only displayed on the payment slip without any additional computation. The customer’s address in inserted in the right and left parts of the payment slip. As it doesn’t require precise positioning, vertical alignment of these address fields is simply done with line feeds.

Remark: The amount printed on the payment slip (merge field bvchf) is already formatted in the Excel file using the following formula:

=SUBSTITUTE(FIXED(FLOOR(S2,0.05),2,TRUE),"."," ")

Cell S2 containing the amount to pay as a float number, the formula returns it rounded at 0.05 Frs, formatted as a string with a space instead of a decimal separator. This value can be directly printed on the payment slip, using a fixed width font as courier, since it is formatted for use with a monospace font. Before printing, the vertical position of the amount to pay has to be adjusted manually, because the preprinted payment slips are not 100% accurate. Select the amount text, right-click and choose Paragraph…. For example, if you need to push the text down from 1mm:

Text body

Now that all is set up, we can fill the contents of the printed page of the template. It contains references to the variables presented in previous sections, and constant text that does not depend on the reminder level or invoice. All possible outputs are now generated by a small number of IF statements:

A page break is inserted after greetings line (“L’équipe Switzernet”) to keep the body text and the formulas located on the following pages of the document separated.

Table of variables

The following reference table shows all merge fields and variables defined in the template document with SET formulas. They appear in the table in the same order as they are defined.

Table 1 Variables

Name	Type	Description	Comments and corresponding section
send	Merge field	Indicates if the reminder must be sent or not	Data which is read from the input Excel file Input data (merge fields)
sip	Merge field	Customer phone number (02x-550-xxxx)
reminder	Merge field	Reminder level (1 to 4)
blocked	Merge field	Indicates if the customer phone line is blocked (Y/N)
bvchf	Merge field	Due amount of the last invoice, for printing on the payment slip
company	Merge field	Company name of the customer
firstname	Merge field	Firstname of the customer
lastname	Merge field	Lastname of the customer
address1	Merge field	Address of the customer (first line)
address2	Merge field	Address of the customer (second line)
zip	Merge field	ZIP code of the customer
city	Merge field	City of the customer
date	Merge field	Date of the reminders generation (mm/dd/yyyy format)
subscriptions	Merge field	Customer’s subscription fees of the last month (paper invoice only)
calls	Merge field	Customer’s call charges of the last month (paper invoice only)
manual_charges	Merge field	Customer’s manual charges of the last month (paper invoice only)
today	Formula	Stores the date merge field for further calculations	Convert the date read from Excel to variables Retrieving the date from the Excel file
year	Formula	Year of the reminder generation
month	Formula	Month of the reminders generation
day	Formula	Day of the reminders generation
issueold0m	Formula	Issue date of the last invoice	Generate the required dates for display Displayed dates
issueold1m	Formula	Issue date of the 1 month old invoice
issueold2m	Formula	Issue date of the 2 month old invoice
issueold3m	Formula	Issue date of the 3 month old invoice
issueold4m	Formula	Issue date of the 4 month old invoice
dueold0m	Formula	Due date of the last invoice
dueold1m	Formula	Due date of the 1 month old invoice
reminderid	Formula	Reminder reference number (date-sip-reminder)	Generate a unique reminder ID Reminder ID
lastpaid	Formula	Date of the last invoice paid in full	Calculate the billing period concerned by the reminder/invoice Billing period
yestyear	Formula	Temporary variable for date calculation
yestleap	Formula	Temporary variable for date calculation
yestmonth	Formula	Temporary variable for date calculation
yestday	Formula	Temporary variable for date calculation
yesterday	Formula	Date of the last day of previous month
period	Formula	Billing period concerned by the reminder
rules1r	Formula	Rules for the 1^st reminder	Texts defining the conditions of each reminder level Reminder rules
rules2r	Formula	Rules for the 2^ndreminder
rules3r	Formula	Rules for the 3^rd reminder
rules4r	Formula	Rules for the 4^th reminder
rules5r	Formula	Rules for non payment after the 4^th reminder
text1r	Formula	Text of the 1^st reminder	Define the general structure of the reminders text Reminder texts
text2r	Formula	Text of the 2^nd reminder
text3r	Formula	Text of the 3^rd reminder
text4r	Formula	Text of the 4^th reminder
reference	Formula	Reminder/invoice reference information	Reminder/invoice reference numbers Invoice/reminder reference
intro	Formula	Introduction text displayed on reminders	General texts accompanying the invoice/reminder Intro, settlement, wire and precredit
settlement	Formula	Text accompanying the payments table
wire	Formula	Text informing of wire payment facilities
precredit	Formula	Text informing of possible precredit of the account, web interface, etc.
invoices	Formula	Table summarizing the concerned invoices and payments	Table summarizing the invoices concerned by the reminder/invoice Invoices table
warning	Formula	Warning displayed before sending the customer to collection (text + image)	Warning text and logo for customers who will be transferred to collection Warning image

Bookmarks

As presented in SET formulas and bookmarks, all the variables we set define a corresponding bookmark in the document. Once all formulas have been entered, we obtain the following bookmarks. We also see that no other bookmark is present.

today

year

month

day

issueold0m

issueold1m

issueold2m

issueold3m

issueold4m

dueold0m

dueold1m

reminderid

lastpaid

yestyear

yestleap

yestmonth

yestday

yesterday

period

rules1r

rules2r

rules3r

rules4r

rules5r

text1r

text2r

text3r

text4r

reference

intro

settlement

wire

precredit

invoices

warning

Styles and formatting

Table of styles

This section describes all the styles present in the reminders template with a short description on where they are used.

Style

Description

amounts and values

Amounts and values appearing in the reminder rules

att.

(a)(b)(c)… separating points appearing in the reminder rules

Formula Arrows

Blue arrows (→) marking the lines where the field codes are located

Formula Pages

Default font for the formulas definitions and comments

Letter issue date

Issue date of the letter, on the right top of the template

Normal

Default style for the printed page of the template

Paragraph+space

Paragraph followed by a small spacing

Postal Address

Customer’s postal address, printed to appear in the envelope window

Postal PP

PP logo for postal sending

Postal PP ZIP

Company zip code and city for postal sending

Rule after

Conditions (rule+text) of the next reminder level

Rule now

Conditions (rule+text) of the current reminder level

Slip address

Customer’s postal address, printed on the payment slip

Slip CHF

Amount to pay, printed on the payment slip

Slip info

Invoice/reminder references, printed on the payment slip

Slip SIP

Customer’s phone number, printed on the payment slip

smallcaps

Small capitals text, used in the reminders text

Table Control

Control tables for formulas validation

Table Grid

Default table, for positioning elements on the printed page

Table Paper Invoices

Table Paper Invoices

Table Paper Invoices

Details on the customer’s last invoice, printed on the postal invoice

Table Reminders

Table Reminders

Table Reminders

History of the customer’s invoices and payments, printed on the reminder letters

Wire List

List of possible payment facilities, printed on the postal invoice

Remarks on styles

This section describes some formatting problems that appeared during the redaction of the template. It also proposes solutions to solve them.

Superscript and small caps

When setting text format to the defined smallcaps style, formatting of superscript characters is lost. For example, “1^st reminder” becomes “1st reminder”. To obtain the correct display, we must manually apply superscript format to the selection (in Format -> Font) to obtain the correct display “1^st reminder”. This problem does not appear when using small caps without defining a style.

Mergefields and table styles

A table style with special font formatting is not displayed correctly if the inside content is a merge field using a formatting option. For example, we set a table with default formatting. We insert merge fields in the table cells, once with formatting (here \# "0.00" for 2 digits rounding) and once without:

Then we define a table style (rightmost table column set to bold) and apply it to the table. Formatting is applied correctly:

But after an update of the fields, formatting is lost for the formatted merge field:

To correct the problem, we can manually unset the bold format on the merge field:

Borders around reminder texts

Some problems appeared with the borders around the reminder texts. Depending on how the text was built in the textXr variables, borders appeared merged or with a missing heading line. This was because the border was around an IF statement. To solve this, texts are split into paragraphs using line feeds, and the borders do not contain the IF:

This solution solves the borders problem, but an issue remains. The additional line feed in the first clause of the IF statement appears in the output document, resulting in a blank line. The solution was to change the font size of the corresponding line feed (marked with a red arrow) to a very small size.

References

http://www.gmayor.com/formatting_word_fields.htm

http://switzernet.com/public/080902-layout-red-slip/

http://unappel.ch/public/080916-ms-word-field-formulas/

http://switzernet.com/public/080904-word-field-process-dates/ [mirror]

http://unappel.ch/public/080904-word-field-date-calc/

http://unappel.ch/public/080920-word-field-yesterday-date-leap-years/

* * *