Discussions

Expand all | Collapse all

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

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

    Posted 08-11-2014 08:47

    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



  • 2.  RE: Can a formula text field be used as the table's key?

    Posted 08-11-2014 12:35
    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.


  • 3.  RE: Can a formula text field be used as the table's key?

    Posted 08-13-2014 06:25
    Thanks Mark.  The issue is that ALL the records in this table will be imported rather than directly imputed via a form.


  • 4.  RE: Can a formula text field be used as the table's key?

    Posted 08-13-2014 11:46
    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.


  • 5.  RE: Can a formula text field be used as the table's key?

     
    Posted 06-01-2019 16:42
    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?


  • 6.  RE: Can a formula text field be used as the table's key?

    Posted 06-20-2019 19:23
    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.



  • 7.  RE: Can a formula text field be used as the table's key?

    Posted 06-01-2019 17:05
    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.


  • 8.  RE: Can a formula text field be used as the table's key?

    Posted 06-18-2019 18:00
    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



  • 9.  RE: Can a formula text field be used as the table's key?

    Posted 25 days ago
    Do I need to create a new filed (Formula Url) to do this?

    ------------------------------
    Ankit Pruthi
    ------------------------------



  • 10.  RE: Can a formula text field be used as the table's key?

    Posted 25 days 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
    ------------------------------



  • 11.  RE: Can a formula text field be used as the table's key?

    Posted 25 days ago
    Ankit, you can use a formula to calculate the key-value; but you must then push the formula value into a standard text field that you set as your Key for your table.  If your table already exists; then you must build the new [Key] text field; populate it with unique values before you can make it the Key. 

    If your key value is a combination of other field selections separated by a delimeter of some kind; such as [xyz]-[abc]-[def], then you need a formula on your Add-form to "calculate" the Key value.  Either when all required values are populated or when the record is saved, push the calculated formula value into the [Key] text field to save.

    ------------------------------
    Laura Thacker (IDS)
    laura@intelligentdbs.com
    (626) 771 0454
    ------------------------------



  • 12.  RE: Can a formula text field be used as the table's key?

    Posted 25 days ago
    ... Ankit,
    If it was not clear, Laura means to use form rules and set the form rule to fire all the time.  De-select the checkbox at the bottom of the form rule screen so that at all times of the formula does not match the Key field to change the Key field to the value in the formula field.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 13.  RE: Can a formula text field be used as the table's key?

    Posted 25 days ago
    Hi Laura,

    Thanks for the suggestion. Quick Question? When you mean push the calculated value into the key field, you mean automation right?

    I am uploading data, which run's in thousands, Automation is limited to 1000 rows max, so in such a scenario pushing via automations will not be possible right?

    Appreciate your help!

    Regards

    ------------------------------
    Ankit Pruthi
    ------------------------------



  • 14.  RE: Can a formula text field be used as the table's key?

    Posted 24 days ago
    No, we were assuming this was manual data entry in Forms and you would using firm rules.  If you are uploading then Pre calculate the key field before uploading.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 15.  RE: Can a formula text field be used as the table's key?

    Posted 07-16-2019 19:36
    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.


  • 16.  RE: Can a formula text field be used as the table's key?

    Posted 07-16-2019 19:45
    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.


  • 17.  RE: Can a formula text field be used as the table's key?

    Posted 07-17-2019 04:39
    will the change in the API behavior in any way affect existing tables where the key was established in this manner?


  • 18.  RE: Can a formula text field be used as the table's key?

    Posted 07-17-2019 18:09
    Hi Drew - it will not. But having a formula key field has the potential to cause inaccurate data.