Forum Discussion

AshleyBanning's avatar
Qrew Member
3 years ago

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, 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!  



      6 Replies

      • AlexWilkinson's avatar
        Qrew Assistant Captain
        Lovely! Thanks so much. By the way, for those who have crafted their own solutions for finding duplicated people, here is an idea I have found very helpful. For "Last Name" create a formula-text-field consisting of the first __ consonants. (I use 3 consonants). Do the same for "First Name". Finding matches on these formula-fields can help to identify cases like "Wilkinson" (my actual last name) vs "Wilkerson" (how some people misspell it). If I understand how formula queries will work, extraction of the 3 consonants could be done dynamically, rather than in a static, stored formula-field. Nice!
      • DonLarson's avatar
        Qrew Commander
        How do you marry that information together with a Record Picker to prevent creating a duplicate in the first place?
      • @Jonathan Heuer Formula Queries do run within your application so they will contribute overall to your applications traffic. It is still accessing and running against that data so as your data set gets bigger the performance impact does as well. In a lot of cases that is going to be less demand or less intensive then building a bunch of relationships, formula and summary/lookup fields to get similar results but in some cases it will add to your apps complexity and performance so it will be something to consider as you add them to your apps. That is also something we are keeping a very close eye on during our Beta process.​
      • Alex that's a great idea, and sounds like a promising substitute for whatever Google is doing when I try to search for "pusta recipes" and Google asks "did you mean pasta recipes". But what text function are you using to extract consonants from words?
      • Is there a sort order for the results? example: 3,2 instead of 2,3

        UPDATE: this returns the highest [Record ID#] instead of the lowest. 

        ToNumber(Right(ToText(GetFieldValues(GetRecords("{10.EX." & [Related WhatsIt] & " }"), 3)),";"))