Merging multiple records into 1 layout

[cpfeiler]

Hi

I am fairly new to FM. I have a multi-tabled project that I am working on. One table contains "families". A second table contains children. These 2 tables are linked by a family code. In the family table is the street address. All the children are seperate records in the children table. I am trying to create a mailing label that would say:

To the parents of xxx,yyy,xxx

where there might be 1 child in the family or 4 children.

Thanks in advance

cathy

[CobaltSky]

Hello Cathy,

I suggest that you set up a value list called ChildrenInFamily and define it to use the contents of the name field in the children file via the relationship from the Families table.

You will then be able to calculate the texct for your mailling label with the formula:

"To the parents of " & Substitute(ValueListItems(Get(FileName); "ChildrenInFamily"); "

[Fenton]

I did a little example file. I called my "parent" table Parents. The ChildrenVL_c field is what Ray suggested. But, more interesting (to me) is how to deal with addresses.

Human families are actually fairly complex. There are several variations:

1. Both parents live in the same house

2. Parents live at separate addresses

3. Parents have different last names

4. Children have different last names from one of the parent's last names

There are even more, but those are ones you'd like run across, even in a small town. I put together a test, so that you can mail either 1 to 1 house, or 2 to 2 houses. It also gets the childrens' names correct. It does not, however, deal with different last names for the parents; not yet :-]

Families.zip

[Fenton]

This one is the same, but it also deals with different last names. People are fussy about their names :-)

It could possibly be optimized. It is likely to be slow, to brutally slow on large record sets. I cannot see how it could be a lookup, because it depends on 2 tables, Parents and Children (where editing doesn't trigger it).

Unless someone else can figure that out, it would be best to either trap all editing within a controlled layout interface, so that a script runs to set the results into plain text and number (the Address test) fields. Or use a plug-in to run the script upon editing any of the referenced fields (FirstName, LastName, Address, Child LastName, Child ParentID 1 or 2).

Families.zip