I have a portal showing "Description" and "Cost" from a related file.
Description is a text field, and Cost is a number field. Using a
calculation field I can add up the total cost of related items (using
the Sum function), but I would like to also created a calculated text
field which would contain something like "Item1, Item2, Item3" (just
concatenating all related Description fields with a comma). I need
this for a merge field in a contract. I can't seem to figure out how
to do this. I know I can use a script to do it, but that could
introduce problems if the fields are updated but the script doesn't
get run.

Any ideas would be greatly appreciated.

Thanks,
Anthony

On 03/14/2007 16:32:11 acuccia (AT) gmail (DOT) com wrote:


Script concatenation of the text fields into a global field. Something like (pseudo):

Go To Related Record [ Whatever Table, Whatever Layout, Show only Related Records ]
Sort Records as necessary
Go To Record [ First ]
Loop
Set Field [ gText ; gText & ", " & Item ]
Go To Record [ Next, exit after last ]
End Loop
Go To Layout [ Original Layout or wherever ]

Depending on version, you could concatenate into a script variable, then put that into a destination field at the end.

Matt

On Mar 14, 1:47 pm, Matt Wills <I... (AT) witz (DOT) end> wrote:
Thanks, but I'm trying to do this without a script so that the data
doesn't get out of sync. I don't want to depend on someone running the
script after they've updated some information. I'd like it to work
like the number calculation (which doesn't require a script, it just
gets updated whenever the values change).

-Anthony

You don't say what version of FileMaker you are using. If you are on FM
8.5, then take a look at the List() function. If you are on FM7 or
higher, take a look at Brian Dunning's website
<http://www.briandunning.com/filemaker-custom-functions/> for a custom
function that will fit your needs. If you are on FM6 or below, then you
can use the ValueListItems() function as long as each record's text is
under a certain number of characters (64 characters IIRC?)

acuccia (AT) gmail (DOT) com wrote:

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

In article <12vgsbd4rerr009 (AT) corp (DOT) supernews.com>, Howard Schlossberg
<howard (AT) antispahm (DOT) fmprosolutions.com> wrote:
Deja Vue?!? I don't know if it's the same person, but we did answer
this exact question only a week or so ago.


You can define the Value List that uses the Relationship link to obtain
it's values from the other file's field (the field you want to
concatenate together).

Then create a Calculation field that retrieves those values using the
ValueListItems function (in the Design Functions section).
eg.
* * * RelatedItems * * *Calculation, Text Result, Unstored
* * * * * = ValueListItems("Database Name", "Value List Name")

This will give you all the related values separated by carriage return
characters. You can easily swap the carriage returns for a comma (and
space) by wrapping the above in a Substitute function.
ie.
* * * RelatedItems * * *Calculation, Text Result, Unstored
* * * * * = Substitute(
ValueListItems("Database Name", "Value List Name"),
"**",
", ")

where the ** is really the carriage return symbol (the backwards "P"
button in the define calculation window).


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Sorry, should have mentioned my version. I'm using 8.5, so the List
function seems to be just what I need. Thanks to everyone for the
help.
-Anthony

On Mar 14, 3:02 pm, Howard Schlossberg
<how... (AT) antispahm (DOT) fmprosolutions.com> wrote: