One-Time Internal Data Conversion in a Huge Table
I'm pleased to have succeeded with implementing cascading dropdown menus (Thanks to Don and Babi for their input!), and now I have to convert the legacy categories (22 main categories and 463 subcategories - yikes!) to the new category system (14 main and 163 subcategories).
If I was in Excel, I would do this with a lookup table to fill in blank fields in the newly created fields of New-Main and New-Sub based on values from the Old-Main and Old-Sub of the legacy data. But I have tens of thousands of records to convert, and this is too large for QB to export as one file. I suppose I can filter and export data for each Old-Main category, do the lookup, and import the updated datafile back into QB. But that seems inelegant.
I have to think that QB can do this. I need to identify blank records in the New-Main and New-Sub Categories, read the Old-Main and Old-Sub Categories, lookup the correct combination of new Main and Sub Categories (in a new temporary table perhaps?) and copy those into the blank fields. This process would only be done ONCE, and at a time when no one else was using the App. Once complete, entering data into the old category system will be taken off the entry form, and the new method will take over.
For Example,
Old-Main + Old-Sub --> New-Main + New-Sub
Bottles + Soda --> General_Americana + Bottles (a reduction in overall detail that we don't need)
Mining + Stocks --> Stocks & Bonds + Mining (Main Category rename and a reversal of category priority)
Aviation + [no Old-Sub] --> Transportation + Aviation (reverse priority and fulfill new requirement for both non-blank values for New-Main and New-Sub)
Those are a few possible combinations that will occur interpreting New from Old.
I don't even know where to begin in QuickBase to do something like this. Is QB even the right tool?
------------------------------
Rob Vugteveen
------------------------------
If I was in Excel, I would do this with a lookup table to fill in blank fields in the newly created fields of New-Main and New-Sub based on values from the Old-Main and Old-Sub of the legacy data. But I have tens of thousands of records to convert, and this is too large for QB to export as one file. I suppose I can filter and export data for each Old-Main category, do the lookup, and import the updated datafile back into QB. But that seems inelegant.
I have to think that QB can do this. I need to identify blank records in the New-Main and New-Sub Categories, read the Old-Main and Old-Sub Categories, lookup the correct combination of new Main and Sub Categories (in a new temporary table perhaps?) and copy those into the blank fields. This process would only be done ONCE, and at a time when no one else was using the App. Once complete, entering data into the old category system will be taken off the entry form, and the new method will take over.
For Example,
Old-Main + Old-Sub --> New-Main + New-Sub
Bottles + Soda --> General_Americana + Bottles (a reduction in overall detail that we don't need)
Mining + Stocks --> Stocks & Bonds + Mining (Main Category rename and a reversal of category priority)
Aviation + [no Old-Sub] --> Transportation + Aviation (reverse priority and fulfill new requirement for both non-blank values for New-Main and New-Sub)
Those are a few possible combinations that will occur interpreting New from Old.
I don't even know where to begin in QuickBase to do something like this. Is QB even the right tool?
------------------------------
Rob Vugteveen
------------------------------
