1 Reply Latest reply on Oct 11, 2011 10:40 AM by philmodjunk

    Merging/Combining Issue

    TimSchweitzer

      Title

      Merging/Combining Issue

      Post

      I'm working to solve an issue with my first database.  It's essentially a collection of information about where people have been seen.  Like this:

      The fields are FirstName, LastName, and SiteSeen. so the info looks like this.

      Michael  Scott   New Jersey

      John      Doe     London

      John      Doe     Australia

       

      Now, basically what I want to do is reduce records that have the same name into one record per person and combining the location field, so that the resulting record would look like this:

      Michael  Scott   New Jersey

      John      Doe    London, Australia

       

      Or something of that sort.  Is there a way to do this without manually going through and checking for matches with each name?  Thanks for any help you can provide.

        • 1. Re: Merging/Combining Issue
          philmodjunk

          Matching by Name--either manually or via systems built into FileMaker will be problematic as different people can have the same name. You might want to assign ID numbers if at all possible.

          Sounds like you need two tables. One table where you have one record for each person and another where you have one record for each "sighting" and then you link them in a relationship.

          People::PeopleID = Sightings::PeopleID

          Then you can define this calculation field in People to get your list of locations separated by commas:

          Subsitute ( List (Sightings::Location ) ; ¶ ; ", " )

          Then a list view layout based on People can be used to produce the report you describe.

          If you insist on matching by names, you can use name fields in place of the ID fields, but note that you were warned about the potential complications involved.