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.
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.
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.