Can a formula text field be used as the table's key?

  • 3
  • 1
  • Question
  • Updated 1 month ago
  • Answered

I have a formula text field that I want to use as my table's key, however the option is not available to me. Can formula fields not be used as the Key? Note: values in that formula field are and will always be unique.

Thanks in advance.

Juan

Photo of Juan

Juan

  • 74 Points

Posted 5 years ago

  • 3
  • 1
No, the Key field must not be a formula.

An alternative is to have your form show the calculated formula and then have a Form Rule that says "when the record is saved, change the value of [my key field] to the value in he field [my key field formula]".

But then you will also probably want a safety net to subscribe to where the Key fields does not equal the formal field.
Photo of Juan

Juan

  • 74 Points
Thanks Mark.  The issue is that ALL the records in this table will be imported rather than directly imputed via a form.
If all the records are being imported, them import them with the Key field calculated.

If the source data is not flexible, then import to an Excel Template to add the calculated the Key field and then import to your app.
Photo of Minda M

Minda M

  • 568 Points 500 badge 2x thumb
I have a similar situation. In my case, I'm not using a form or importing the records. The records are created by the Copy Master Detail button... but I want a formula field to populate the key field when the records are created. Any ideas?
Photo of Brian Cafferelli

Brian Cafferelli, Quick Base Technical Marketing Manager

  • 1,698 Points 1k badge 2x thumb
Hi Minda, you could use the method Mark suggested above, but using an automation rather than a form rule. So the automation will listen for new records to be created and move the field value from your formula - text field to a non-formula text field. That way it will work with your Copy Master and Detail Records button.

The reason why you generally cannot set a formula as a key field, by the way, is that every record needs to have a unique value for the key field, but the value of a formula field can change at any time.

There is an API to set the key field which can simply be hand typed into the URL address bar. It does allow for a formula field to be the Key field.

But you would be in somewhat uncharted territory as I’ve never seen any documentation that this is OK to do. I was just told about it an an EMPOWER breakout session.

You would have to make a copy of your app to test that.
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 6,396 Points 5k badge 2x thumb
I have done this on many occasions and use the API in the URL bar to set this.

?a=API_SetKeyField&fid=X&apptoken=catjbq7277cyecb3vh95zdqgi5

Photo of Russell Lane

Russell Lane, Employee

  • 80 Points 75 badge 2x thumb
Hey folks - thanks so much for your question, and for the comments and answers!  We noticed the discrepancy between the UI and the API around this issue, and had some concern about possible data integrity issues resulting from that.  Data integrity is a critical issue to us, so we've made a change that will make the API behave the same as the UI - it will no longer be possible to set formula fields as keys via the API.  That should appear in an upcoming release.
Russel,

Thx for that clarification.  I used to use that technique, but then ran into "bugs"where I had records with duplicate key fields.  Given that this was an undocumented feature, I reverted back to a traditional Key field.
Photo of Drew

Drew

  • 522 Points 500 badge 2x thumb
will the change in the API behavior in any way affect existing tables where the key was established in this manner?
Photo of Harrison Hersch (QB)

Harrison Hersch (QB), Quick Base Sr. Product Manager (Platform)

  • 1,530 Points 1k badge 2x thumb
Hi Drew - it will not. But having a formula key field has the potential to cause inaccurate data.