How do a set a formula text field to require a unique value?

  • 1
  • 2
  • Question
  • Updated 1 year ago
  • Answered
Photo of JeffJoyce


  • 10 Points

Posted 6 years ago

  • 1
  • 2
Photo of Mark_Shnier


  • 640 Points 500 badge 2x thumb
Click the checkbox called Unique in the field's Properties :)
Photo of Bethany


  • 10 Points
I tried this and it said "This formula is incompatible with this field being marked unique" - what are the compatibly requirements?
I don't know what all the limitations are. I do know that you cannot use the [record id#], a lookup field, .the formula user() field, the now() function or the today(0 function.
Photo of Drew


  • 492 Points 250 badge 2x thumb
Does anyone have a list of what the limitations are? Somewhat frustrating that it keeps saying that a formula is incompatible, but not what is making it so.
See my answer above. I would also add User().

It makes sense really. You can't use a lookup field in a Unique formula since a change in the lookup field could cause many records to go Non Unique.

Note, however, that the typical field [Related Parent] is quite ok to use.
Photo of Todd


  • 110 Points 100 badge 2x thumb
I'm having a similar issue in my app. I have a formula text field called File Name that I marked "must be unique" which prevents Users from uploading duplicate file attachments into the Documents table. However after selecting this Unique value checkbox, I'm still able to upload documents with the same name. Thoughts? 
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb

Just checking on this since I am looking for the best way to do this exact thing (prevent users from uploading an attachment with a name that already exists.)  Any ideas?  I too tried to make a formula text field unique, but that doesn't seem to work.
you could try this

make a formula which calcuates the name of the file.

ToText([my file attachment field]) and then make a form rule to copy that to a text field all the time.

ie when Formula of file attachment field <> text value of file attachment field change the text to the formula and uncheck the checkbox so the rule always fires.

Then mark the text field unique.
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
This sounds like a great way to get around a formula field not being able to be set to be unique, but I'm having some issues with sequencing.  I've got the formula field and I've got the dynamic form rule, but no matter what I seem to use for the trigger the form rule doesn't fire.  It seems like the formula field is executing after the dynamic rule and nothing gets copied over.  If I were to edit that record it comes in correctly since it is already there.

Any way to fire the action after the form rule executes?
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
Actually - we may be able to get rid of the inbetween step of the formula field altogether. 

Even though it is a little nonsensical I modified the form rule to copy the attachment directly to the text field.  This did produce a value that did appropriately throw the non-unique error when I attempted to attach the same file.  The question is whether the format that was populated in the text field is going to stay consistent.  So, I have a new question!

Will the a copy of an attachment to a text field always come in as "C:\fakepath\YOUR-FILE-NAME"?
I did a test and now I see that the formula text field does not calculate until the record is actually saved.  So now I'm not sure what to suggest.
Photo of Tate Forgey

Tate Forgey

  • 702 Points 500 badge 2x thumb
Just tested with a different user, different file location, same file and did get the error, as you would expect, with the "fakepath" text.  So, as long as QB doesn't change the way that processes I'm going to call this solved!