how would I pool together the information in the field "city" for a record with several cities associated with it
How are the cities "associated" with the record?
Sorry, that wasn't very clear. I simply mean that one customer/client might have multiple cities in their record because they have purchased products from suppliers located in different places. Right now, I have a list (CSV format) of all the records in my database that have multiple cities, but I don't have a way of pulling all of the cities listed for each customer/client together into one place.
I have something like this:
Client no. Location
001 New York
001 San Francisco
And I want it to look like this:
Client no. Location(s)
001 New York; San Francisco; London
I hope that is clearer!
It is somewhat clearer, except the CSV part. Assuming you have two tables, Clients and Locations, related by ClientID, a calculation field in the Clients table =
Substitute ( List ( Locations::City ) ; ¶ ; "; " )
should produce the desired result.
The list works, but it's still not exactly what I need. I'm going to sift through the forum (I posted before I had done a comprehensive search, and I'm new to the FM forum). I'll post again if I'm unable to find a previously posted solution to my query.
Thanks again for your help!