Forum Discussion

KrishnaKrishna's avatar
KrishnaKrishna
Qrew Trainee
10 years ago

Enforcing Uniqueness on a Formula Field

I have created a formula field on my Project Status record to concatenate Project record id (totext version of it) and a date field on the project status record (child of project). I would like to make this unique so the system would prevent the user from creating multiple statuses on the same date for a project. When I try to enforce uniqueness on this field I get the following error message:

Here is the formula:

ToText([Project - Record ID#]) &

Mid(ToText([Status Week Of]),7,4) & Mid(ToText([Status Week Of]),1,2)

& Mid(ToText([Status Week Of]),4,2)

Here is the error message when I try to enforce uniqueness:

?This formula is incompatible with this field being marked unique. Please change the formula or uncheck the unique checkbox.?

How can we meet the requirement underlined above? Let me know if you need further clarity.

Formulas and functions
NotificationsEmail
Tables and fields

13 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    10 years ago
    I think it's the lookup field from the Project record which is the problem. But I don't know a solution.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    10 years ago
    Try changing the formula to

    ToText([related project]) &

    Mid(ToText([Status Week Of]),7,4) & Mid(ToText([Status Week Of]),1,2)

    & Mid(ToText([Status Week Of]),4,2)
  • FrancescoSpiga1's avatar
    FrancescoSpiga1
    Qrew Cadet
    6 years ago
    I have encountered the same issue. Any solution to this nowadays?
    Is there a way to concatenate lookup fields and use them to uniquely identify a record and avoid duplicate entries?

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    No, because conceptually, the system cannot prevent the source of the lookup from being changed and hence causing a violation of uniqueness.  So I would say that is not a Quick Base deficiency, but rather a logic deficiency or asking for something that conceptually does not make sense to expect.
  • FrancescoSpiga1's avatar
    FrancescoSpiga1
    Qrew Cadet
    6 years ago
    thanks for your quick reply. I'll have to find another way to avoid duplications then. 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    You can make a summary report and sort it by the # of records at the top..  That will at least identify the culprits.
  • AlexCertificati's avatar
    AlexCertificati
    Qrew Cadet
    6 years ago
    Apologies if this won't work, as I am just starting my journey from Excel to Quick Base. Conceptually, another possible solution might be to nest your lookup concatenation formula within an IF statement that checks the output against existing records and returns a failure message of some sort to your field if it finds a matching record.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    Regardless of he formula, a formula may not use a lookup field if it is intended to be flagged as Unique.
  • AswinBabu's avatar
    AswinBabu
    Qrew Member
    4 years ago
    Avoid using any look up fields, instead use the reference. Reference usually has "Related" as prefix. Also avoid using any formula fields.

    ------------------------------
    Aswin Babu
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Qrew Champion
      4 years ago
      Formula fields are fine as long as they do not refer to look up fields or summary fields or the native date created or date modified fields.

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------
      • AswinBabu's avatar
        AswinBabu
        Qrew Member
        4 years ago
        That was something that I missed. Thanks, Mark!!

        ------------------------------
        Aswin Babu
        ------------------------------