Skip to main content
Question

Filtering Out Duplicates

  • June 29, 2021
  • 0 replies
  • 275 views
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
------------------------------
This topic has been closed for replies.

  • BlakeHarrison
  • Quickbase Alumni
  • June 29, 2021
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/
------------------------------
    MarkShnierYou
    Forum|alt.badge.img+22
    • MarkShnierYou
    • Quickbase Alumni
    • June 29, 2021
    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
    ------------------------------
    Mark Shnier. Your Quickbase Coach
      Terms & ConditionsAccessibility statement