Reply to comment
While QuickBase tables come with the key field set to Record ID#, you can use one of your own fields as the key instead. (To learn how to change the key field, please see this help topic). But what’s the difference? Is it worth making the change?
Why change your key field?
The Record ID is the best choice as key field for most people, so if you’re not regularly importing data into your app you’ll probably be better off not changing your key.
The primary benefit of changing a table's key field is being able to import data into a related details table, pushing the key values into the reference field. That way you don’t need to know the QuickBase Record ID to quickly connect data in two tables when importing.
For example, let’s say you have two tables: a Resources table for which you set your Employee ID# to be the key field, and a related details table for Projects. You have another system that tracks projects, that contains the Employee ID# but not the Record ID for the related resource:
If you import the Employee ID# (Column D in our example) into the Project table’s Related Resource reference field, those projects will automatically link to the correct resource.
Also, if you change a table’s key field, relationships will probably not need a reference proxy. This is because you can specify a key field that’s more descriptive than the Record ID.
Note: If you’re setting up a new app and only need to import data into a related details table once, it’s often preferable to update your spreadsheets to include the QuickBase Record IDs instead of changing your key field. (To learn how, please see Import Data from Excel into an Existing Application.)
What kinds of fields are best to use as key fields?
Identifying data that never changes works best as a key field, such as an Account Number, Store Number, or Employee ID. You can also use other field types such as Text, User, or Email Address, but the values in the field must be unique.
What effect could this have on my users?
When you change the key field for a table, your users would need to manually fill in its value. You’ll want to make sure your users know to search the table for a record they need before adding a new one. Otherwise, they could create a new record and hit save only to find that a record using that key field value already exists!
If the table where you’ve changed the key field already has a related details table, please be aware that changing the value of the key field on the master record will break the relationship to the related details records. For example, let’s say you’ve changed the key field on your Companies table to Company Name, and you have a related details table for Contacts. You have a company called Acme, which has 10 related contacts. The company’s name has changed to Acme Solutions, so you update the name in the company record. However, the reference fields in the related contacts still say Acme and will no longer show up under the Company. To fix this, you would need to browse the records in the details table and use Grid Edit to update the invalid reference values (They'll show up in red).
Some helpful tips:
- When you try to set a key field, you might get an error explaining that the field contains values that are not unique. You’ll need to find the duplicate values and correct them to use that field as the table’s key.
- Changing a table’s key field can be a major change to an app. If you have a field in mind to use as the key, but you’re not sure what effect it will have, consider playing it safe and making a copy of the app before making any changes. This will allow you to make the change in the copy first and confirm that your plan will work out before updating the live app.