Skip to main content
Question

Enforce unique values in field and also allow blanks / empty / null values.

  • December 12, 2018
  • 0 replies
  • 128 views
I have a table of individuals.  

A subset of these individuals exists in this table, lets call them VIPs.

These VIPs are tracked with an (internal) ID that is unique within the Individual table; however not all individuals are VIPs.

Is there a way within the individual table to enforce a unique value on the VIP ID field even though a majority of standard individuals will not have a VIP ID (but rather they will have a null, empty, or blank value)?

Hopefully this is simple and I'm just too tired to see my solution.

Thanks in advance.
    This topic has been closed for replies.

    The only value able to be placed in a unique field more than once is a null value.

    You will need to make a second field that is a formula field and use an If statement to make this work. Your actual unique field will be the formula field. Something like as follows.

    Field one-[VIP's]

    Field two-Formula Field-If([VIP's]=null,null,[VIP's])

    At this point your VIP's will copy over to your formula field if it is not null and you cant have duplicates but a null value in the formula field will still be allowed.
    • anomDiebolt
    • Registered
    • December 12, 2018
    >The only value able to be placed in a unique field more than once is a null value.

    This true but can't you accomplish the same thing by just making the [VIP's] field unique and not filling it in when the record does not represent a VIP individual? Why is a second formula field needed?
    Yes you could, I have always created a formula field to accomplish this as I tend to use this field in other areas of my applications and as a formula field it can not be changed unless you edit the original field. It is useful at times for example in one of my applications I have a case ID that HAS to be unique and I don't want anyone to be able to change this from a grid edit view so I display my formula field in my reports and the only one who could change the original Case ID is a Role with Admin or Manager permissions that can open the original record to edit it. I was assuming the original post was looking to do the same...
    • SuryaExpert
    • Registered
    • December 12, 2018
    I fully agree that a unique field can be unique as long as it has a value. Null values should not interfere. May be one has to raise it in the uservoice to quickbase.
    Terms & ConditionsAccessibility statement