In Filemaker 7 I've a database for a an annual race. 600 entrants.
I've a record for each runner - name, address, age, race, t-shirt
size, race year (I've kept it for 5 years). here's the problem: Every
year I have to compile a mailing list from the last two years. I've
many applicants with the same address (i.e. two parents and three
kids) how can I generate a mailing list with no duplicate addresses.
what I'd like is if Jane, Tom and Patrick Doe enter I could have a
separate record for each, perhaps in addition to the First and Last
name fields i could have a Household field (the same for each member
of the family?) that would be the common factor linking the family
members, or those with the same address. Like Doe Family.

Would I create two tables with the House field and the address, city,
state, zip and phone number and the other table with first and last
name and the race specs - age, race, tshirt size, etc. which fields
would be look up fields so I could generate a layout with the
household name and address etc on top and then in a portal at the
bottom each member of the family with their individual specs?

Thanks for your help.

Lynne

<calder (AT) mcn (DOT) org> wrote:

Find the records in question, sort by mailaddresses, export the
e-mail-adresses with the option group by mail address. The will be only
one record per mail address in the export file.

That's a clean way of doing such a thing. You'll need an address record
in the address table and a participant record in the second table for
every runner, if they have no familiy.

I see no need for lookups and scripts. Work from a layout in the address
table and use portals to edit runner specs.
--
http://clk.ch

<calder (AT) mcn (DOT) org> schreef in bericht
news:1178342173.426947.191410 (AT) n59g2000hsh (DOT) googlegroups.com...

Here's my ten cents worth. There might be someone with a more cheerfull
message for you, and I certainly hope there is an easier way and I just did
not think of it. I'm sorry to say you have found yourself in a great heap of
trouble. And you are going to do a major re-thinking of your database. You
might think you can just use duplicate addresses, but you might find this
very troublesome. There is no way to guarrantee you will find all
duplicates. Not unless you have been very rigorous in entering them. But
one -, one comma, one whatever out of place will render the 'find
duplicates' useless. And the problem with look-ups is that you still have no
way in telling if an address has been used previously. You will have to use
related tables. First I will cover the basics of transferring addresses,
assuming you have entered them all in the same manner. If you haven't been
very strict only my second option will do it.

If your records don't have a <uniqueID>, create it, set it to self generate.
create a calc, containing all address details
create a new table <Asdress>
create all fields you need for the address, including an autogenerating
<AddressID>
create a join table containing the <UniqueID> and <AddressID>
create the same calc as above here
create a relationship <AddressRel> between the calcs

<Layout of script>
In your main table find all records
if the <AddressRel> is not valid
store the <UniqueID> in a global
create a new record for <Address>
Set all the fields you need
end if
if you have created a new <Address> record
Store the <AddressID> in a global
go to the <JoinTable>
create a new record and set <UniqueID> and <AddressID> with tthe values from
your globals.
end if
If for a record in the <MainTable> the adress allready exists, but the name
doesn't just add a record to the <JoinTable>
go back to main table and loop through the records

In the end you'll have a list of all unique adresses inside the <Address>
table
Joined to all names throught the <JoinTable>
The <Address> table will now become the source for your printing.
Every address is unique, but might have multiple names to it.
Use the relationships to pull all connected names through the <JoinTable> to
the <Address> table and display them there.

Like I said, you have found your self in a great heap of trouble. The main
problem being that your current design is absolutely not meant to do what
you want.

There is one other way I would prefer, but you will certainly not. Rebuild
the solution (make use of a join table, like I showed you). Plan it better,
write down what it does, what it has to do. Talk to everyone involved with
the same task, they might have usefull insight. Start to read on objects
and attributes. In your case you have treated addresses as attributes of
people, while they are (in your case anyway) objects. And when you have done
with creating a new database, just print the old one (At least the last two
years) find a couple of friends who are willing to read out the prints while
you re-enter them. Since now the solution will fit your needs, entering and
using them will be easy enough.

And perhaps it is just too early in the morning to see the simple solution
:-))

Keep well, Ursus

Thank you for your help and encouragement. But I've some questions:

What do you mean by "export the e-mail-addresses with the option group
by mail address". Is that assuming that ALL the records have a unique
email address, (which they don't.) But I could create a serial number
in my Participants Database and export with the option group by mail
address? Am i pulling all the fields over? Then I'm importing those
unique addresses into a new table I've set up with just the address
information and a household name (The Calder Household) calling it the
Mail Address table?

i've tried to set up a layout with the Mail Address table info at the
top and in a portal display fields from the Participants Database
below. But I'm having a problem getting the portal to work
properly. The portal will display existing data linked to the
address shown above, but not allow me to enter any new information.

Ideally I'd like to have an entry layout where I can put in mail
information (going into the Mail address table) and then put in data
on all the runners in that household below and that data would go into
the Participants database in the portal. So... what am I doing wrong
so that I can't enter data?

What fields do I link to create the relation between the two tables
(relational tables do work the same as relational databases, don't
they?) Should one or both of the tables have a serial number for each
record? Or do i link the table with the Household Name field in one or
both of them?

I used to know how to do this, but it was with separate databases. I
can not get it to work in Filemaker 7 with tables. Can you help?

thanks so much for your time.



On May 5, 12:29 am, c... (AT) freesurf (DOT) ch (Christoph Kaufmann) wrote:

<calder (AT) mcn (DOT) org> wrote:


If these are the mail addresses of 7 participants:
mail1
mail2
mail2
mail2
mail3
mail4
mail4

The described export will get you a a file that looks like this:

mail1
mail2
mail3
mail4

If you use the text of this file for addressing your mail, you only
write once per address.


The export is for single use. No need to create a new table.

The relation does not allow it. You can change that in the definition of
the relation and say for each side of the relation whether records can
be created or deleted over the relation, i.e. using a portal.

The unique serial no of every household.

I see no need for participiants serial numbers. But you need a number
field for the household serial number in the participants table in order
to link the records.

It's possible if the names are unique and not too long. However, using
editable text fields as keys is asking for trouble. You will most
certainly be sorry if you do that.
--
http://clk.ch