Finding duplicate values

  1. If you plan to delete the duplicate records that you find, make a backup copy of the file.
  2. Start a find request.

    See Making a find request.

    3. To find

    Use this operator

    Examples

    All duplicate values

    !

    ! in the Company field finds all records that contain the same company name in the Company field. (ABC Company and DEF Ltd. when duplicate values exist for these companies in multiple records)

    Duplicate values of a specific type

    !characters

    !ABC in the Company field finds all records containing ABC Company when duplicate values exist for this company in multiple records
  3. Click Perform Find in the status toolbar.

Notes 

  • One field by itself is often not sufficient to determine the uniqueness of a record. If several fields taken together determine uniqueness, you can create a calculation field (returning a text result) that concatenates the values of several fields, and then perform a find for duplicate records on that field. For example, you could use the formula First Name & Last Name & Phone Number to concatenate three fields. See Defining calculation fields.
  • Values are duplicates only if word order is the same.
  • When determining uniqueness in text fields, FileMaker Pro looks at the first 100 characters in each field.
  • ! does not find records in which duplicate values are entered in the same field (for example, ABC Company ABC Company in one field).
  • You can also identify duplicate records using a self-join relationship. See Finding duplicate values using a self-join relationship.

Related topics