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 the heading of the column you want to
convert and within the menu that appears, select Convert this
field into a table.
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.
If you want to send additional fields into the new table too, click the Additional Fields button on the lower left of the dialog box.
QuickBase displays a list of columns. To select a column turn on its checkbox. When you've made all your selections, click OK.
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.
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.
When you've made your selections, click Next.
In the dialog box that displays, type in a name for the new table.
Type in a term for records in the new table and click OK.
QuickBase displays a message telling you that it has created the new table. The table itself now appears on the far right of your Tables menu. (Want it in a different spot? Reorder your tables.)
Your new table is automatically related to the existing table as the master table. If you don't know what a master 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 master table (What's a master 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 master
(Customers) of a master (Invoices) of a detail 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 details
table at the end of the line. Start by creating the master 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 master, 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 master to the Invoices table and Invoices
will be the master to the Products table. So, when using this conversion
tool to create multiple tables, always create the first master, then the
master of that table, then the master of that table, and so on.
Related Topics