Expand all | Collapse all

One-Time Internal Data Conversion in a Huge Table

  • 1.  One-Time Internal Data Conversion in a Huge Table

    Posted 10-24-2019 23:56
    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

  • 2.  RE: One-Time Internal Data Conversion in a Huge Table

    Posted 10-25-2019 06:48

    Did you tried automation to run table import or copy record from you old to new. You may need to add a field in your old table as a check flag to trigger your automation on value change. 

    I am trying to interpret your example and can work-out on exact solution.  However, suggest to try above method. 

    Babi Panjikar

  • 3.  RE: One-Time Internal Data Conversion in a Huge Table

    Posted 10-25-2019 10:00
    Or, you may want to create a report, filtered to records where values in the new fields are blank, that includes both of the old fields (Old-Main + Old-Sub) and both of the new fields (New-Main + New-Sub) as columns. Then you can grid edit to quickly fill in the new fields.

    Brian Cafferelli