Changing the primary key field in a table that already has relationships

Question

When I created my QuickBase application, I didn't have the full set of data (no primary keys). So I imported the data into QuickBase, allowing it to create it's own Primary Key. There are now 2 tables with relationships linked to those temporary Primary Keys.

Today, I got the full set of data, including their internal primary keys (just a sequential number). So what I'd like to do is update the temporary key with with company's key.

I tried using Quickbase's internal tool, but if I import the company's key and set that as the key in the Field section, it won't automatically set primary keys for new records.

I tried overwriting the temporary keys values, but Quickbase makes them un-editable. I tried importing in new values for the temporary keys, but I get an error saying, "Cannot find all key numbers" or "Can't update two columns at the same time".

There must be a way to do this? Or do I literally need to make a new table without data and re-create all my relationships manually? But even if I do that, will Quickbase automatically increment the assigned internal primary key, or will I have to update it?

Thanks!

quickbasecoachd · Answer

The [Record ID#] field is the default Key to the table. Typically in Quick Base "speak" we do not call that the Primary Key as there is only 1 Key per Table, so that is the Key field.

If you are now have imported new data which includes a different field that you want to have be the Key field, you can set that new field to be the Key field and after a few scary warning message, Quick Base will make the required changes so that any child record will remain attached to the correct Parents.

Its always a good idea to play with this in a copy of your application if you have not had much experience with changing the Key field.

But once you make that change, then while the [Record ID#[] field will always continue to exists and will always auto increment right up to infinity (well at least to an incredibly big number), your new Key field is now a data entry field that you must maintain manually. It will not auto increment.

I think that you need to reconsider why you want to change the Key field. Is your data going forward going to be hand typed into the app. If so, why change the Key field?

Or is the data going to be imported for another system which will do the auto incrementing of your new Key field? if that is the case, then you do not need Quick Base to auto increment.

Maybe your goal is to retain the value of the Key field for old records being imported and have Quick Base auto number going forwards.

I that case, you could import enough blank records from excel to create the highest record ID# count that you need. Let's say that is 9,000 records. So create say 10,000 records which are blank. (You will need to have at least 1 dummy field being loaded)

Then import your legacy data and map the old Key field into the record ID# field. Most of those 10,000 records will be updated. Those records will be updated. if there were deleted Keys in the legacy data, then those Record ID's will not get populated.

Then delete all the blank records.

Now, new manually entered records will start fresh at Key field ([Record id#]) of 10,000 so every knows that records below 10,000 were from the big import and above 10,000 were the new Quick Base system records.

katetheriault · Answer

The second half of your comment is exactly right. I'd like to maintain the old values and have Quickbase autoincrement the new values. I will try that method, thank you. You are so reliable on here; it's really incredible. /hats off

quickbasecoachd · Answer

Thx, let me know how it goes.&nbsp; As I was typing I realized your likely goal so that is why the 2nd half of my answer was more relevant. :)

katetheriault · Answer

So my current record ID for that table is around 2000, and the old keys only go until about 1600. I'm confused how to overwrite the Record ID. I have two columns in my excel sheet. The Record ID from Quickbase and the Old ID. When I try to Select Existing Field for importing the Old ID to the Record ID, I get this error:

Some record IDs specified were not found in the table.

I might have to tell you more details. I've been using this table as a testing ground, so many of the records that previously existed for testing don't exist anymore. Is that causing this problem? But I don't understand because the Record IDs up to 2000 must have existed at one time because it is an autoincrement.

The column on the left is the current Record IDs of all the data. The column on the right is the Old ID. How do I overwrite the left column? I feel silly, I must be missing something obvious here.

quickbasecoachd · Answer

I should mention that I have never exactly tried this technique, but it should work. :)

First of all, the technique does depend on all the record ID's below 1600 existing. If you ever deleted a record from your test app, then that will be a showstopper.

So odds are very high that you will need to copy the app without data.

So I think that the steps would be

1. Copy the app without data

2. In the Copy app change the key field of the main parent table back to [Record ID#]

2. Then create say 2000 records in the main Parent table from excel by importing say an X into a dummy field. That will run the Record ID# up to 2000.

3. Next is to copy the data for the main Parent table across to the Copy App. You can actually do a table to table copy now from your old app into the new app for the main table. Just run a "List All" report from the old app and use the "More" button. The fields should all line up properly as they are identically named, but then carefully map the Legacy Primary Key into the Record ID#. It should give you a warning that existing records may be updated. That is exactly what you want to happen.

4. If that all works, then you will similarly do a List All report from the child table and carefully map the field for [Related Primary Key] into the field [Relate Parent] but in fact I think it will line that up correctly automatically as really it is still being mapped to [related parent].

5. Repeat for the next child table and then of course you may have some other tables with data that do not have relationships.

Forum Discussion

Changing the primary key field in a table that already has relationships