Forum Discussion

CBH_Service_Acc's avatar
CBH_Service_Acc
Qrew Member
22 days ago
Solved

Duplicate Record Checker

Hello,

I have a form with two fields named "Year" and "Quarter". The Quarter field is a dropdown with options such as "Q1", "Q2", etc. The Year field is a numeric field where users enter a year (i.e., 2025).

What I need help with is displaying a error message when users have duplicate Quarters for a single given year. Example: If a user has a record with "Q1" for the Quarter and "2025" for the Year, they cannot enter another record for 2025 with Q1. They may only select "Q2", "Q3" or so on for 2025.

Hopefully I explained this well enoguh for someone to understand. If I can elaborate more, please let me know.

 

Forms
  • Mez's avatar
    Mez
    17 days ago

    If you've marked this formula field as unique (must be unique is checked), you won't need any form rules, just attempt to save a new record with a known duplicate value and the standard built-in error message will appear. 

5 Replies

Sort By
  • Mez's avatar
    Mez
    Qrew Assistant Captain
    22 days ago

    If your users will always use the form to enter data and save records, a simple approach is to have a formula field to join {using list()} these two fields and make this field unique, resulting in something: "Q1-2025". You'll also want to have the fields Quarter and Year required so this formula field will always have a value. 

    The standard/built-in error message will display when the user attempts to save the record. 

    If this ends up being too restrictive on this table, you can always add another field to the formula to capture uniqueness, except Record ID. 

    • CBH_Service_Acc's avatar
      CBH_Service_Acc
      Qrew Member
      19 days ago

      Hey Mez,

      Thanks for that. I created a formula field called "Quarter-Year Duplicate Check" where I combine the text for the two fields using the formula "[Reporting Quarter] & "-" & [Reporting Year]". The text it returns is like "Q1 - 2025", but I'm having trouble figuring out the next step from here.

      On my form, I can't think of how to add a rule that will on record-save, check for duplicates records. Any other suggestions?

      • Mez's avatar
        Mez
        Qrew Assistant Captain
        17 days ago

        If you've marked this formula field as unique (must be unique is checked), you won't need any form rules, just attempt to save a new record with a known duplicate value and the standard built-in error message will appear.