Skip to main content
Question

Enforcing Uniqueness on a Formula Field

  • July 30, 2014
  • 0 replies
  • 359 views

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.

    This topic has been closed for replies.

    I think it's the lookup field from the Project record which is the problem. But I don't know a solution.
    • ArchiveUser
    • Registered
    • August 29, 2014
    i have this same issue - anyone have any recommendations?
    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)
    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?

    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.
    thanks for your quick reply. I'll have to find another way to avoid duplications then. 
    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.
    Regardless of he formula, a formula may not use a lookup field if it is intended to be flagged as Unique.
    • AswinBabu
    • Registered
    • January 13, 2021
    Avoid using any look up fields, instead use the reference. Reference usually has "Related" as prefix. Also avoid using any formula fields.

    ------------------------------
    Aswin Babu
    ------------------------------
    Terms & ConditionsAccessibility statement