Hello. I've created a FileMaker Pro 7 database (Mac OS X) for a
non-profit org. One of the tables contains the donations made by
people along with date it was made.

I would like to create a Microsoft Word mailmerge document that says
basically "Dear so-and-so: you have donated $<TOTAL> this year,
putting you at the Silver level. For another generous donation of
$<DIFFERENCE>, you can reach our Gold level."

So I need to subtotal the donations for a person within a date range,
then compare it to a lookup table and determine the difference value to
the next level.

Any ideas how to do this with MS Word, or does it have to be done
inside Filemaker?

Thanks
David

I haven't played with mail merges, but to get the data that your
looking for is relatively easy. Assume you have the tables;
People, Donations, and Levels

Create the fields "Date Start_g" and "Date End_g", both global dates,
in the People table.
Create a relationship from People to Donations where
PEOPLE::ID = DONATIONS::ID Person AND
PEOPLE:ate Start_g <= DONATIONS:ate AND
PEOPLE:ate End_g => DONATIONS:ate

Now you can create the calculated field "$ Donated In Date Range" in
People using the above mentioned relationship to get the data, don't
forget to set the 2 global dates to the date range you're looking for.

Using this field you can create a relationship to Levels where
PEOPLE::$ Donated In Date Range < LEVELS::MinLevelAmt

Now you can create another calculated field in People "Next Level
Amount"; the calculation for this field would be
Min(LEVELS::MinLevelAmt)

Now you got all the data you need in the People record and your on your
own from here on in.

dmcheng1 (AT) yahoo (DOT) com wrote:

