Finding Duplicates in Quickbase Just Got a Whole Lot Easier

Quickbase is truly an amazing platform, and I am excited to say it just keeps getting better. I currently work as a Solutions Consultant with Quickbase and have been here for three years. During this time, I cannot tell you how many requests I’ve received where customers or prospects inquired about methods to find duplicate values. This request frequently comes up when tracking contacts in a CRM, but I have also heard use cases around transaction/expense tracking or even consolidating overlapping information into Quickbase from multiple systems.  

There was just not a great solution. In some cases we could force some duplicates to be visible, but the setup was a bit inelegant. Simplicity is always something we strive for at Quickbase, and so that is why I am super excited to announce (in case you haven’t already heard), that finding duplicates in Quickbase just got a whole lot easier! 

Let’s talk about the solution…*drum roll*… Formula Queries! If you missed the Empower Session on Formula Queries, you can find it here. Formula Queries are pretty darn awesome. If you are an intermediate to advanced builder in Quickbase, you have probably written your fair share of formulas. Traditional formulas (outside of lookups and summaries) look at only the record they are on. In other words, traditional formulas cannot look at other records in the same or other tables, only the single record on which they live. Formula Queries, on the other hand, can look at the record they live on, plus any other record in the same table, and even any other record in the same application. There are numerous uses for Formula Queries, many of which we have not yet discovered. A few uses are things like resource allocation, rolling summaries, holiday planning for project durations, and of course, duplicate tracking.  

So, let’s dive into duplicate tracking. Some of the items you probably should think of before trying to write the query are things like: 

1. What makes another record a duplicate? In the case of a contact, is it the first and last name that indicates a match, or in a transaction is it the dollar amount and date? This is something to really begin thinking about to ensure you are telling the system how to properly identify the duplicates.
2. How do I want to be notified of a duplicate? Is it a report on a dashboard that displays them? Or should you receive an email if a record was saved and it’s a potential duplicate?
3. Should duplicates exist at all? Should users be able to enter duplicates to begin with? 
4. Who should manage duplicates if found? Should it be a single person or team? Should there be some approval process?

Let’s take a look at a Formula Query in action: 

Reading from the inside out: 

First, GetRecords: inside of this function is the Quickbase Query Language (see more about queries here) that compares the field id 7 of the current record (which is the Last Name) with the [Last Name] field of other records in the same table. The field id 8 is similar, but instead compares the email addresses. This specifically says get any record where the Last Name and Email of another record are the same as the record we are currently on. So, if I have two or more records where the Last Name is Stewart and the Email is JimmyStewart@example.com, then all records with that matching criteria will return as a result to that function.  

Then, GetFieldValues: Once GetRecords returns any matches it found that have the same Last Name and Email as the current record, then GetFieldValues grabs the contents of the returned records. In this case, the values that are grabbed are the contents of field 3 (the record ID of the match) 

The result is magic:  

Ta-da! You can see the record IDs of any duplicates (in our case where the Last Name and Email match), show up right here in this field.  

This may spark some additional thoughts such as “who should be responsible to see the duplicates”, or “what should we do if we identify duplicates”; There is no ‘right’ answer. You may wish to have an individual or team dedicated to managing this process, and perhaps they are the only ones with access to view these fields. Once the duplicate is identified, maybe there should be some sort of approval process to delete the finding, or some way to hide that record from end users. These are only some ideas on how to manage the duplicates, but from organization to organization or team to team, these processes may differ. And of course, the beauty of Quickbase is that you can customize this workflow to best fit your needs. 

Great, you have now seen how to track duplicates in Quickbase with Formula Query magic. Shoutout to our product team who keep pushing the limits of Quickbase!