Is it possible make a field unique but conditional?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have a field that is marked unique, but I have some records that don't have this field and need to behave a little differently. Is there any way to allow some entries in this field to be the same? For instance either left blank or filled with N/A.

This field is a serial number field, and we want it to be able to let us know that we already have a part with that serial number. However, there are quite a few items in our inventory that truly do not have a serial number. So it would be nice to be able to put "N/A" in the field. 
Is this possible?
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
No problem.
You can have duplicate entries as long as the duplicate is for the value blank.

So make a formula field called [Serial must be Unique] and mark it unique.

The formula will be like
IF(
Trim([Serial]="","",
Trim([Serial]="N/A",""
Trim([Serial] = "n/a","", [Serial])