concatenating from many related records

[Nik]

Is there any way to concatenate all the values from a single field in multiple related records (one-to-many relationship)?

Specifically, a video conference booking is in multiple venues, but for display purposes, I'd like to show all the venues for a single video conference booking on one line. Is there any way of doing this in a calculation field?

I know that I can set up a portal for the booking and run a script to concatenate from each portal row, but I'd like to do it by a calculation field to avoid the possiblility of having outdated data displayed.

[LiveOak]

You can do it with a calculation, but with limitations. A script can concatenate an indeterminate number of records. A calculation can't loop and must be set up for a maximum number of records. You would need to create one relationship for each different record to be concatenated. Five records, five relationships. Five hundred records, you get the idea.

As an alternative you could define each display position on the line as a related field. With a clever definition on relationships, you can display the first booking/venue in the first position, the second in the second slot, etc. You don't really need to concatenate the data to display it in a row. The index would be a composite of venue/booking. When a booking was past, you would need to renumber the booking number.

-bd

[Nik]

Thanks for the reply. Unfortunately, these methods are all a bit nasty, and not really true to the data model. However, they would work, provided circumstances were just right.

What I would really like to see (does this exist in FileMaker 5?) would be an aggregate function which would work similarly to the other aggregate functions, but instead of averaging, or counting all the related fields, it would concatenate them all.

Never mind.

[LiveOak]

(music) Fairy tales, can come true, it can happen to you.... -bd

[BobWeaver]

I agree. An aggregate concatenate function would be incredibly useful.

[Nik]

Hey, I'm glad to see its not just me!