Forum Discussion

AdiMakhija's avatar
AdiMakhija
Qrew Member
3 years ago

Updating the existing key values for a table based on the Record ID#

I have an app with multiple tables and relationships. For one of the table, I have a key field, called "ID" that was maintained by imports from another system and of course the built-in Record ID# which QB manages. Now, the 3rd party system is changing and all the data from old system is being migrated to the new system. The new database has a completely new numbering syntax and field, called RLID. So I need to update the existing ID field to match the new RLID. I thought I could use the Record ID# of existing records and update the ID field to RLID. However, it seems QB doesn't allow that via mass upload even though I am able to update the ID field manually for each record. Given that I have thousands of records, I don't want to do this manually for each. Is there something I am missing? I have thought about making Record ID# the key field and then do the upload (if it allows me) and then change it back to ID field but not sure if that'll preserve all the relationships or make them messy. I get this huge warning about QB creating new fields if I change the key field, and I am not sure if that'll be the cleanest method. I have taken into account what relationships might be impacted by updating the value in the ID field and I can update the records in the other tables by importing the new RLID so I am covered there. I appreciate any suggestions.

------------------------------
Adi Makhija
------------------------------
Data import and export
Tables and fields
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    Assuming there are no cross app relationships the first thing I would do is make a copy of your application and test everything on that copy before taking a chance on your live data.  

    I presume that you can use Excel to create Excel sheet with the the Record ID number field and the value for a new field called [RLID].  You could choose to override the actual ID field but he will probably feel more comfortable with a brand new field. 

    I would change the Key field of the table back to Record ID#.  Yes you will get that pop-up warning but in fact what will happen is that QuickBase will update all the records in child tables to keep them connected to that parent table where the key field was just changed.  

    Then you should be able to do an Excel upload of the Record ID number field and the value for a new field called [RLID].  You could choose to override the existing ID field but you will probably feel more comfortable with a brand new field, thus preserving, at least initially, the old ID field.  

    Next you will change the key field back to RLID, and once again except all the warnings and Quickbase will once again link every thing up for those child records affected by the Key field change of the parent.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------