Do a find for a date range then display the found set in Preview Mode.
Use the "GetSummary ( summaryField ; breakField )" to get the donations
for the date range and then calculate the difference value for the next
level. You will have to create a layout for the Thank you Letter in
LIST MODE with a sub-summary field sorted by the Surname of the Donor -
so the breakfield will be the surname or a calculated field (Surname & "
"& First Name & " " & MI). Enter the "hard text" in layout mode in the
HEADER of the layout and use the merge command to insert the fields you
want with in the text of the document. The donations will display in a
list below the text in the header. In the layout setup have the letter
force a new page each time the surname changes.

AJLawrance
Developer
Gabriola Island, BC
http://www.members.shaw.ca/ajlawrance/

In article <1124638964.616964.67760 (AT) z14g2000cwz (DOT) googlegroups.com>,
dmcheng1 (AT) yahoo (DOT) com wrote:

It can probably be done in Word. However, it is so easy to do in
Filemaker, I would suggest that approach.

You can easily generate letters from inside FileMaker. You can have
individual letters, or form letters that are mail merge. In fact, it is
so much easier than messing around with Word, you will never use Word
again for any kind of mail-merge document.

To begin with, I assume you have a table for the Contacts (or Donors),
and a table for donations. I assume further that each person (entity) in
the COntacts table is identified by a primary key field, which
presumably is simply a unique serial number assigned automatically when
a new record is created, which I will call kpContactID. I assume the
Contacts table has text fields for NameTitle, NameFirst, NameLast,
NameSuffix, Aaddress, City, State and Zip, as well as any other fields
you want., such as phone number, email address, etc.

I assume further that each donation record is identified by a primary
key field (serial number) which we will call kpDonationID, and that the
Donation table includes a field for the foreign key kfContactID, and
fields for date received, amount, Date acknowledged, and remarks.

I assume that Donation records are related to Contact records by
ContactID, and that you can create Donation records from the Contacts
table, in a portal in a layout of the Contacts table.

To make a form letter to acknowledge each donation:

Make a new layout in the context of the Donations table. Use a Blank
layout. Compose the letter head, body text of the letter, etc in the
Layout mode. Insert merge fields for name & address, and a merge field
for donation amount, and maybe date received, in the body. The merge
fields for name and address would come from the related Contact record.
The merge field for Amount and Date Received would come from the
Donation record. Put the Date Acknowledged field in the usual place for
the Date of a letter. Then it is easy to put in the date, and print the
letter. Also make an Envelope layout in the the context of the Donations
table, and put return address in the layout, along with merge fields
from the Contacts table for name & address.

You can write a script that will find all the donations for which the
Date Acknowledged field is empty, then fills that field with today's
date, prints the letters and prints envelopes. Once you call the script,
either directly or by a pushbutton, it will generate the letters and
envelopes automatically. All you have to do is put letter paper in the
printer and then put envelopes in the printer, at the proper time. You
can put Message steps in the script that tell you when to put the paper
in and when to put the envelopes in. You can also include a step that
sorts the letters by, say, name, before you do the print.

To make a form letter to acknowledge summary donations for the year,
take a similar approach, but build the letter and envelope layout in the
context of the Contacts table. define a new field in the Donations
table, Year, calcualted from the Date received, as Year =
Year(DatRreceived). Define a field in the Contacts table named Donation
Year. Make a new Table Occurrence of the Donations table, and a new
relationship to the Contacts table, based on Year and ContactID.Also
make new fields in the Contacts table for Total Donations and Total
Donations Year. These should be calculation fields set to
Sum(Donation:Amount) and Sum(DonationYear:Amount) respctively. As the
merge field for the amount, you would insert the TotalDonationsYear
field. Write the script to insert the current year into the Year field
of the Contacts table, then perform a Find for all those that have a
non-zero donation amount in the year just completed, then sort them
appropriately, and then print the letters and envelopes.

To generate individual letters for any purpose, make a new table for
Letters. Include the ContactID as key field in the letter table, and
define other fields as Date (Date) and LetterBody (text) for the text of
the letter. Define a relationship between Letter and Contact based on
ContactID, and allow creation of letters from the Contacts table. Make a
layout in the Letters table that includes letterhead, merge fields for
the name & address, date field and LetterBody field. Set it up to make a
nice-looking letter, with appropriate font, etc. It is wise to make the
layout body and the LetterBody large, so that you can write a multi-page
letter, but format sliding /printing to slide up the LetterBody and
shrink the enclosing part, so that the letter will be only as many pages
as needed. Also make an Envelope layout in the Letters table, with
return address and merge fields for name and address.

Make a new layout in the Contacts table called Contact Letters. Make a
portal in that layout that shows related letters, including the Date and
Body fields. Then you can simply create an individual letter in that
portal, then go to the related Letter layout, finish it up and print it.

To help with navigation among related records in this setup, it is wise
to use pushbuttons to take you to the related layouts. They should be
formatted as not to print. You can also use pushbuttons to call thhe
scripts to generate the various kinds of letters.

This is fairly easy to set up, and once it set up, you will never do
mail merge form letters, individual letters, or envelopes any other way.

Note that you can include layouts for mailing labels as well as
envelopes, and you can write the labels into the scripts for mailing,
rather than envelopes, if you prefer.

If you do bulk mail, you can have FMP7 automatically generate a count of
mailpieces by zip code, which you can then use with the post office for
each bulk mailing. You need to include a summary field for Count by Zip
Code, and then set up a layout that includes the zip code field and the
count by zip code both in a subsummary part sorted by zip code, and the
count by sip code in a grand Summary part. You can write a script that
finds the contacts you want to send the bulk mailing to, sorts them by
zip code, prints the mailing labels and prints the count by zip code.

In order for this bulk mail approach to work properly you need several
fields defined to calculate the 3-digit zip code and the 5-digit zip
code, and summary fields that count by 3-digit zip and by 5-digit zip.
The post office goes mainly by 3-digit zip for pricing, unless you are
using the approved barcodes for the mailing. The main zip code field in
the Contacts table should be a text field. Assume this Zip code field is
named Zip. Then the 3-digit zip code is calculated by the formula
Left(Zip,3) and the 5-digit zip code is calculated by the formula
Left(Zip,5).

Bill Collins

--
For email, remove invalid.

Thanks all for the detailed responses! Let me read through and absorb
them. I appreciate your help.

David

FP - that method gave me exactly what I needed. Thanks for your help.

David

FP wrote: