Forum Discussion

AndyHendricks's avatar
AndyHendricks
Qrew Member
4 years ago

Filtering Out Duplicates

Hello, 

My company utilizes a Quickbase App that was custom developed in-house for our CRM. We are in the process of cleaning up the bulk of our data in our CRM, and noticed that we have a lot of duplicate Company Records (over 25,000). We've identified this issue by exporting our Company Table into an excel spreadsheet. However, we would prefer to correct this error within Quickbase if possible.

What is the most efficient way to filter duplicate records in a table or to create a custom report returning duplicate values? 

Thank you!

------------------------------
Andy Hendricks
------------------------------
reports and charts
Tables and fields
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    4 years ago
    Unfortunately, there's not currently anything you can do in Quickbase with respect to duplicates. There are new formula functions on the horizon, but we don't have a firm release date on these, so that won't help your current situation. I would suggest pulling the records out of Quickbase into Excel, sorting by the Company Name (assuming this is the field that identifies the record as a Duplicate, and then setting a formula that will look at that field, compare it to the record above and if it's the same, return a 1. I typically label this field "Duplicate" and will create a matching checkbox field in Quickbase. Then, upload the record ID# and the "Duplicate" field back to Quickbase and you'll be able to report and/or delete records based on that field.

    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quickbase Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    No problem

    1. Make a concatenated field in Companies which describes uniqueness called [Unique Key].  For example name-phone-email ..
    2. Make a new table called Temp Unique with a field called [Unique Key]. Make that he Key field.
    3. Make a summary report on the Unique field in Step 1 and use the More menu to copy these to another table and populate the Temp table.
    4. Make a relatinship where 1 Temp Unique has many Companies.
    5. Make a summary field of the Minimum Record ID# and called it [Record ID# of the Company to keep].  Look this up down to Companies
    6. Make a Report in Companies where [Record ID#] <> [Record ID# of the Company to keep].  call this report "Purge Candidates".  You will probably want to do some sanity checking and also to have a report with the opposite filter called "Compnaies to Keep".

    When you are sanity checked, run the report of "Purge Candidates", and then use the More menu or vertical dots menu to delete the records in one "are you sure" click.  



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • MichaelTamoush's avatar
      MichaelTamoush
      Qrew Captain
      4 years ago
      I used Marks method when I needed to do this and it worked great.

      If you decide to go the Excel route, you can also go to the Data Tab and there is a remove duplicates button. It asks which columns to look at, and you an select there the parameters.

      After you remove duplicates, you can create an extra column called Keep This Record, and set them all to 'True'.

      When you reimport back into QB, have QB create a new checkbox column for keep this record. Filter by that checkbox and delete the non checked.

      The excel solution is a one time solution, whereas Marks solution you can always go back and see if somehow you have new duplicates, so I think his is worth the effort.

      ------------------------------
      Mike Tamoush
      ------------------------------