Forum Discussion

5 Replies

Sort By
  • ForrestParker's avatar
    ForrestParker
    Qrew Cadet
    7 years ago
    If the primary and foreign keys are already a part of the data in excel, then it is just a matter of making those fields the Key Field in Quick Base.  

    If you want to use the default primary keys created in Quick Base (Record ID#), then you need to upload the parent records first, export them back to excel to get the Record ID#, then use a vlookup to pull the record ID # from the parent record into the child table data.
  • EstherGrandal_D's avatar
    EstherGrandal_D
    Qrew Cadet
    7 years ago
    Hi Manish, 

    There is a simple way to do it.

    https://help.quickbase.com/user-assistance/setting_the_key_field.html

    If you want, you can designate another field in a table as the key field. Of course this must be a field that contains unique values. The advantage to choosing a different field, is that it might contain a value that's more meaningful than an arbitrary number. For example, fields like Part Number, Account Number or Employee ID#, Project Number, Customer ID,.... usually work well. (If you'd prefer, you can make the key field a text type field instead of a numeric field.) When you create your own key field, Quick Base displays it as a required field on the table's entry form.

    Select in each table the Key filed you want to use in the future, and it lets you to update the data from another external source of data without create new records, because the system detects that the data has already this Key field, so instead of create the new record QB updates the existing ones and only create the new ones if the Key field does not match with the resident records.

    In my case I am keeping the data continually updated with another database, so all my tables use the Key fields I wanted ( I hardly use the Record ID# it is not meaningful for me), it is easier to make relationships if you know the meaning of each key field.

    I will tell you my case, for the project Table I select the project number 


    Remember to make this key field Required and Unique in the settings of the Field.



    If you are not sure , make a copy of you app, change the key in the copy and see how it works before to make it in the real working app.

    I hope it helps.
    • ManishTewari's avatar
      ManishTewari
      Qrew Trainee
      7 years ago
      Thanks for the response Esther. I was trying on the same lines as you are suggesting, and have a "non" Record ID# key set up in my table. Then in the excel data I have assigned the key values and upload those directly during the import into the key field. However, the point where I get stuck is-- When the app goes live, users will start creating new records. At this point, I would like to have new key values populated automatically in the key field. I tired converting the key field to formula type and use Record ID# to create the next value. But Quick Base doesn't allow key field to be a formula type field. Can you suggest some work around?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      Manish,
      I suggest that you do your one time import using the Key fields from excel.

      Then change the Key field of the parent table back to the more typical [Record ID#] field.  Quick Base will give you a couple of scary warnings, but it will perfectly maintain the parent child relationship and the children will stay children of the correct Parents.

      Then, going forward, You will just the [Record ID#] as the Key field which is totally "vanilla" Quick Base.

      To change the Key field you just go to the field list, checkbox beside [Record ID#] and click the gold Set Key at the top of the field list..
    • EstherGrandal_D's avatar
      EstherGrandal_D
      Qrew Cadet
      7 years ago
      Hi Manish.

      You do not need to have a previous key in you excel, you can create it if you want.

      You can set a formula field as a Key field , you only need to use one time this URL in you navigator (X is the Formula Field ID you want to set as a KEY Field(.

      https://target_domain/db/target_dbid?a=API_SetKeyField&fid=X
      &ticket=auth_ticket&apptoken=app_token

      Ihttps://help.quickbase.com/api-guide/setkeyfield.html

      I use it a lot , I make strings using a formula fields ( using lookup fields o reference fields from parents tables), It works consistently 

      Look this example


      as you can see the Key field is a formula and it use a lookup field and a reference field to create it , it makes that all work harmonically .



      I hope it helps.