Convert a Field (or Column) into a Table
The life of an application developer is full of surprises. There's nothing quite like the moment when you realize "Hey, that 'customer' column should really be its own table." But, how to do it?
Quickbase makes it easy to turn a field into a table. Just display a table report that includes the column in question. Then, in a click, convert the field into its own table. You can even take related fields along for the ride.
WARNING: Once you convert a field into a new table, you cannot undo this action. Your field will no longer exist in the original table. If you're not certain that you need a new table, copy the application and test this procedure on the copy before you proceed.
Tip: If you need to turn a single table into three or more tables, read the FAQ at the end of this topic before you begin.
To convert a field into a table:
-
Display or create a table report that contains the field you want to convert into a table.
-
Click on a column heading menu
, then select New table based on this column. -
A dialog box appears, which shows you the unique values that Quickbase found in the field. Each value listed will be a record in the new table.
-
Click the Additional Fields button on the lower left of the dialog box if you want to add additional fields to the new table, then complete the following steps:
-
To select a column that you want to add to a table, select the checkbox next to the column.
-
When you've made all your selections, click OK to dismiss the dialog.
FAQ - Some of my records display in red and Quickbase won't let me proceed. What did I do wrong?
When you toss additional fields into the mix, conversions can get messy. Extra fields often return inconsistencies. For example, say you're converting your Companies column into its own table. One company, Acme Corporation, has offices in New York, Dallas and Portland. So, when you add the City column to the conversion, Quickbase finds three different locations for Acme. A single value in the column you're converting can only match one value in any additional field. Quickbase needs you to clean up the extra cities before it can create your new table. To do so, you have one of two choices:
If you want to create three separate Acme records (Acme-New York, Acme-Dallas and Acme-Portland) click the Conform link at the top of the column. Quickbase will create unique entries for each combination.
or
If the dissimilar entries are mistakes (say Acme only has one office in New York and the other locations are data-entry errors) go back into your table and correct the inconsistencies—in this case, changing all locations to New York. Then try the conversion again.
-
-
Click Next.
-
Click Continue to dismiss the warning dialog.
-
In the dialog box that displays, type in a name for the new table.
-
Type in a term for items in the new table and click Continue.
Quickbase displays a message telling you that it has created the new table. The table itself now appears on the far right of the table bar. (Want it in a different spot? Reorder your tables.)
Your new table is automatically related to the existing table as the parent table. If you don't know what a parent table is, or you need to relate your new table to other tables, then:
FAQ: I need to split columns out into multiple tables. How do I do that?
If you need to convert columns into more than one table you can easily do so, but be thoughtful. First, determine how all your tables should relate to each other. Converting a column into a table always creates a parent table (What's a parent table?). For this reason, you need to create tables in order from the most detailed on up.
For example, say your original table tracks sales. You want to convert this table into three tables: Customers, Invoices, Products. One customer can have many invoices and each invoice can have multiple products on it. So, in terms of Quickbase table relationships, you've got a parent (Customers) of a parent (Invoices) of a child table (Products). To create this setup, you'll break out tables in steps. Your original table which now holds everything will eventually be the Products table—the child table at the end of the line. Start by creating the parent to the Products table, which is the Invoices table. To do so, you'd convert the Invoice Number column into its own table and take ALL the columns that don't belong in the Products table along for the ride. That means, you'll include not only those columns that belong to the Invoices table but also those that will belong to its parent, the Customers table. Once you've created the Invoices table, you can then convert the Customer column into its own table (again bringing fields that contain customer details along with it). The Customer table will be parent to the Invoices table and Invoices will be the parent to the Products table. So, when using this conversion tool to create multiple tables, always create the first parent, then the parent of that table, then the parent of that table, and so on.
