Import relational data into Quick Base
How can I import related data from excel into 2 tables in Quick Base and then join them using a key? I tried creating primary and foreign keys in excel
Forum Discussion
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.
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.
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..
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..