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

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
  • (Edited)
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.
Photo of Rob White IV

Rob White IV

  • 1,050 Points 1k badge 2x thumb
  • perplexed

Posted 3 months ago

  • 0
  • 1
Photo of Donald Lundgren

Donald Lundgren

  • 90 Points 75 badge 2x thumb
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.
Photo of Rob White IV

Rob White IV

  • 1,050 Points 1k badge 2x thumb
Thanks, Donald.  Your solution was on point. 

I hope I can help you out one day.

~Rob
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,924 Points 20k badge 2x thumb
>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?
Photo of Donald Lundgren

Donald Lundgren

  • 90 Points 75 badge 2x thumb
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...
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.