Removing duplicate records from FileMaker


This past week I completed a two-day class on FileMaker Pro to enhance my ability to support customers who rely on this popular database program. A lot of information was covered over the two-day period, and I mistakenly entered the wrong information into a few of the sample exercises.

In one instance, I noticed that the FileMaker database that I was working on had twice as many entries as my classmates. My database contained duplicate records because I had unknowingly grabbed data from the same Excel spreadsheet on two separate occasions.

During one of the class breaks, I asked the instructor if FileMaker included an easy way to remove duplicate records. He suggested a method that required creation of a self-join relationship. While I thanked him for his tip, I was looking for a simpler solution and found one on the FileMakerToday website that required the creation of a script:

Go to Record/Request/Page [First]
Set Variable [$$ID; Value:”Table Name”::(field)]
Go to Record/Request/Page [Next]
Loop
If [“Table Name”::(field) = $$ID]
Delete Record/Request [No dialog]
Else
Set Variable [$$ID; Value:”Table Name”::(field)]
Go to Record/Request/Page [Next; Exit after last]
End If
End Loop

The field in “Set Variable [$$ID; Value:”Table Name”::(field)]” is the field you use to find duplicate records. The first part of the script sets variable $$ID to the value of the field. The If statement checks the next record to see if the contents of the field are the same as the previous record and deletes it if it’s the same.

About these ads

24 Comments

Filed under Apple Software, Troubleshooting

24 responses to “Removing duplicate records from FileMaker

  1. Thank you for the code :)
    I’ve tried it and worked for me except for one thing…Here’s my problem:

    I’ve been working on a simple database that has different telephone numbers as records.
    ————————————————
    tel flag
    03-1234-0001
    03-1234-0004
    03-1234-0001
    03-1234-0005
    03-1234-0003
    03-1234-0005
    ……
    ————————————————
    What I needed to do is to delete all the duplicates and return only unique records.

    First, I’ve put a flag for the duplicated ones and sorted them so that it will be easy for me to see the duplicates. After sorting them, my table will look like this:
    ————————————————
    tel flag
    03-1234-0001 1
    03-1234-0001 1
    03-1234-0005 1
    03-1234-0005 1
    ————————————————
    After that, I made a script base on what you’ve written in the above code and return the table as:
    ————————————————
    tel flag
    03-1234-0001 1
    ————————————————
    What it did was to delete all the duplicates but it also deleted the last value in the row, which in my case is 03-1234-0005.

    My desired output should be
    ————————————————
    tel flag
    03-1234-0001 1
    03-1234-0005 1
    ————————————————

    Here’s my script:

    Go to Record/Request/Page [First]
    Set Variable [$$ID; Value:SampTbl::tel]
    Go to Record/Request/Page [Next]
    Loop
    If [SampTbl::tel = $$ID]
    Delete Record/Request [No dialog]
    Else
    Set Variable [$$ID; Value:SampTbl::tel]
    Go to Record/Request/Page [Next; Exit after last]
    End If
    End Loop

    What is wrong with my above code?
    I am a FileMaker newbie :) Any advice? Thank you so much.
    I am using FileMaker Pro Advanced 12.

  2. P.S.
    “tel” and “flag” are supposed to be separated but they appeared as one field in my post… sorry to that.
    should be
    tel | flag
    03-1234-0001 | 1
    03-1234-0005 | 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s