concatenate fields of related records

[lsmall]

This may sound a bit odd, but can someone suggest a calculation to concatenate values of multiple fields in related records into one string in a single field.

For my purpose this would be 5 fields of up to 80 characters each in each record. There would be up to 150 related records. So at most the field would contain 60,000 characters, which I think FM9 allows.

Thanks

Edited July 23, 2008 by Guest

[Inky Phil]

Because there is more than one related record I do not think that there is a calculation that will cope but you could do this through a script.

Create a layout that contains a portal with the related data in it and then step through the portal rows adding the text to your field as you go along.

hth

Phil

[comment]

Actually, there are two ways (at least) to do this with calculations:

One is to define a calculation field in the child table that concatenates the five fields, then use List() to "collect" them into the parent table.

The other is to use a custom function, such as the one described here:

http://www.fmforums.com/forum/showtopic.php?tid/194942/post/290607/#290607

or this one:

http://www.briandunning.com/cf/309

[lsmall]

That has potential. Thanks for the suggestion.

Thanks

[Agnès]

Hello,

to concatenate values of multiple fields in related records into one string in a single field.

you can used CustomList ( Start ; End ; Function ) CF, here

and write :

CustomList ( 1 ; GetFoundCount or ValueCount or other ;

"GetNthRecord ( T::Field_A ; [n] ) & "" "" & GetNthRecord ( T::Field_B ; [n] ) & "" "" & GetNthRecord ( T::Field_C ; [n] ) & "" "" & GetNthRecord ( T::Field_D ; [n] ) & "" "" & GetNthRecord ( T::Field_etc ; [n] )" )

CustomList ( 1 ; GetFoundCount or ValueCount or other ;

"GetValue ( MyList_A ; [n] ) & "" "" & GetValue ( MyList_B ; [n] ) & "" "" & GetValue ( MyList_C ; [n] ) & "" "" & GetValue ( MyList_D ; [n] ) & "" "" & GetValue ( MyList_etc ; [n] )" )

but, carrefull, if you still have a field that contains so many characters, it can slow down your database

Agnès

[lsmall]

Well thanks for all the great suggestions. Unfortunately I've run into a snag. In the concatenated field the values need to be separated by ascii CR (hex 0D) and ascii LF (Hex 0A). The whole field has to end with ascii SUB (Hex 1A). The CR is not a problem and I was able to copy and paste the ascii SUB from an ascii/hex editor, but when I copy and paste LF from the ascii/hex editor, filemaker changes it to CR. I'm stumped!

I'll have to export and do "Find and replace"

[comment]

http://www.fmforums.com/forum/showpost.php?post/297146/