Stumper - field that will combine text values

[Steverino]

Is it possible to create a calculation or summary field that will combine the text values in a particular field in a found set? In other words, if record 1 has a field "letter grade" with a value of "A" and record 2 has a value of "B," can I create a field that will return the value "A, B,...etc.")? I already know how to do it with a script, but I was just wondering if there were other alternatives.

[-Queue-]

I've tried this using an If[ Status(CurrentRecordNumber), 1, 0] calc related to a constant (1) and a valuelist based upon related fields, but it only appears to evaluate if the record is a member of the current found set. Bummer. You might be able to do this after marking all current found records and relating them. But that would be too much overhead and using a script anyway.

Anybody else have any brilliant ideas? Ugo?

[kennedy]

If you can formulate your find as a self relationship, then you can define a value list on the field using that relationship, and then the function ValueListItems on that value list will get just the list you are wanting.

HTH.

[-Queue-]

A self relationship based on what fields? I already did exactly what you appear to be saying, but it won't work because there isn't a 'member of current found set' calculation to relate.

[kennedy]

That's what I meant when I said "If you can formulate your find as a self-relationship,". Thus, this doesn't work for any old found set a user might come up with. But, as is often the case, if you're trying to implement a particular design pattern, it might work great.

For example, if when you need this you'll be doing a find of all records where the field F6 is a particular value. Define a global that your user can fill in that value, define a relationship between that global and the field F6, and then define a value list using that relationship on the field you want concatenated, and then use ValueListItems to get the list.

This doesn't work at all with arbitrary found sets... but I thought I'd post it in case it might be helpful.

[Ugo DI LUCA]

Relationship would be the only way to tag each record according to their "order"....

So you might implement Queue or Brian suggestions here, saving the Found set in a global field.

This would require some kinds of Interface design and scripted finds where all records founds are "exported" to a global.

I like the "Copy all records" script step, having a layout with only the Record_Id on it to copy.

[-Queue-]

kennedy said:

This doesn't work at all with arbitrary found sets...

Then it's like I said, it doesn't seem doable without scripts.

[kschott]

I had a similar question answered awhile ago.

What I had was two related tables. For each record in the main table there could be zero to many related records in the second table.

I was trying to combine one field from the related table (Document Title in my case, Grades in your case) into a single field in the master file in what sounds like the same format you are (name1, name2, name3, etc).

I'll paste the reply I got that worked for me and maybe you can adapt it to work for you.

Good luck:

-------------------------------------------

Fenton

member

Reged: 02/06/03

Posts: 52

Re: Concatenating variable number of fields [Re: kschott]

#74431 - 07/11/03 03:03 PM Reply Quote Quick Reply

It sounds like you want "values from a field(s) in a variable number of related records."

This is not the same as "a variable number of fields." Semantics?, perhaps; but very different.

You can use the ValueListItems to get the values from a related field.

Create a Value List, values from a field, only related values, DR/DAR relationship, DocumentTitle field.

Call it DR_Docs.

Create a calculation field, Unstored.

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

[jon2004]

thats an excellent solution, however can i stop FM sorting the value list in alpha order? can i have it returned in the order the child docs were created, sorted by an id or creation time etc??

[-Queue-]

You can specify a value list to include a secondary field and be sorted by that field. When you create a calculated field with ValueListItems( ), the secondary field will not be included, but will still 'invisibly' sort the list as specified. I discovered this trick when sorting a value list based on a repeating field via a serial self-relationship by a field that provided the index for each repetition. I would reference the file, but a hacker destroyed the thread, and I don't have the demo on this computer.