3 Replies Latest reply on Oct 29, 2010 11:31 AM by philmodjunk

    Duplicate records and nested tables



      Duplicate records and nested tables


      Hi, I am a self-taught FileMaker Pro 10 user trying to set up a membership database for an art museum. There are two main problems I've run into and before I turn back to the forums, I wanted to see what you could recommend. I work in IT and would greatly appreciate the most effective solutions regardless of how technically advanced they may be.

      1.) I have a minimum of 6 fields that I would like to be able to update multiple times per year and keep the previous data paired with the date it was entered (if not otherwise specified).

      2.) I have ~10,000 records that have ~50 fields (most important are: first name, last name, home address, email email address) non of which are mandatory. I am having a problem locating and managing duplicate records primarily (but not exclusively) upon receiving updated (but incomplete) lists of ~3000 records from associates.

      Thank you for your time. I eagerly await your responses.
      -Scott Nikiel

        • 1. Re: Duplicate records and nested tables

          1) I'd use a related table for this with your 6 fields plus a date field. Everytime you need to change the data, create a new record with the new date. You can use relationships so that this data is then displayed on the correct set of records in your main table or a looked up value setting can copy the values over from the most recent record in this table.

          2) That's a major headache you've got there unless there is an ID number associated with the data you are importing that reliably and uniquely identifies each imported record. If you have to compare names and addresses to identify duplicates, this gets pretty darn complicated. Given that: names are not unique; names and addresses are easily mistyped; people change their names; People move to new addresses; And people change their phone numbers--setting up a system to match values reliably can be quite difficult.

          Best suggestion I can think of is to import this data into a separate table with a mulitple field relationship that matches key data fields such as names and addresses. You might need to use multiple table occurrences of your main table with relationships to your import table that match the data in several different ways. You can then script a process that treats records that don't match as new and imports them. Recods that match an existing record can then be flagged for a person to compare the data and decide whether the imported record is a duplicate and what to do with it (discard it, use it to update existing data or...)

          • 2. Re: Duplicate records and nested tables

            Thanks, I was considering the second table, but wanted to see if you had any better ideas.

            As far as the duplicates go, I get the gist of what you're saying, but I'm not sure I understand how to implement it.

            • 3. Re: Duplicate records and nested tables

              You could define a relationship like this:

              MainTable::FirstName = ImportTable::FirstName AND
              MainTable::LastName = ImportTable::LastName AND
              MainTable::Address = ImportTable::Address

              Now, if you create a layout based on ImportTable and place a field such as FirstName from MainTable on it. You can enter find mode, put * in this field and your find will list all imported records where the first name, last name and address fields contain exactly the same data.

              Where this gets messy, is that you may have variations in your data that keep identical records from matching. Your MainTable record migh have "Phil" as the first name and your ImportTable might have "Phillip" or "Philip" as the first name--which will keep your records from matching even though they are actually for the same person.