Concatenate text fields from multiple related records

[Norvin]

I want to concatenate name fields from related records into a single field. I have a table named “client” and a related table name “relations”. In the client table I want to concatenate names from related records in the relations table. For example I have 3 related records from the relation table: first record is Bill Smith, second record is Jim Jones, and third record is Steve Jackson. I want a field in the client table to read: Bill Smith; Jim Jones; Steve Jackson. I can get the first record in the relationship to display but how do I get the formula to look at the second, then the third etc?

[Søren Dyhr]

Well you mean that ValueListItems( sorts wrongly from a dynamic valuelist - If you were on 8 could GetNthRecord( be utilized, but what you need is to sort on the records creationtime or the portals sortorder.

I would build the valuelist on the integervalue behind creation time, because you then can get the valuelist strained from numbers that perhaps is usable via this CF:

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

...where you fill in "0123456789" in the second half, and then finally Substitute( space->return to just return.

--sd

[Breezer]

I think this will resolve what you are trying to achieve. I have attached a sample file "ClientRelations"

You will need the following fields:

i) A unique client ID field, usually the primary key of your client table, which I will call CLIENT_ID

ii) The Client name field (which you already have)

iii) A global counter field in the relations file, will call it gCOUNTER

iv) A counter to count the related clients (count of the CLIENT_ID), will call it KEYCOUNTER. Note that this is an unstored calculation field.

v) The field that will display the concatenated names, will call it COMBINE

Set up the script that will copy the related records and paste them in the "relation combine field"

Initialize the global counter "gCOUNTER" to "1", reset your combine field or use a predefined text,

Go to the first row of the related record,

Copy this record,

Paste it on the COMBINE field,

if the global counter is less than the KEY COUNTER, then Loop through the portal rows

I have attached the file to demo this process.

ClientRelations.zip

Edited October 3, 2005 by Guest

[Norvin]

Søren Dyhr & Breezer thank you for your responses. I apologize for not stating my problem more clearly. I need the information hard coded into the formula of the field in the client table so that the first record in the relation table is read then the second, third and so forth. There are never more than 4 related records in the relation table so the formula would only have to accommodate 4 records. I would like to accomplish this without a script if possible. Again, thanks for your help.

[-Queue-]

Create a value list of the related full name field, sorted by the related serial field (if your relationship/portal is not sorted). Then use an unstored calculation of

Substitute( ValueListItems( Get(FileName); "yourValueListName" ); ¶; "; " )

If the relationship or portal is sorted, then you may need to sort the value list by a different field (or a concatenated field, if the sort is based on multiple fields).

[Søren Dyhr]

Thanks Queue for rephrasing my advice, I did actually mean to put it inside a calc'field.

But if your wish to hardwire means getting it into an indexed field, won't ValuelistItems( work either, since it only behaves in unstored fields. This hint's in direction of making searches but a related table away, which before FM8 was a tricky task to perform.

http://www.kevinfrank.com/download/multi-gtrr.zip

The advice Queue and I gave would only work if the value is set buy script, but then again might creation order not be the order wished for, eventhough that could be scripted as well!!!

--sd

Edited October 4, 2005 by Guest

[Norvin]

Wow! Works great. What makes this really nice is there are a couple of more applications of this technique in the solution that will really be sweet.... Thanks guys.