Concatenating data from a field on related records

[StevenJay]

Hello!

I've recently posted this problem a couple of times on comp.databases.filemaker, but haven't figured out a solution yet. I have a mailing list database. Two related files: "household" and "dancers". The mailing address is in the household file, individuals are in the "dancers" file. Each household is related to one or more individuals. Now, I want to create mailing labels and send only one letter to each household. However, I wish to list the names of all of the individuals in the household on the mailing label as well, like this:

Bev Jones and John Smith

123 Maple St.

Toronto ON etc.

or

Bev Jones, John Smith and Sharon Smith

123 Maple St.

Toronto ON etc.

It seems like the way to do this is to insert a merge field on the label based on a calculated field which concatenates the names. I found a post in FM Forums which addresses this problem almost exactly: "Concatenation from related records" dated 10/25/01. (Is there a better way to reference postings in these forums?) However, there is one step of the solution which I can't figure out--it says to create a value list based on the related records in the other file. I can't seem to find that option and have begun to wonder if it's a feature introduced in a later version of FMP. I'm using 4.1v3. If so, is there any way to accomplish the task in 4.1? It seems like it would be quite a common thing to want to do and you'd think it would be straightforward. Any assistance would be appreciated. Thank you.

Regards,

Steven Nagy

[Vaughan]

That's easy: related value lists was introduced in FMP 5.0. now is the time to upgrade!

[BobWeaver]

Yes it is a frequently required thing, and that's why Filemaker added the related values option for value lists in version 5. So, this is an ideal excuse to upgrade. If you want to do this in version 4, you will have to cobble together some script that goes to the related dancers records from the household file copies all related records and then cleans it up into a single text field. Better to upgrade.

[StevenJay]

Thanks for your suggestions. I upgraded. (Although only to 5.0v3 for now). Then I defined a value list in my "household" file which contains whatever is in the calculated "full_name" field of related records. Next I created a calculated field in "household" called "CommaSeparatedRelatedNames":

Substitute( ValueListItems( Status( CurrentFileName), "Names" ) , "