A DOCUMENT ASSEMBLY SYSTEM USING
MICROSOFT WORD’s MAIL MERGE FEATURE AND A SIMPLE DATA
BASE —
As Simple or as Complicated As You Want It To Be
Noel C.
Ice
Cantey & Hanger, L.L.P.
2100 Burnett Plaza
(817) 877-2800 (Main no.)
(817) 877-2885 (Ice)
(817) 877-2807 (Fax)
E-mail: teleice@earthlink.net
Web Page: www.trustsandestates.net
Copyright 2002
Noel C. Ice
All rights reserved
A DOCUMENT ASSEMBLY System
USING MICROSOFT WORD’s MAIL MERGE feature AND A SIMPLE DATA BASE —
As Simple or as
Complicated As You Want It To Be
TABLE OF CONTENTS
1.2 See Website For Electronic Version of this Article, Together With All Appendices.
1.3 A Note on the Printed Format of this Article.
2.1 Concept One: FIELDS and FIELD NAMES.
2.3 Concept Three: The DATABASE.
2.5 Concept Five: Correspondence Between FIELD NAMES and DATA.
2.6(a) MAIL MERGE Step One: Designate the Document as a MAIL MERGE Document.
2.6(b) MAIL MERGE Step Two: Designate Your DATA SOURCE.
2.6(c) MAIL MERGE Step Three: Merge to New Document.
ARTICLE 3 A SIMPLE EXAMPLE OF THE CONCEPT
3.1 A Generalized Form Letter.
3.2(c) Getting the Information Out of the DATABASE and Into the Form.
ARTICLE 4 USInG a DATABASE PROGRAM
4.1 Any Table Will Do as a DATABASE.
4.2 Any DATABASE Program Will Do As a Table.
4.4 FileMaker Pro as a DATABASE.
ARTICLE 5 How to Run Filemaker And Use it to create a Mail
merge Document, step-by-step
5.1 Create or Open a DATABASE.
5.5(a) Export Your Data as Text or as a Merge File.
5.6 Create Your Mail Merge Form In Ms Word.
ARTICLE 6 inserting FIELD names into your FORMS and USING
IF-Then Fields
6.3(a) An Example of an If-Then Statement.
6.3(b) Problems With Quotation Marks.
6.4 Using “AND” and “OR” Conditions—Boolean Programming.
6.5 DATA That is Particular to Each Document, That You Don’t Want to Store in a DATABASE.
6.6 Appendices A & B: A Mail Merge Letter, and the Finished Form.
7.1 Browse Mode and Layout Mode.
7.2 Finding Records in Find Mode.
7.3 FileMaker on a Network—Sharing DATABASES.
7.4 An Original Use of the “Value List.”
7.7 Don’t Use Quotation Marks in Your DATABASE.
ARTICLE 8 Things Get Bigger, Better and More cOmplicated
8.1 Two and Three FIELD DATABASEs Are Not Enough.
8.2 A Basic Contacts DATABASE.
8.3 Calculated and Concatenated FIELDS.
ARTICLE 9 A FORM System Even Your partners Can use
9.1 Getting Your Partners to Use Your DATABASE Requires More Cooperation Than You are Likely to Get.
9.2 Using Your DATABASE as a Form Generator.
9.3 Using Your DATABASE as a Form Generator-Part 2.
10.1 Examples of MAIL MERGE FORMS And BRACKETED FORMS In The Appendices.
10.1(a) Two BRACKETED Will FORMS and the MAIL MERGE FORM That Created Them.
10.1(a)(1) Exhibits C & D, a Simple and a Pourover BRACKETED Will FORMS.
10.1(a)(2) How to Use the BRACKETED FORMS Generated by the MAIL MERGE FORM.
10.1(a)(3) Appendix E, the MAIL MERGE FORM That Generated The Two BRACKETED Will FORMS.
10.1(b) Appendices F & G, an IRA Beneficiary Designation Form.
10.1(c) Appendices H & I, a Power of Attorney Form.
10.1(d) Appendices J & K, an Engagement Letter Form.
10.2 The MAIL MERGE FORMS That Generated the BRACKETED FORMS.
10.3 Dozens of DATABASES and Forms on the Web.
10.4 Making Practical Use of My Forms.
ARTICLE 11 PartinG Shots (Some at Microsoft)
A DOCUMENT ASSEMBLY System
USING MICROSOFT WORD’s MAIL MERGE feature AND A SIMPLE DATA BASE—
As Simple or as
Complicated As You Want It To Be
By Noel C. Ice
This is not going to
be a detailed instruction manual for operating Word’s MAIL MERGE feature, nor
is this going to be a how to use FileMaker Pro workshop. Rather, this article
is going to be mostly theoretical, although where I have found the instruction
manuals to be extremely deficient, I will offer suggestions.
I am going to post this article and all attachments at www.trustsandestates.net —or, more specifically at http://www.trustsandestates.net/FormBook.htm—, as well as on the ACTEC website if I can. You should be able to download everything to your computer and play with it there, including some FileMaker DATABASES.
I am faced with a difficulty in showing you the
programming involved in a MAIL MERGE document, because if I give you an example,
Word will want to replace the FIELD CODES with what they stand for. The way
around this is to choose “Print Field Code” from the Options-Print dialog box,
but if I do that, my Table of Contents and pagination will show up as FIELD
Codes. Word is so powerful that there is undoubtedly a way to get around this
properly, but as it could take me a week to figure it out, I am simply going to
put most of my hard core examples in Appendices for now, printing the FIELD
CODES or not, depending on whether I am demonstrating a finished form, or am
trying to demonstrate the coding. The few examples I will use in the article
proper may be dummied up a little, just to make the point.
All of us have created FORMS in our law practice. These FORMS typically have blanks, where variable information is later inserted when the FORM is converted to an actual legal document or letter. For example, a simple FORM Will might contain a blank for the client’s name, or for the appropriate personal pronoun to use when referring to the client’s spouse. It might occur to you that when converting a FORM to a finished document, instead of typing in the client’s name over and over again, that you could insert NAMEOFCLIENT in your FORM, and then use the GLOBAL SEARCH AND REPLACE feature on your word processor, typing the name only once. You may type the name correctly or incorrectly, but, in any case, it will at least be the same throughout your document. The same approach can be used to globally insert the correct personal pronoun (say, HeSheSpouse in some of my examples below). Think of NAMEOFCLIENT and HeSheSpouse as FIELDS, holding variable information. FIELDS have names, FIELD NAMES. The FIELD holding the client’s name is called NAMEOFCLIENT in my example. NAMEOFCLIENT is the FIELD NAME of the FIELD that answers the question: “What is the client’s name.”
Your first FORM system undoubtedly was similar to the GLOBAL SEARCH AND REPLACE system just described. GLOBAL SEARCH AND REPLACE is a low level, but a serviceable, basis for a FORM system. In fact, later, I am going to suggest a method of generating FORMS using a DATABASE and a “MAIL MERGE FORM” at the back end, with a “BRACKETED FORM” using GLOBAL SEARCH AND REPLACE at the front end. This is because you may have partners for whom GLOBAL SEARCH AND REPLACE is still high-tech, and they aren’t going to move beyond it.
When you search and replace NAMEOFCLIENT in the example above with say, “E.C. and Lotta Money,” you have supplied the DATA for the NAMEOFCLIENT FIELD. If you have lots of clients, then your NAMEOFCLIENT DATA will be different for each one. (You will have a separate RECORD for each client, as explained below.) Ideally, you would like to type in the client’s name once or twice in your life, and be done with it, using that name in all documents that you ever prepare for the client. That is where a DATABASE comes in handy.
Think of a DATABASE as similar to a TABLE. In fact a TABLE can be a DATABASE.
|
NameOfClient |
Salutation |
MailingAddress |
TelephoneNo |
Email |
|
E.C. and Lotta Money |
E.C. and Lotta |
c/o
Touch of Grey Hair Salons, Inc. |
(817) 999-9999 |
ECLotta@cash.net |
|
G.O. Numbers, C.P.A. |
Mr. Numbers |
|
(817) 926-1258 |
GoGo@earthlink.net |
|
Dr. and Mrs. Jones |
Bill and Sarah |
24523
Hi-Line Dr. |
(817) 292-2675 |
BSJones@aol.com |
The table above is a DATABASE. The first row is your Header, and it contains the FIELD NAMES. The information in each cell is your DATA.
Each row, after the first row in the TABLE above, is a separate RECORD (there are three RECORDS in this example). The information in each CELL in each Record is your DATA.
Note that there is a
one-to-one correspondence between each FIELD
NAME and the information in the CELL of any particular RECORD. This is a
clue to how the Microsoft MAIL MERGE feature —to be
discussed momently— works.
Since its inception
almost two decades ago, Microsoft Word has a feature that has been a part of
the program called “MAIL MERGE.” Most people probably use this feature
exclusively for merging names and addresses into FORM letters being sent to a
number of different clients at once, and the feature does, indeed, perform this
task well. The idea is that the user would produce a number of RECORDS, one for
each client, using a TABLE, such as the one above, and then could generate a
mass mailing based on the DATA in the TABLE.
In order to run a MAIL MERGE,
you first have to designate your Word document as a MAIL MERGE document. This
is variously accomplished, depending on which version of Word you are using,
but, in any, case, it involves no more than a mouse click. In Word XP (2002),
simply click on the “Main Document Setup” button at the far left end of the
MAIL MERGE toolbar.
If the TABLE above were on a separate page you could simply designate it as your DATA SOURCE. This too is variously accomplished, depending on which version of Word you are using; but, in any, case, it involves no more than browsing and clicking on the DATA SOURCE you have selected. In Word XP (2002), simply click on the “Open DATA SOURCE” button, which is the next to first button on the far left end of the MAIL MERGE toolbar.
The last step is to hit the
Merge to New Document button on the tool bar. In Word XP (2002), simply click
on the “Merge to New Document” button, which is the fourth to
last button on the far right end of the MAIL MERGE toolbar.
Let us say that you want to
create a simple MAIL MERGE FORM letter for writing any client you desire at any
time. You could just use a copy of the last letter that you prepared for the
client; or you could create a blank letter for the client as a template, and
use it each time. But what if you live in
Suppose
your generalized FORM letter is nothing more than:
{MERGEFIELD
NAPLUSPHONES}
RE:
Dear {MERGEFIELD SALUTATION}:
TEXT
Yours very truly,
Noel C. Ice
You have two FIELDS: NAPLUSPHONES and SALUTATION. You need DATA, preferably in a DATABASE, which keeps track of your clients name and address and how you typically greet them in correspondence.
You need some place to record and store your address and salutation information. You could put it in a TABLE created either in Word or EXCEL. It could have as many FIELDNAMES as you desire, and as many RECORDS as you have clients. Let’s keep it simple for now, and just use a two FIELDNAME, three RECORD TABLE for our DATABASE, such as the following:
|
NAPLUSPHONES |
SALUTATION |
|
E.C. and Lotta Money |
E.C. and Lotta |
|
G.O.
Numbers, C.P.A. |
Mr. Numbers |
|
Dr.
and Mrs. Jones |
Bill and Sarah |
I actually designated this paper as a MAIL MERGE letter. I then copied the above TABLE onto another document, and named that document ACTEC-TABLE. Next, I went to my Word Merge Toolbar, and clicked on the Open DATA SOURCE button and quickly located and selected the ACTEC-TABLE document. I then clicked on the MAIL MERGE Recipients button and picked the RECORD containing E.C. and Lotta’s info. If I hadn’t done that, when I performed my Merge, I would have gotten three documents, and I only wanted one. I then just clicked on the Merge button and merged this whole Article and got one that looked just like it, except that instead of
------------------------------------------------------------------------------------------------------------
«NAPLUSPHONES»
RE:
Dear «SALUTATION»:
TEXT
Yours
very truly,
Noel C. Ice
------------------------------------------------------------------------------------------------------------
I got:
------------------------------------------------------------------------------------------------------------
E.C. and Lotta
Money
c/o Touch of Grey Hair Salons, Inc.
2525 Mars Hotel
999
New
(817) 999-9999
ECLotta@cash.net
RE:
Dear E.C. and Lotta:
TEXT
Yours very truly,
Noel C. Ice
------------------------------------------------------------------------------------------------------------
Again, if I
wanted, I could have selected all three records, and I would have gotten three
versions, instead of one; in other words, in addition to the above, I would
have also gotten:
G.O. Numbers,
C.P.A.
(817) 926-1258
GoGo@earthlink.net
RE:
Dear Mr. Numbers:
TEXT
Yours
very truly,
Noel C. Ice
------------------------------------------------------------------------------------------------------------
Dr. and Mrs. Jones
24523 Hi-Line Dr.
(817) 292-2675
BSJones@aol.com
RE:
Dear Bill and Sarah:
TEXT
Yours very truly,
Noel C. Ice
Microsoft is all but blind to the fact that MAIL MERGE is much more powerful, and much more useful, performing functions way beyond the production of simple mass mailing FORM letters. Using only one record, or at most two records at a time to create a document for a client using a MAIL MERGE with a legal FORM is the concept that I am suggesting. I actually have little use for generating lots of mass letters; though from time to time, this is very useful, and fortunately this can be accomplished very easily using the same system.
Attached as Appendices are a number of documents that illustrate the point.
Any Table will do as a DATABASE. With Tables in Word you
can sort in all kinds of interesting and helpful ways. You can even perform
mathematical operations in the cells, and export the results in your merged
documents. However, Excel is much more powerful, and is almost as simple to
use. You could begin to use the system I am suggesting using Word or, better
yet, EXCEL, as a simple DATABASE to keep track of your clients. If you desire to
go to a full-fledged DATABASE program, you can always export your DATA into it,
by simply matching your FIELD NAMES to the FIELD NAMES in your new DATABASE.
Although you don’t
need a DATABASE program to create a DATABASE that Word can use to perform MAIL
MERGE operations, you will find that your flexibility is greatly increased if
you use a DATABASE to create your Table. Any DATABASE will do. That is the
ACCESS comes bundled in MS Office Professional, and therefore there will no doubt be lots of people using it. I started out using MS File, but it was abandoned by Microsoft and replaced by several other Microsoft DATABASEs. Despite history, it’s a good bet that Microsoft is going to stick with Access for a while, so ACCESS might be a good choice for the truly committed. However, by all accounts, ACCESS is not easy to learn or use. That is all I need to know. It may be more powerful than other DATABASEs, but, if so, that power is not anything I need for document assembly.
I have been using FileMaker Pro for years, and am very satisfied with it. I have been told that it is definitely easier to use than Access, and that is important. I cannot afford to make a career out of DATABASE programming, so ease of use is critical. Further, I have to train my staff to input DATA, and time is money in that department as well.
FileMaker has been around for years. It has a popular following, and shows every sign of staying the course. For what it is worth, it works just as well on the Mac as it does on the PC.
If you don’t get too fancy, you can be up and running and using FileMaker, for purposes of implementing the system being proposed in this paper, in a couple of hours.
Once FileMaker is installed and opened, you will be presented with a dialog box asking you if you want to create a new DATABASE or to open an existing one. Should you pick “create a new DATABASE,” you will be prompted to give it a name and a location, and then you will immediately be asked to create FIELDS.
Create as many FIELDS as you like. You can create new FIELD Names and change the names of old FIELD Names at any time, as the need arises.
You will probably begin by using Text FIELDS primarily, but you will be given the option of making a FIELD a text, date or a calculated FIELD, to name but a few of your real options.
Because FileMaker is a powerful DATABASE, you can use calculated FIELDS to compute just about anything. It works much the same way that spreadsheet programs such as Excel work. For purposes of the system that I am advocating today, you could get by perfectly well using text fields alone. However, there is a lot of useful power that can be tapped by using calculated FIELDS. I use calculated FIELDS to compute probate and tax due dates, and I find this function to be very handy. I also use calculated FIELDS to combine or concatenate DATA, as explained later. For example, I use a calculated FIELD to combine all of my mailing address information (name, street address, state and zip) into one FIELD.
When you are done creating your first FIELDS you will be presented
with a vertical LAYOUT showing each FIELD in a box, and one record at a time. You
can create as many different LAYOUTS as you want, allowing different view of
the DATA. I suggest that, at first, you stick with the vertical LAYOUT that
FileMaker offers as a default. You can create a horizontal layout, showing all
of your records if you want. This is simply a glorified version of the Table
concept. After you have thirty FIELDS or more, I find the horizontal
layout/TABLE layout to be cumbersome, myself.
For our purposes LAYOUTS are not that important. FileMaker thinks that you are going to use your DATABASE to produce REPORTS, invoices, etc., and you can. So there is naturally a great deal of emphasis in the instruction manual and tour in creating presentation LAYOUTS. Occasionally, I want to print a LAYOUT, but most of the time, I just want to extract the DATA, and use Word to create my printed document.
You can type just about anything in a LAYOUT, almost as if it were a piece of paper. You can give instructions for entering DATA in a particular FIELD right on the LAYOUT. I have a trick, discussed in the next Article of this outline, for putting my special instructions in a pop-up box called a “Value List,” and assigning a different Value List to each FIELD.
Once you have created your FIELD Names, you are ready to enter your DATA. You enter DATA separately for each Record (or for each client). FileMaker has an easy Find feature to find the record or records that you need. After I have entered or changed DATA for a particular client, I like to export that record into a little text file that I never look at. If I did look at it, it would have the FIELD names at the top in one paragraph, separated by tabs or commas, followed by the DATA in another paragraph in which the DATA is separated by commas or tabs. You could create a real long Table out of it if you wanted.
After you are done, you are ready to access the DATA in a MAIL MERGE file. First of all, use the Find feature if necessary to find the record(s) you want to use. Next, go to the File Menu and click on Export Records. You are given a jillion formats to pick from. Text will work, but there is actually a dedicated type called “Merge Files *.mer” that is tailor made for the job, so pick it.
You can actually link directly to your commercial DATABASE if it supports the Open DATABASE Compliance (ODBC) protocol. Both ACCESS and FileMaker support ODBC. Theoretically, using ODBC, you can skip the export step, and link directly to your DATABASE, and use the “Mail Merge Recipients” button to pick the records you want. I have experimented with ODBC, and it is pretty neat; however, my DATABASES are so big, that I have found that it is simpler (and less likely to crash) if I export and save a client’s RECORD as a Merge text file, and access that text file as my DATA SOURCE whenever I want to run a FORM for the client.
Go to http://www.maclane.com/mail_merge_in_microsoft_word_wit.htm
for an explanation of how ODBC and FileMaker work together with Word.
Here are a couple of tips.
§ If you use FileMaker to export a text based DATA SOURCE using the merge (.mer) format, then when you are browsing to select your DATA SOURCE in Word, the “Files of Type” box at the bottom of the browse window will default to “all DATA SOURCEs.” The “all DATA SOURCEs” option does indeed contain dozens of formats, but unfortunately the “.mer” format is not among them, which is interesting, and speaks volumes about the Microsoft philosophy regarding competition. Simply go up one on the list and select show all files, and you will be able to choose a “.mer” source. In the worst case, you could always use a pure text file to hold your DATA.
§
Occasionally, Word wants you to “confirm your DATA
SOURCE” after you have selected it, which involves an irritating extra step. To
turn this option off, go to Options-General and uncheck “Confirm conversion at
Open.” This took me all of one Saturday afternoon to figure out, so I pass it
on here.
Any Word document can be used as a MAIL MERGE document. Just click on the “Main Document Setup” button in the MAIL MERGE toolbar, and select “Letter.” That’s right; Word thinks that the only use for this powerful feature is to create FORM letters for mass mailings. This is incredibly ignorant. Somebody should tell Bill.
Next you have to insert FIELDS into your FORM “letter.” I
discuss that below, in a separate Article. After the FIELDS are inserted, you
simply click the “Merge to New Document” button, and presto, you have the
almost finished product. I say almost finished, because no matter how elaborate
your system, there is still a place for a human being in the production of
legal documents. That role is not going to be subsumed by any computer program,
no matter how much the financial planners may wish.
Inserting FIELD NAMES into your FORMS is a snap. There is an Insert Merge FIELDS button on the MAIL MERGE toolbar that lists all of the FIELDS in your DATA SOURCE, assuming you have selected one. You can insert any of the available FIELDS with a mouse click.
It helps at this stage to
have Word programmed to show Field Codes (Options, View, Show Field Codes);
else things can get pretty confusing.
Virtually everyone I have ever talked to who has designed a document assembly system using a DATABASE, puts variable paragraphs in the DATABASE itself, and then programs the word processor to insert the right one. Note that I am not doing anything remotely similar to that here. I do all of my real programming (beyond the creation of a few calculated FIELDS) in my Word document, a document that I can touch and feel and change much more easily than in any DATABASE.
There are several additional reasons for keeping all of my
drafting language in Word, rather than in the DATABASE. The system I am
advocating in this paper is not dependent on any particular DATABASE, nor are
you. Although I love FileMaker and, I confess, hate Word (I’m sorry, but I do),
I am quite sure that Word is going to still be here when I retire, whether it
deserves to be or not. I would rather depend on Word than on any DATABASE
program for long-term survivability, whether that DATABASE program be Access, FileMaker or Dbase II. For all I know, I may
switch DATABASES someday, but I hope to will retire before I learn
another word processing program.
I have been told that FileMaker is the easiest of the relational DATABASE programs to use, and since Claris, the company that makes it, is very large, stable and profitable, and since I have found FileMaker to be everything and more that I would want in a DATABASE program, I am sticking with it. But I am hedging my bets, because I realize that creating the DATABASE is the easy part. It is the legal FORMS I have designed that I don’t ever want to lose. They are designed in Word where I feel they are assured a long life.
I do my programming primarily by using IF_THEN statements in the Word document, not in the DATABASE. If the answer to a question in the DATABASE, as reflected in a particular FIELD, is one thing, then Word inserts one paragraph; else, another. That is basically it, and I will explain this approach in more detail below. (I make an exception for Boolean AND & OR statements, for reasons explained below.)
After a while, you will want to make the system a little more complicated, by using IF-THEN conditions. There is an Insert Word FIELD Button that makes this easy. IF-THEN conditions are the mainstay of my programming, but they don’t have to be yours. If you want to keep it simple, just use FIELDS with no conditions. However, if you want to have a really fancy FORM for all seasons, you will have to use IF-THEN statements to tell you what paragraphs to insert.
The WORD MERGE Toolbar makes IF-THEN constructions fairly easy for simple one or two sentence constructions. I use IF-THEN constructions to insert many paragraphs, and I often nest within them many more IF-THEN constructions. This is not as difficult as it seems, but it takes some getting used to, particularly where multiple conditions are nested. Once you figure out how it works, you can paste between the quotation marks and get the result you want.
Interestingly, this whole process was a hundred times easier years ago, in earlier versions of Word, particularly the old Macintosh versions. This is not exactly what I would call progress. Why Microsoft decided to make this feature so much more difficult to use is frankly quite beyond my ability to comprehend. I think it is because the company is so big it long ago lost what little common sense it started out with. I will say that MAIL MERGE in Word 2002 is easier to use than in the last few versions, so not all progress at Microsoft is retrograde.
An IF-THEN construction might look something like this:
{If {MERGEFIELD SIMPLEWILL } = "y" "[Simple Will paragraph]" "[Complicated Will paragraph]"}
Your DATA for the SIMPLEWILL FIELD in your DATABASE would presumably by either “y” or “n”. When you perform your Merge, you will get one result or the other, depending on whether the SIMPLEWILL FIELD is “y” or not.
Because Word uses quotation marks to separate the possible outcomes of an If-Then query, you will cause all kinds of problems for yourself if the text you are inserting has quotes within it, unless you put a back slash in front of the quotes, like so:
{If {MERGEFIELD SIMPLEWILL } = "y" " This is a \"simple\" will. " " This is a \"tax-planned\" will. "}
It might be necessary to perform
a little post-merge clean up work, depending on how complicated the document
you merged out of was. Because of formatting problems in the program, I have
found it best to separate long nested IF-THEN instructions, which contain
within them differently formatted paragraphs, by blank paragraphs, beginning
and ending with one common style. (This shouldn’t be necessary, but it is.)
When I am done with a merge, I will often end up with extra blank paragraphs as
a result. Use search and replace (or write a macro) to find all instances of ^p^p and replace them with ^p. Get rid of all the \” the
same way.
Oh how I miss the old days, when Boolean programming in MAIL MERGE documents was so, so easy. The geniuses at Microsoft took what was a perfectly straight forward and user friendly programming tool and made it all but incomprehensible. In fact, the situation was so bad that even the examples that Microsoft used in its previous Help files were flat out wrong, and would not work. Imagine my frustration in figuring out how to make Boolean instructions work, and then trying to help Microsoft understand what the problem was, since there is an institutional reticence at that company to admit that anything is wrong.
Suffice it to say, that the Help examples are now correct, but that the method for programming is so odd that just about the only way to do it is to copy the example, and then substitute your own terms. I find it easier to do my Boolean programming in FileMaker. For the record, here is Microsoft’s example, taken from a Help File, of what an AND statement is supposed to look like.
Example 1
In this example, COMPARE fields examine the data fields CustomerNumber and CustomerRating as Word merges each data record. The AND function of the = (Formula) field returns a value of "1" (true) if both of the data field values indicate a satisfactory account, in which case Word prints the first text in quotation marks.
{ IF { = AND ( { COMPARE { MERGEFIELD CustomerNumber } >= 4 }, { COMPARE { MERGEFIELD CustomerRating } <= 3 } ) } = 1 "Satisfactory" "Unsatisfactory"}
Isn’t that just wonderful! This is the best they can do? I am sure that a dedicated programming type of person would see this as being as elegant as Schubert’s Trout Quartet, but not I. Again, I just copy that example, and make substitutions, since trying to recreate the syntax from scratch is more than I care to do.
It would be a real improvement if future versions of Word
added AND & OR statements to the “Insert Word Field” drop-down box on the
MAIL MERGE toolbar (like they did with IF-THEN statements.)
It does not make sense to have a FIELD in a DATABASE that tells you whether a letter will have an enclosure or not, or whether it is going to be faxed or emailed or FedExed. On the other hand, you don’t want to format each letter by hand to address this issue. Microsoft has an ASK FIELD that allows you to ask a question each time you merge a FORM, and to prepare the FORM in accordance with the answer to the question. The way it is done is somewhat bizarre and involves, of all things, creating a BOOKMARK for the name of your FIELD, but once you figure it out, it comes in handy.
See Appendix A for a copy of my typical MAIL MERGE FORM letter. It is much more complicated than yours needs to be, but I have been doing this for almost twenty years and can afford to add more than the usual bells and whistles. Since I expect that the mode of delivery will vary with each actual letter, this FORM asks, each time it is run, whether it is going to be sent certified mail, faxed, FedExed, emailed, etc. I answer “y” or “n” as the case may be. There are prompts for certified mail numbers if I answered “y” to the certified mail question; or, if I answered “y” to the FedEx question, it will ask for the FedEx Airbill number.
If the letter is run using my special FORM RECORD as the DATA SOURCE, you get Appendix B, a BRACKETED FORM. Imagine that all of the bracketed FIELDS are actually real client DATA, and you will get the idea. For purposes of this Article, I simply typed in place holders as my DATA in a special RECORD. However, by replacing MERGE FIELDS with bracketed place holders, I have created a BRACKETED FORM out of a MAIL MERGE FORM, one that even my partners can figure out. More about this technique later.
There are several modes of viewing your FileMaker DATABASE. Two of the most common are Layout mode and Browse mode. You use Layout mode when modifying the way your DATA is laid out. You use Browse mode to select and enter DATA.
With the exception of my financial statement DATABASE, I do most of my design in my Word documents, and simply use FileMaker to hold my DATA. However, I do like to use various LAYOUTS in FileMaker to help organize my DATA. For instance, if all I want to see is the client’s name, address and telephone number. I will create a new LAYOUT, and insert just the FIELDS that hold that information. Similarly, if I want to see only the marital deduction options I have chosen, I use a specially designed layout which has that information only. The point is that I don’t put all of my FIELDS in one LAYOUT. However, FileMaker will create a LAYOUT with all of your FIELDS in it automatically, if you ask it to.
Since I have at least one RECORD for each client, I have
to have a way to find that client’s RECORD in order to export only the DATA I
need to create a document for the client using Microsoft Word’s MAIL MERGE
feature. In order to do that, I switch to Find mode
and type in some sort of identifying criteria into whatever FIELDs
I select, and I will get all RECORDS that match the criteria. Since every lawfirm assigns each client one or more unique file
numbers, a search by file number will always do the job. Mostly, though, I just
search by last name, and discard the spurious look alikes.
FileMaker can easily be configured so that more than one
user can access a database at the same time. You have to determine what
protocol to use. I use TCP/IP and it works well enough. However, if you want to
do something more than adding and retrieving DATA —for instance, if you want to
add new FIELDS or change the calculations in existing FIELDS—, you have to be
the only one with the DATABASE open.
You need to have something in the DATABASE to tell you just what DATA your FIELDS are supposed to hold. There is a text tool that allows you to write directly on a LAYOUT FORM. Remember, FileMaker thinks you are going to print FORMS, so that part is easy.
Since I use lots of different layouts, I like to have one set of instructions which I can call upon to tell me what a particular FIELD is supposed to do. That way I don’t have to retype my instructions every time I use a FIELD in a LAYOUT. I have discovered a FIELD Format tool which allows me to assign an editable pop up “Value List” to a FIELD. FileMaker thinks that the primary use for this tool is to create yes no radio buttons and the like to ease DATA entry. However, I use it to give me and my secretary instructions to follow, or questions to answer, in filling in the DATA in the FIELD.
I create an editable pop up Value List for each FIELD I create. I then assign that Value List to the FIELD box in each LAYOUT. Again, I think that FileMaker has a completely unrelated use for these Value Lists, but for my purposes, whenever I (or my secretary) click on a FIELD, a drop-down box appears with instructions and suggested answers. Suggested answers can usually be entered as DATA by selecting and clicking the answer that is suitable. To assign a Value List to a FIELD, simply right-click on it in LAYOUT mode, and you will be given the option to create or assign a value list to the FIELD. It is really pretty easy.
I have recently discovered that it is very useful to assign a color to different types of FIELDS. For instance, all of my calculated FIELDS are now red. That keeps me from trying to enter DATA into them. My FIELDS that are actually located in another DATABASE, but which I have imported using FileMakers relational capabilities, I color blue, so that I know I am changing something located elsewhere, if I start to muck with a blue colored FIELD. Black means I am to enter DATA in the FIELD.
FileMaker thinks that you are creating your DATABASE in
order to compile and print a REPORT, and so there are quite a few layout and
design tools in the program. I seldom, if ever, use it for that purpose, though
once and a while it comes in handy. I designed a financial statement DATABASE,
where each record represents an asset, and I am able to generate
It is extremely important not to use quotation marks in
any of the DATA in your DATABASE is you are exporting records in text format as
your DATA SOURCE. The reason is that Word will interpret a quotation mark as
the beginning of a new FIELD, whereupon you will have more FIELDS and FIELD
NAMES and all hell will break loose. There is an option in Word for
interpreting slash quotes (\”) in the DATA SOURCE as true quotes, but I have
never experimented with that feature.
In my introduction
above, I tried to demonstrate how a simple
I recommend that you start with a simple FileMaker (or any other) DATABASE that keeps track of little more than the names and addresses of your clients. Here are a half dozen quick examples from my Contacts_DATABASE:
«Full_Name»
«Business_Title»
«Business_City»
«Business_State»«Business_Street»
«Business_ZIP_Code»
«PhoneBusMain»
«TOWHOM»
Alternatively, you could put most of the above information into one FIELD called, say, NAMEANDADDRESS (or NA). The problem is that you might want to extract or sort by (for instance) just the zip code. Knowing this, I use a separate FIELD for every piece of information, and then, for simplicity sake, create a calculated FIELD called «NA» (NA stands for name & address) which combines, or concatenates, several FIELDS.. I create the FIELD by giving it a name, and designate it as a calculated FIELD. In the dialog box that then appears, I typed :
If(TOWHOM="N/A","",TOWHOM& "¶") &
If(HOMEORBUSINESS="H",HOMEADDRESS,If(HOMEORBUSINESS="B",BUSINESSADDRESS,If(HOMEORBUSINESS="O",ADDRESSOTHER,"fixxxxxxxxxxxxxxxxxxxxxxxxx")))
This combines a couple of concatenated FIELDS. It’s not as complicated as it looks. You can probably figure it out for yourself. TOWHOM is a FIELD in which I type the version of a person’s name that I would use in the address block of an envelope. If TOWHOM is blank, then “” means don’t give me a blank line. I have a FIELD for the client’s full name, instead of trying to concatenate the first and last name, because, there is no standard convention: some people use their middle name, some people use their first two initials, etc.
I usually want both home and business information in my DATABASE, but I will typically mail letters to one or the other, and I make that decision by entering an H, a B or an O (for other) in the HOMEORBUSINESS FIELD. You can see from line two, that if HOMEORBUSINESS was H, then the HOMEADDRESS FIELD will be used. If HOMEORBUSINESS was B, then the BUSINESSADDRESS FIELD will be used. And You can see from line two, that if HOMEORBUSINESS was O, then ADDRESSOTHER FIELD will be used.
HOMEORBUSINESS, BUSINESSADDRESS and ADDRESSOTHER are likewise calculated FIELDS. The FORMula for BUSINESSADDRESS is:
If(Company="N/A","",Company &"¶") &Business_Street& "¶" & Business_City& ", " & Business_State& " " & Business_ZIP_Code
You can probably figure this one out for yourself too. If there is no company name, then I don’t want anything in that place. Hence If(Company="N/A","". The comma stands for “ELSE”, and in this case, the ELSE is “else insert the DATA in the Company FIELD.” After that I want a new line (&"¶"), followed by the rest of the relevant FIELDS.
I use calculated FIELDS for all sorts of purposes. If you want to know the date (including the day of the week) that occurs nine months after date of death, you can quickly create a calculated FIELD to do it. If the day is on Saturday, you can tell it to add two days; if on a Sunday, add one.
In FileMaker, calculated FIELDS are fairly straight forward, and easy to figure out for the most part. It is not nearly as hard as you might think.
FileMaker is a fully relational DATABASE. You could put all of your DATA in one DATABASE, and not have to worry about relations. However, I have found that a DATABASE can get very large and unwieldy if you do that, and I find it more convenient to have several DATABASEs performing different functions, which are related to one another. For example, I have a basic contacts DATABASE. This belongs to my secretary, and (theoretically) I am not supposed to ever have to even open it.
My law practice requires that I use FORMS for business organizations, probate, estate planning, trusts, and qualified plans. Each one of these areas is specialized, and I prefer to have a DATABASE for each. However, many of my clients need both business and estate planning FORMS, or qualified plan and trust FORMS. In any case, I like to have my contact information in one central file: my Contacts DATABASE. But I need a lot of the same information in my other DATABASEs, and I don’t want to retype the information if I don’t have to. So what I do is to create a FIELD in, say, the business DATABASE, that is generally identical to a similar FIELD in the Contacts DATABASE (though it could have a different name if I wanted it to). I make this a calculated FIELD in the business DATABASE that looks something like this
COMPANY= Contacts::COMPANY.
This is all done by
browsing and double clicking on the Company FIELD from a convenient dialog box
that reads the FIELDS in the Contacts DATABASE. First, I define the
relationship as being something like “if the FILENUMBER FIELD in the Business DATABASE
= the FILENUMBER FIELD in Contacts then relate to that record.” If you find a
misspelling or some other change, you can even make it in one DATABASE, and
have the change made in the DATABASE it came from, which is
Face it: no matter how simple FileMaker is, you are going to be very lucky indeed if you can get everyone in your practice section, much less in your firm, to buy into it. If you can, then more power to you. I have a second best solution.
With a Will DATABASE, you don’t have to have an A will, a B will, a C will, and maybe a D will. You can have dozens of FIELDS asking dozens of questions, resulting in hundreds of potential will formats. The client DATA will of course be different, but if you put all of your yes/no questions in one DATABASE, and all your client information in another, you will find that each time you create a document for a client, you can copy the yes/no stuff, and use it as a template for the next client who has a similar estate plan. For instance, after you have prepared a couple of dozen or so wills with various trusts, you can go into your will DATA base and find all records where pecuniary marital deduction formula was “y” and Generation Skipping Trusts was “y” and “fairly representative valuation” was “y,” etc., etc. If you need to make further modifications, make them. Link that estate plan with the personal DATA for your new client, and you can produce an estate plan with all of the characteristics that your find queries asked for.
Once the principle just described is understood, you can generate BRACKETED FORMS that even your partners can use. How? Take all of your variable personal DATA, and insert something like “[NAMEOFCLIENT]” into the FIELD where you would ordinarily have your secretary type the client’s name. This only has to be done once in your life. Next, go to, say, your Will DATABASE, and run some queries based on common fact patterns. Next export the RECORD and run your MAIL MERGE FORM. Instead of having a tailor made document for a specific client (which you would have if you used a RECORD based on your real client’s personal DATA), you will have a BRACKETED FORM that even an attorney can use, using old fashion GLOBAL SEARCH AND REPLACE.
The key is to keep your client’s personal DATA —names, addresses, etc.— in its own DATABASE, and your estate planning variables —fairly representative valuation, pecuniary GSTT Exempt Trust, distribution standards, etc.— in another DATABASE. This second DATABASE can store hundreds of permutations. If you have ever created one type of plan, you can use it again, simply by querying the DATABASE for that pattern.
Appendix C is part of a simple Will BRACKETED FORM, one which few of us would ever use. Appendix D is part of a BRACKETED FORM where the probate estate is designed to pour over into a trust created during life.
I generated these BRACKETED FORMS rather quickly by using the records of two clients for whom I had previously prepared, respectively, simple and complex Wills. I changed the file number FIELDS to a couple of made-up file numbers for a BRACKETED FORM records. That way I got rid of all of the information in the client’s contact DATABASE (I sincerely hope), substituting my bracketed DATA instead; but I kept all of the yes/no DATA that went into the formation of the final document. The results are Appendix C and Appendix D.
If I were going create a simple or a pourover Will for a client of mine, all of the bracketed information would be replaced by actual personal DATA peculiar to the client, taken from the DATABASE; but for someone who is not that committed, at least I can generate a FORM of sorts, that can be completed by hand, using GLOBAL SEARCH AND REPLACE for everything in brackets.
To find what needs to be replaced, search for
“[”. The first thing you will find is [MFULLNAME]. Replace [MFULLNAME]
everywhere in the document with the client’s real name. Then search for the
next occurrence of a “[”, which I believe will cause you to find [SPOUSEFIELD]. Replace it with “husband” or “wife,” as
the case may be. Of course, if I were preparing the document, the DATABASE
would already have done all this for me. But as a second best solution to the
problem of getting anyone besides me to use my FORMS, it is not bad. Actually,
the idea is brilliant.
I don’t want to intimidate anyone
with the actual MAIL MERGE FORM that I used to generate the two search and
replace BRACKETED FORMS in Appendices C
& C above, because it is more complicated than it need have been. But
remember, I have been doing this since 1984, and I take liberties. If you want
to see what was going on behind the scene, take a look at Appendix E. There you
will find the MAIL MERGE FORM that generated the two BRACKETED Will FORMS described above.
Appendix F is a BRACKETED FORM individually designed IRA beneficiary designation, leaving the IRA to the surviving spouse, with a special alternate disposition to a Credit Shelter trust to the extent of any disclaimer. The MAIL MERGE FORM that created the BRACKETED FORM is Appendix G.
Appendix H is a BRACKETED FORM
Appendix J is a BRACKETED FORM
Don’t be intimidated by my programming. I take liberties. You don’t have to. Actually, if you inspect the MAIL MERGE FORMS in the Appendices carefully, you will be able to figure out most of what is going on just by looking.
I have posted dozens of FORMS and DATABASES on the Web at http://www.trustsandestates.net/FormBook.htm for you to look at and perhaps try out. Unless you are very good at this sort of thing, the DATABASES may simply intimidate you, since they are complicated and were not designed for anyone but me. I advise you to just create your own DATABASE and FORM system, rather than try to figure out mine. However, I am going to post some of my stuff anyway, because there will be a few of you who might find them useful. Again, I have sort of gotten carried away with my programming. I caution others not to, and remind you that my approach is somewhat idiosyncratic. It works for me, but if I were to approach the subject again, I would do it differently. On the other hand, I am certain that the idea —the concept— is sound, and I commend it to you.
If you want to try to get some use out of my MAIL MERGE FORMS, you are welcome to them, but I would think that to the uninitiated, a great deal of explaining would be required. That is why I came up with the idea of creating BRACKETED FORMS out of my MAIL MERGE FORMS. (I am even considering using them to create formbook, but that is in the future.) No IF-THEN statements or other complicated programming are in the BRACKETED FORMS, just straight forward fill-in-the-blanks with variable information stored on a DATABASE. The BRACKETED FORMS, with bracketed information representing the FIELDS, are not difficult to use or to understand.
It occurs to me that one could easily take any BRACKETED FORM
created by my original (somewhat complicated) MAIL MERGE FORM, and simplify it
considerably as follows. Take a BRACKETED FORM created by the MAIL MERGE FORM,
such as any of those in the Appendices, and use the BRACKETED FORM as the basis
for creating another, easier to understand, true MAIL MERGE FORM, simply by
creating a DATABASE that has a FIELD for every bracketed FIELD found in the BRACKETED
FORM, and then creating a new MAIL MERGE document by inserting the newly
created FIELDS in place of the bracketed information. Voilá!
The possibilities are endless.
Word is a powerful program, but its power comes at a price. Not only is it very hard to master, but what is worse, it is filled with bugs and functions that do not perform as they are supposed to. The numbering in headers and footers has to be monitored constantly, because, for reasons that are inexplicable at best, Word will not properly count the number of pages. My favorite is the Page 8 of 7 type of footer. This is as much a problem in XP as in previous versions, despite Microsoft’s promises to the contrary.
MAIL MERGE is a lot easier to use in XP than in previous versions, but it is still not perfect.
My advice is to keep your DATABASES and your FORMS as simple as possible. (I do not follow my own advice in this regard, so I know of what I speak.)
I have been using MAIL MERGE with a DATABASE since 1984 or
1985. (Not all of the changes since that time count as progress in my
estimation.) There is much more that I could tell you about both Word Merge and
FileMaker, and would be delighted to do so; but for now, I am mainly interested
in imparting the basics of the concept. Because the concept is so useful for
attorneys in general, and estate planners in particular, I would like to see it
catch on among the ACTEC Fellows, so that we could share information (and FORMS
and DATABASES), perhaps through the website.