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

They recently blocked doing the API to change a table's Key field to be a formula field as it was unreliable and led to weird buggy behavior.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

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.

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.

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?

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

17 Replies

QuickBaseCoachD
Qrew Captain
10 years ago
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.
JuanSolorio1
Qrew Cadet
10 years ago
Thanks Mark. The issue is that ALL the records in this table will be imported rather than directly imputed via a form.
QuickBaseCoachD
Qrew Captain
10 years ago
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.
MindaMay
Qrew Cadet
5 years ago
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?
- BrianCafferelli
  Qrew Captain
  5 years ago
  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.
QuickBaseCoachD
Qrew Captain
5 years ago
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.
LauraThacker
Qrew Captain
5 years ago
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
- AnkitPruthi1
  Qrew Member
  5 years ago
  Do I need to create a new filed (Formula Url) to do this?
  
  ------------------------------
  Ankit Pruthi
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    5 years ago
    They recently blocked doing the API to change a table's Key field to be a formula field as it was unreliable and led to weird buggy behavior.
    
    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
RussellLane
Qrew Member
5 years ago
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.
- QuickBaseCoachD
  Qrew Captain
  5 years ago
  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.
- DrewVoge
  Qrew Cadet
  5 years ago
  will the change in the API behavior in any way affect existing tables where the key was established in this manner?
- hhersch
  Qrew Captain
  5 years ago
  Hi Drew - it will not. But having a formula key field has the potential to cause inaccurate data.