Enforcing Uniqueness on a Formula Field

  • 0
  • 2
  • Question
  • Updated 7 months ago
  • Answered

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.

Photo of Krishna

Krishna

  • 10 Points

Posted 5 years ago

  • 0
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
I think it's the lookup field from the Project record which is the problem. But I don't know a solution.
Photo of Bethany

Bethany

  • 10 Points
i have this same issue - anyone have any recommendations?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
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)
Photo of Francesco Spiga

Francesco Spiga

  • 264 Points 250 badge 2x thumb
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?

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
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.
Photo of Francesco Spiga

Francesco Spiga

  • 264 Points 250 badge 2x thumb
thanks for your quick reply. I'll have to find another way to avoid duplications then. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
You can make a summary report and sort it by the # of records at the top..  That will at least identify the culprits.
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Regardless of he formula, a formula may not use a lookup field if it is intended to be flagged as Unique.

This conversation is no longer open for comments or replies.