concatenate text from multiple records

[Charles Henebry]

One thing I keep finding that I'd like to be able to do in FM is to concatenate a text field accross multiple records. This is analogous to the summary fields that allow you numerically sum or average a number field across multiple records.

Let's say I have a database with two tables: People and Households. I'd like to name each household with a calculation:

Household Name = "The " & People::Last Name & " house"

But that will only work if all the people in the house share the same last name. If Joan Smith and John Regan live together, I'd like for their house to get the name "The Smith Regan house"

On the other hand, if John Phillips and Sandra Phillips live together, their house should be listed as "The Phillips house" and not "The Phillips Phillips house". So I guess what I'm looking for here is for it to concatenate without repeating duplicates, just as FM already does when it creates dynamic value lists.

On several other occasions, I've not been worried about duplication, but just wanted to create a "summary field" or some other sort of calculation that concatenates all the text in a field across several records. But, regardless, I've not had any luck figuring out how to do this fairly simple task.

I'm now using FM 10 (despite what it may say in my sig — having trouble changing the forum settings) on a Mac OS 10.5.

[Vaughan]

Look into the List() function which was introduced in FMP 8.5.

... if John Phillips and Sandra Phillips live together, their house should be listed as "The Phillips house" and not "The Phillips Phillips house".

This is easy enough to do, it's a simple text parsing issue.

Good to see you're off FMP 7.

[Charles Henebry]

That's just what I was looking for. Thanks!

Now if I can just figure out how to change my FM profile here on the FM Forums! For some reason, even though I change the setttings every time I post, it insists on presenting me as a user of FM 7 on Mac OSX Panther.

Edited April 5, 2009 by Guest

[Lee Smith]

For some reason, even though I change the setttings every time I post, it insists on presenting me as a user of FM 7 on Mac OSX Panther.

The information in each post is for the Thread, and is used for those questions when you are not using your setup in your profile.

Now if I can just figure out how to change my FM profile here on the FM Forums!

To change your profile,

go to the Menu Bar at the top.

Choose [color:blue]MY PROFILE >> [color:blue]Control Panel >> [color:red]FileMaker Questions

Lee

[Charles Henebry]

Thanks for the help! Fixed my forum settings.

I've been working hard on getting FM to edit the List of names in such a way as to eliminate duplicates—a "simple text parsing issue," as vaughn put it.

It's proven tougher than I hoped, though I'm making progress. At the moment I'm trying to use a recursive script, where the last name in the list is checked against each of the previous names. If there's no match, I proceed to the next name in the list. This is a fair amount of coding though. If anyone has a better solution, I'd love a hint!

[LaRetta]

It eliminate the duplicate issue on the names, use ValueListItems() instead. For instance, a self-join from Addresses to Addresses on address (call the self-join Occupants) and a value list in Addresses called Occupants based upon Occupants name, include only related values from Addresses.

Then Addresses would have calculation (unstored text) with:

"The " & Substitute ( ValueListItems ( Get ( FileName ) ; "Occupants" ) ; ¶ ; " " ) & " house"

In this way, if Phillips and Phillips lives there, the result will be The Phillips house otherwise it will list two three or more last names.

Edited April 5, 2009 by Guest

[Charles Henebry]

That's great. But it still runs into redundancy if Joan Smith and John Regan live in the house with their son Percy Smith-Regan (or even Percy Smith Regan). In that case, it'd be nice if the output were "The Smith-Regan place" or "The Smith Regan place" and not "The Smith Regan Smith-Regan place".

[comment]

I think you are right on the edge of what can be expected from a mechanized approach, without human supervision. Strictly speaking, "Regan-Smith" is a unique last name, different from both "Regan" and "Smith". You indicated this by entering it as such, instead of "Regan¶Smith" (indicating two last names).

Perhaps you could take this one step further by doing the above, i.e. breaking out individual words into separate values, and basing the value list on that. But then Ms. Smith-Regan might not be happy about receiving letters addressed to the "Regan Smith" household (value lists are alphabetized), and so on.

[Vaughan]

Regarding the removal of duplicate values form a list, look into some of the custom functions at

www.briandunning.com

You'll need FMP Advanced to add CFs to a file.

[Charles Henebry]

I'm actually using the household name only as an internal label. So I'm not that concerned about the final product, so long as its easily readable.

I wound up creating a 22 line script with nested loops. My first time scripting in FM with variables. What a wonderful thing, variables!

I'm willing to post my solution, if anyone is interested. Thanks, everyone, for your help and advice!

[Vaughan]

Ah. Scripts and nested loops. Didn't you need a calculated field?

Check out the RemoveDuplicates() custom function at http://www.briandunning.com/cf/492

"The " & 

Substitute( RemoveDuplicates( List( People::Last Name ) ) ; "¶" ; "-" ) 

 & " house"

[Søren Dyhr]

Wouldn't the CF's use not only make sense if the data is case sensitive?

FilterValues(ValuelistItems("all";Get(CurrentFilename);List( People::Last Name ))

--sd

[Charles Henebry]

That custom function looks great, but unfortunately I'm on FM Pro, not Pro Adv.

A calculated field would be better, but the complexity of the calculations was daunting. A recursive script seemed simpler.

[comment]

use ValueListItems() instead.