Quick Base Discussions

Expand all | Collapse all

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

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

     
    Posted 12-12-2018 03:31
    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.


  • 2.  RE: Enforce unique values in field and also allow blanks / empty / null values.

    Posted 12-12-2018 10:06
    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.


  • 3.  RE: Enforce unique values in field and also allow blanks / empty / null values.

     
    Posted 12-15-2018 02:02
    Thanks, Donald.  Your solution was on point. 

    I hope I can help you out one day.

    ~Rob


  • 4.  RE: Enforce unique values in field and also allow blanks / empty / null values.

    Top
    Contributor
    Posted 12-12-2018 11:24
    >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?


  • 5.  RE: Enforce unique values in field and also allow blanks / empty / null values.

    Posted 12-12-2018 14:07
    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...


  • 6.  RE: Enforce unique values in field and also allow blanks / empty / null values.

    Bronze
    Contributor
    Posted 12-12-2018 15:57
    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.