Forum Discussion
I like with both Renee and Chayce's suggestions, but one caveat with Renee's is you'd need to figure out how to make the new key field (your imported values) be unique for future records (e.g. auto-increment). I typically try to avoid changing the default key.
The other option that comes to mind is make a foreign key like Renee suggested, but not change that to the primary key field. Then, you could create a relationship to the old table using that key to reference old values.
I'd suggest Chayce's approach though assuming you haven't burned IDs in the new table by deleting test records, etc. That also potentially allows you to remove the old table and it's one less thing to manage.
The underlying issue here is we are not able to change Quickbase's default primary key values and the next auto-incrementing value like you can with a SQL table.
Ooh, and regarding your error … there's a different between importing and creating new records vs. importing and updating existing records. Or an "upsert" which is combination of both. It sounds like in your import you are trying to use a "merge field" to update records that don't exist. I'd make a test a table to play around with the import process and options that you can throw away once you're finished. In the import process, you'll set of merge field of say "Record ID#" … but, then in the subsequent step where you map fields, select "Do Not Import" to this field and that should create the new records for you using the new tables auto-generated IDs. Then, I'd try what Chayce suggested!
------------------------------
Brian Seymour
------------------------------
Thanks Renee, Chayce and Brian,
These are great suggestions and what I am now understanding is that the Record ID must contain not only unique numbers but also in increment sequence.
My issue is that I do have deleted records in that table which were "burned" otherwise I would attempt the blank import. I am really hoping to avoid changing the key field as it has caused issues for me doing so in the past.
Does anyone have a quick way to repopulate the missing records? Seems like a logistical nightmare...
Thanks,
Josh
------------------------------
Joshua Kaye
------------------------------
- ChayceDuncan12 months agoQrew Captain
You can still do the blank import you'll just have to 'burn' the records from the new table. In practice once you backload the data from your old table there should be at least some data that you can easily run a report of records that don't have anything in them and just delete them back out so your tables match exactly.
Since they are deleted from your old table they are now gone for good
------------------------------
Chayce Duncan
------------------------------- BrianSeymour12 months agoQrew Cadet
It sounds like the issue may be that Joshua has already deleted records in the new table, so IDs are already burned and "not recoverable." Thus, those IDs could not correlate to old table IDs.
For example, in the old table, pretend you have IDs 1 to 1000. And in the new table you deleted 10 test records while you were building/test it (so the next ID internally will be at 11). The question becomes how do you import IDs 1 to 10 from the old table?
You could potentially duplicate the new table to reset the IDs, but that may impact relationships and formulas depending on the complexity of the design at hand.
Or perhaps you could temporarily switch to new a key, but then revert back to the default key to see if that resets the counter (I've never tried that) … but, that's doubtful, as I imagine under the hood QB needs those values to remain for things like their backup processes.
Or perhaps you could open a Support Ticket to see if the QB team could reset your new table's internal record id counter back to 1?
------------------------------
Brian Seymour
------------------------------- ChayceDuncan12 months agoQrew Captain
Are you able to just scrap the current table and just make new one based on some of the feedback above and have a fresh start?
------------------------------
Chayce Duncan
------------------------------