Forum Discussion

RobIV's avatar
RobIV
Qrew Cadet
7 years ago

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

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;...
Tables and fields
DonaldLundgren3's avatar
DonaldLundgren3
Qrew Member
7 years ago
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.
  • RobIV's avatar
    RobIV
    Qrew Cadet
    7 years ago
    Thanks, Donald.  Your solution was on point. 

    I hope I can help you out one day.

    ~Rob