concatenating related field values

[DRoberts]

I am working on a database where I have two related tables

The main table contains my records which may have a number of variant names held in the related table

e.g. Record1 with related values Variant_name3 / Variant_name1 / Variant_name2.

Not all main records have variant names. Some records will have only one variant, while others have multiple related names (as above).

I want to concatenate the related record strings into a single text string held with Record1 which would read “Variant_name1, Variant_name2, Variant_name3”

I’d like them in alphabetical order with commas behind each string (but not the last one).

I’m not sure where to start apart from thinking about a loop through related records but how do I sort them alphabetically and test to see if I have more than one record – any pointers would be much appreciated

Thanks

David

[comment]

Try a calculation field (result is Text) =

Substitute ( List ( Variants::Variant_name ) ; ¶ ; ", " )

Set the relationship to sort the related records by Variant_name.

[IdealData]

I was going to chime in here but comment beat me to it. An alternative to using LIst is to declare a VALUE LIST based on the relationship.

You can use the ValueListItems function (in Design Functions) to provide a return delimited list that is ALREADY SORTED because value list are always sorted.

[comment]

It probably doesn't matter here, but it's worth noting that using a value list not only sorts the records but also removes duplicate values.

[Mooner]

Have a very similar problem to the OP.

In my case the text field with concatenated values needs to have some tags as delimiters instead of commas (xml), is it possible?

(I can start a new thread in case, don't want to hijack!)

Edited June 24, 2009 by Guest

[comment]

Filemaker can export as XML - much better to use this option than to fiddle with generating XML code by calculations, IMHO.

[IdealData]

And it would also work in FM7 - comment, surely you're not still on FM7?

[bcooney]

Something I've always wondered, too...

[DRoberts]

Thanks Comment,

I have amnaged to get the Substitute method to work.

I can't understand what it's actually doing....could you explain what is happening .....I'm following blindly at present and want to learn how FM works!

Similarly I haven't managed to get the Value List to work - probably something I'm not doing

This forum is fantastic - thanks again (to all of you)

[comment]

I can't understand what it's actually doing

Perhaps you should look at the result of just:

List ( Variants::Variant_name )

Then imagine substituting carriage returns with commas.

[DRoberts]

Good - now the mist is clearing

many thanks

David