4 Replies Latest reply on Jul 25, 2015 4:08 PM by perk

    Create summary but eliminate redundant records with same company name



      Create summary but eliminate redundant records with same company name


      I imported a database that includes the list of every casino in the US along with the number of gaming machines they have. The database also included the name of each key manager at each casino, making the database rather large (20,000 records). There are only about 1300 unique casinos, of which I want to create list and sort by the number of gaming machines. The problem is the list will not eliminate the redundant records. In other words, I just need each casino to be listed once. Is there a way to eliminate the redundant casino names after the first one has been identified? I know I can do this in excel using a VLookup function but not sure how to do this in Filemaker.

      See example that I created in excel to show you what is happening and what I need as it relates to the found (and sorted) records.




        • 1. Re: Create summary but eliminate redundant records with same company name

          Well, looking at your example, how do you know which ones you want to eliminate?  In the Gold Casino example, how do you determine which of the 3 records you want to keep?

          As of right now, I guess you have one table, with all 20000 records that you want to pair down the just 1300?

          Edit:  Additional info

          IF...that's a big IF, All the casino names that are duplicates are spelled the same, meaning for example Jackpot Casino also is NOT spelled The Jackpot Casino AND  you don't care which duplicate record you keep, you can try the following:

          FIRST MAKE A BACK UP OF YOUR DB--this is a blunt instrument and may fail

          Clean up the data first to make sure there are no stray characters, space, returns

          Show all records.  Select the Casino Name field.  From the Records menu at the top, select Replace Field Contents.  Click the radio button "Replace with calculated result".  In the calculation box type in :    Trim(Self)  and click Ok to close.  Then click Replace.  This will trim all the casino names.

          Create a new field called duplicate

          Write the following script:

          Freeze Window

          Sort Records [Table::CasinoNameField]

          Go to Record/Request/Page [first]


               Set Variable [$dup; Table::CasinoNameField]

               Go to Record/Request/Page [next, exit after last]

               If [$dup = Table::CasinoNameField]

                    Set Field [Table::duplicate; "x"]

               End If

          End Loop

          Now all your duplicates are marked in the duplicate field with an 'x'.

          Next go back to the layout, show all records, sort by the duplicate field, then CasinoNameField, just to check that there is one record for each casino name unmarked.

          Then, manually, enter find mode, put an x in the duplicate field, perform find

          Now you have a found set of records of just the duplicate records, marked by the x

          Then........from the Record menu bar......Delete Found Records.  You'll be left with just the records not marked.

          Emphasizing again to do this on a copy of your DB.  Also, it wont make a distinction on which duplicate it deletes, as the records will be in creation order.  And, if this table is related to another, you may lose some of the data, if the related records per each duplicate are not the same.

          Edit: Edit  Additional info in red

          • 2. Re: Create summary but eliminate redundant records with same company name

            Thank you for your reply Steve. This certainly looks like an excellent solution so will attempt this. I was able to filter and apply some hygiene to the data I have already have (Names exactly the same, no spaces, etc..) so I should be good to go.

            I also was able to extract a list of non-duplicating casino names and # of gaming machines from the original database that included the "complete" list of casino names and contacts. As an alternative to your solution above, I am also looking into somehow importing the data and creating an "X" to mark the matching record as the primary. I can then create a very simple script to search and sort by the fields I am interested in. I just imported all that data into a separate Table within Filemaker, but that may be overkill since all I want to do is mark the record as the primary.

            • 3. Re: Create summary but eliminate redundant records with same company name

              Another method (If you have clean data) is to set up a "unique values, validate always" field option on the casino name field and then import all your data into that table (save a clone and import into it if necessary). During the import, this validation rule will automatically filter out the duplicates.