Quickbase makes it easy to turn a field into a new table in your app. Display a table report that includes the column in question and then click to convert the field into its own table, including related fields.
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 app and test this procedure on the copy before you proceed.
If you need to split a table into three or more tables, read the Splitting a single table into multiple tables section before you begin.
Default table report: Move field into new related table
In the default table report, you can move a single field into a new related table. (Moving multiple fields at a time is only available in the legacy table report.)
-
Open a table report that contains the field you want to convert into a table.
-
Click on a column heading more menu
.
-
Select Move field to new related table.
- You will see a dialog box. Fill out the information and then click Next.
- Choose a name for your new table
- Choose an icon for your new table
- Choose what to call individual records
- (Optional) add a description
- You will see a preview of the records that will be created in the new table. Review the preview and then click Move field. You will be redirected to the new table.
The new table is related to the existing table as the parent table.
If the field used to create a new table was a formula field, the current calculated value in the field is saved to the new table. The original formula will not carry over.
Legacy table report: New table based on this field
-
Display a table report that contains the field you want to convert into a table.
-
Click on a column heading menu
.
-
Select New table based on this field.
-
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 add more fields to the table, click the Additional Fields button in the dialog box. See Adding additional fields to the new table.
-
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.)
The new table is related to the existing table as the parent table.
Adding additional fields to the new table
When you create a new table from an existing field, you can also choose to bring other additional fields.
- In the dialog box that shows you the unique values in the field, click the Additional Fields button.
-
To select a field that you want to add to a table, select the checkbox next to the field name.
-
When you've made all your selections, click OK to dismiss the dialog
Moving more than one field into a new table can require some extra clean up. 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. You need to clean up the extra cities before you can create the new table.
- 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.
Splitting a single table into multiple tables
You can convert columns into more than one table, but we suggest planning carefully before you being moving fields.
- Determine how all your tables should relate to each other. Remember, converting a column into a table always creates a parent table.
- Create the first parent, then the parent of that table, then the parent of that table, and so on
Example:
Your original table tracks sales. You want to convert this table into three tables:
- Customers
- Invoices
- Products
One customer can have many invoices. Each invoice can have multiple products on it. So, in terms of table relationships, that means:
- Customers will be a parent table to Invoices
- Invoices will be a parent table to Products
To create this setup, break out the 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.
- Create the parent to the Products table, which is the Invoices table.
- Convert the Invoice Number column into its own table. Include all the columns that don't belong in the Products table, too. 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, convert the Customer column into its own table. Include all fields that contain customer details.