Generation of a Word reminders/invoices template

Switzernet – Christian L.

2008-09-08

(updated 2008-09-24)

 

 

Generation of a Word reminders/invoices template. 1

Introduction. 2

General notes on MS Word formulas. 3

Formula insertion. 3

Alternative way. 4

Setting and displaying a field. 5

Syntax of the IF field code. 6

Showing all fields contents at once. 6

SET formulas and bookmarks. 7

Formulas formatting. 9

Number rounding. 11

Date format 12

Structure of the reminders template. 13

Input data (merge fields) 15

Field codes, formulas and parameters of the document 18

Skipped records. 18

Dates and periods. 19

Retrieving the date from the Excel file. 19

Displayed dates. 20

Reminder ID.. 21

Last paid invoice. 21

Yesterday (last day of previous month) 21

Billing period. 22

Control table. 23

Building the output document layout 23

Text portions. 24

Reminder rules. 24

Reminder texts. 26

Invoice/reminder reference. 27

Control table. 28

Intro, settlement, wire and precredit 29

Control table. 30

Invoices table. 32

Control table. 35

Warning image. 35

Control table. 36

Contents of the main output page (first page) 37

Formatting. 37

Paper and margins. 37

Tables. 38

Header table. 38

Information table. 41

Address table. 42

Bottom table (payment slip) 43

Text body. 44

Table of variables. 45

Bookmarks. 48

Styles and formatting. 53

Table of styles. 53

Remarks on styles. 55

Superscript and small caps. 55

Mergefields and table styles. 55

Borders around reminder texts. 56

References. 57

Introduction

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 1st to 4th 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

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:

 

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:

 

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:

 

Structure of the reminders template

Our Word template will be structured as follows:

 

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:

 

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 1st record of the sample Excel file [xls], the date in the Excel file is the 15th 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 (1st of the month) of the last invoice

issueold1m

issue date (1st of the month) of the 1 month old invoice

issueold2m

issue date (1st of the month) of the 2 months old invoice

issueold3m

issue date (1st of the month) of the 3 months old invoice

issueold4m

issue date (1st of the month) of the 4 months old invoice

dueold0m

due date (25th of the month) of the last invoice

dueold1m

due date (25th of the month) of the 1 month old invoice

 

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

 

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:

               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 1st 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 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 4th reminder, and appears only at the 4th 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:

 

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

 

 

Remarks:

Invoice/reminder reference

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

 

For the paper invoice:

 

For the reminders:

 

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 1st and 5th 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 4th 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 1st and 5th 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 1st and 5th 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 3rd 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 3rd and 4th 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 1st reminder

Texts defining the conditions of each reminder level

Reminder rules

rules2r

Formula

Rules for the 2ndreminder

rules3r

Formula

Rules for the 3rd reminder

rules4r

Formula

Rules for the 4th reminder

rules5r

Formula

Rules for non payment after the 4th reminder

text1r

Formula

Text of the 1st reminder

Define the general structure of the reminders text

Reminder texts

text2r

Formula

Text of the 2nd reminder

text3r

Formula

Text of the 3rd reminder

text4r

Formula

Text of the 4th 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

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

Table Reminders

Table Reminders

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

Wire List

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, “1st 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 “1st 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/

 

 

* * *

Copyright © 2008 Switzernet