Discussions

Expand all | Collapse all

Enforcing Uniqueness on a Formula Field

  • 1.  Enforcing Uniqueness on a Formula Field

    Posted 07-30-2014 18:02

    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.



  • 2.  RE: Enforcing Uniqueness on a Formula Field

    Posted 07-30-2014 18:32
    I think it's the lookup field from the Project record which is the problem. But I don't know a solution.


  • 3.  RE: Enforcing Uniqueness on a Formula Field

    Posted 08-28-2014 20:10
    i have this same issue - anyone have any recommendations?


  • 4.  RE: Enforcing Uniqueness on a Formula Field

    Posted 08-28-2014 20:14
    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)


  • 5.  RE: Enforcing Uniqueness on a Formula Field

    Posted 08-07-2018 18:30
    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?



  • 6.  RE: Enforcing Uniqueness on a Formula Field

    Posted 08-07-2018 18:34
    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.


  • 7.  RE: Enforcing Uniqueness on a Formula Field

    Posted 08-07-2018 19:09
    thanks for your quick reply. I'll have to find another way to avoid duplications then. 


  • 8.  RE: Enforcing Uniqueness on a Formula Field

    Posted 08-07-2018 19:14
    You can make a summary report and sort it by the # of records at the top..  That will at least identify the culprits.


  • 9.  RE: Enforcing Uniqueness on a Formula Field

    Posted 08-10-2018 00:27
    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.


  • 10.  RE: Enforcing Uniqueness on a Formula Field

    Posted 08-10-2018 00:48
    Regardless of he formula, a formula may not use a lookup field if it is intended to be flagged as Unique.