Splitting A Table to Several Child Records

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I'll be importing Excel data into an existing table, and then breaking off part of that table into a related table with child records.

The problems is that the author created the spreadsheet rows/columns thusly:

Client A | Contract 1 | Contract 2 | Contract 3

Client B | Contract 1 | Contract 2 | Contract 3

Client C | Contract 1 | Contract 2 | Contract 3

Client D | Contract 1 | Contract 2 | Contract 3

Some of these Contract 2 and Contract 3 columns are blank -- the spreadsheet author just added more columns to the right so she could add future contracts -- but in QuickBase it makes sense for each contract to be it's own record, (rather than have one child record per client with many columns for new contract dates).

The problem is I'm not sure how to split off the table so that contract can be created as a separate child record. I don't think it can be--it seems like I'll have to copy several rows for each client and then move data for the individual contracts to their own row.

Is there another, better method?

Thank you!

Photo of Amber

Amber

  • 588 Points 500 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
I suggest the following.  Bring in your data as it is for now, and create your child structure.  You are quite correct that you want the contracts to be children. But we will get there.


Then use import and "Table to Table" copy (import from another table)  to do an import into the child table.  Copy from the Client table to the Child table. The first import will be filtered where Contract A is not blank.  Among the fields that you will map will be the field for Related Client - that will, of course be the Client Record ID.

Save your import when you run it in case its not perfect the first time and you need to delete children and start over.

Once you get it perfect, you can Copy it and modify the filter to bring in Clients records where Contract B is not blank.

Once you are all done, delete those fields in the Client table for Contract A/B/C.
Photo of Amber

Amber

  • 588 Points 500 badge 2x thumb
Wow, perfect!!!! Thank you...again!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Table to table copy is sweet because you don't need to mess with import / export to excel.  Although if you have a lot of fields to be created, then importing via Excel does have the advantage to helping created your fields the first time.