Summarize data by concatenating value of specified field across all records?

[mweiss]

The subject heading may be obscure... I have a table with a text field -- let's call it Table::t_Words. I want to define a field that will take all values of Table::t_Words for the current found set and concatenate them (in their current sort order). Sort of a summary field, but instead of adding or averaging numbers, it concatenates text. Can such a thing be defined? Some clever calculation, perhaps, involving value lists? Does anybody have any ideas?

[Tori Mitchell]

Well... you can just do a script I suppose. I can think of the beginnings of a CF to do this... but really, in this case, you'll really want a looping script, I think.

[T-Square]

If you can create a relationship, you could create a value list on the related table and use the ValueListItems function, which "Returns a list of the values in valuelist, separated by carriage returns."

David

[mweiss]

Currently I use a looping script, but I'd like something that updates automatically, without having to be executed by the user.

I thought of the self-join --> value list --> replace line-breaks with spaces calculation, and it worked, sort of, but it automatically sorts the words in alphabetical order, rather than preserving the current sort order.

I guess I'll stick with my script for now. ???

[bruceR]

Wouldn't it just be substitute( list( relation::textField); "¶"; " ")

Edited September 27, 2007 by Guest

[comment]

Not if you want to get the found set (and not in version 8). You need a custom function for this, something like:

ListFoundSet ( fieldName ; startRecord ; endRecord )

Let ( [

item = GetNthRecord ( fieldName ; startRecord )

] ;

Case ( not IsEmpty ( item ) ; item & ¶ )

&

Case ( endRecord > startRecord ; ListFoundSet ( fieldName ; startRecord + 1 ; endRecord ) )

)