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 d...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendNo 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
------------------------------
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
------------------------------
- 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
------------------------------
