Discussions

 View Only
Expand all | Collapse all

Combo Primary Key

Mark Shnier (Your Quickbase Coach)

Mark Shnier (Your Quickbase Coach)09-10-2019 14:30

  • 1.  Combo Primary Key

    Posted 11-17-2017 23:17
    How do I set two fields in a table as a combo primary key, like in Access?  Thanks!


  • 2.  RE: Combo Primary Key

    Posted 11-17-2017 23:54
    In fact, a little known secret is that you can.  Make a formula field to say hyphenate them together and then use the API_SetKeyField.

    You can just type the api call into the URL bar. 

    Post back if you don't know how to use the API guide and need help.


  • 3.  RE: Combo Primary Key

    Posted 11-20-2017 16:57
    thank you!  I'm a brand new user to Quick Base, so I'm not familiar with how to make a hyphenate formula or use the API_SetKey Field.  Would you mind elaborating?


  • 4.  RE: Combo Primary Key

    Posted 09-10-2019 14:16
    Please can you tell me how to create a combo primary key

    ------------------------------
    priyanka gummuluri
    ------------------------------



  • 5.  RE: Combo Primary Key

    Posted 09-10-2019 14:19
    You would need to create a formula Text field...

    in the formula section have something like:


    [Field A]&"|"&[Field B]

    You might need to add ToText() around the fields if they aren't text already... you then set this new formula field as the primary key of the table.

    ------------------------------
    Ryan Stanford
    ------------------------------



  • 6.  RE: Combo Primary Key

    Posted 09-10-2019 14:28
    Its still not working . I cannot set it as key 





  • 7.  RE: Combo Primary Key

    Posted 09-10-2019 14:30

    Mark Shnier mark.shnier@gmail.com

    2:28 PM (0 minutes ago)
    to pgummuluri95
    Quick Base used to allow a text formula field to be designated as a Key field by using an API command to set the Key field.  But when I used this technique I ran into technical bugs and recently they have prevented the Key field from being a formula field.
    Hence we are now back to the old method, which is typically to have a field to calculate the key field by formula and then have a form rule enforce it and copying the value in the formula field to the Key field.
    Mark


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



  • 8.  RE: Combo Primary Key

    Posted 09-10-2019 14:32
    The form rule method could also work, however a limitation that you could encounter is if you data is being entered via grid edit or import as opposed to using a form.

    ------------------------------
    Michael Grosky
    Director of Customer Strategy, Sympo (Gold QSP)
    mgrosky@sympo.com
    ------------------------------



  • 9.  RE: Combo Primary Key

    Posted 09-10-2019 14:45
    Im completely new to quickbase and can you please guide me in creating a primary key with two fields where the two fields are foreign keys 






  • 10.  RE: Combo Primary Key

    Posted 09-10-2019 14:57
    Sure:
    1) Add a new field to a table setting the field type to Formula - Text
    2) in the formula box in the field settings, select [Field1]&[Field2]

    Note that each field should be surrounded by [] and the & is to concatenate the two fields. Your field types will also have to be text for Field1 and Field2. If they are not you can convert them in the formula totext([Field1]&[Field2])

    ------------------------------
    Michael Grosky
    Director of Customer Strategy, Sympo (Gold QSP)
    mgrosky@sympo.com
    ------------------------------



  • 11.  RE: Combo Primary Key

    Posted 09-10-2019 15:14
    i want the field to be unique but if im trying the way you suggested me its accepting duplicate values as well.

    ------------------------------
    priyanka gummuluri
    ------------------------------



  • 12.  RE: Combo Primary Key

    Posted 09-10-2019 15:17


    ------------------------------
    Michael Grosky
    Director of Customer Strategy, Sympo (Gold QSP)
    mgrosky@sympo.com
    ------------------------------



  • 13.  RE: Combo Primary Key

    Posted 09-10-2019 15:23
    Its showing an error . If i try it.






  • 14.  RE: Combo Primary Key

    Posted 09-10-2019 15:25
    Can you share your formula and the properties of the fields that you are using in the formula?

    ------------------------------
    Michael Grosky
    Director of Customer Strategy, Sympo (Gold QSP)
    mgrosky@sympo.com
    ------------------------------



  • 15.  RE: Combo Primary Key

    Posted 09-10-2019 15:40



    ------------------------------
    priyanka gummuluri
    ------------------------------



  • 16.  RE: Combo Primary Key

    Posted 09-10-2019 15:43
    Will your Order ID always be unique? If so, mark that field unique as well and then try marking the formula unique as well.

    ------------------------------
    Michael Grosky
    Director of Customer Strategy, Sympo (Gold QSP)
    mgrosky@sympo.com
    ------------------------------



  • 17.  RE: Combo Primary Key

    Posted 09-10-2019 15:49
    I have marked orderid as unique and tried but still showing the same error message "This formula is incompatible...."





  • 18.  RE: Combo Primary Key

    Posted 09-10-2019 15:58
    Priyanka,

    It looks to me form your screen shot that one of your fields is a lookup fields, and you are trying to use that in a formula field being marked as Unique.​  A lookup field may not be used that way, because if the value on the lookup field changed at its source, then there would be no way for QuickBase to enforce Uniqueness.

     you would be able to use the field [Related Item] though in a formula to be Unique.

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



  • 19.  RE: Combo Primary Key

    Posted 09-10-2019 16:13
    Thank You . Thanks a lot it worked

    ------------------------------
    priyanka gummuluri
    ------------------------------



  • 20.  RE: Combo Primary Key

    Posted 09-10-2019 14:30
    That is because you cannot set a formula field as the primary key. Is the data being imported in a way where you can create the key prior to import? Or alternatively you can set up a copy of the table and run a table to table import where the key field is populated from the formula field in your original table.

    ------------------------------
    Michael Grosky
    Director of Customer Strategy, Sympo (Gold QSP)
    mgrosky@sympo.com
    ------------------------------



  • 21.  RE: Combo Primary Key

    Posted 11-20-2017 22:31
    Shari,
    I have been tied up today and unable to respond. Can you contact me via the information on my website and I will walk you though the Steps. No charge :). QuickBaseCoach.com