Table relationships

Understanding related data

Drew kept track of items for each order in the OrderDetails.xlsx spreadsheet. Let's open it up and take a look.

This time, as you review the spreadsheet, you recognize field names that are shared with the other spreadsheets. This is the information that Drew copied and pasted from spreadsheet to spreadsheet. By defining table relationships in Quickbase, you can automatically get the related information from one table to display on another. Quickbase calls this a table-to-table relationship. Let's do that now.

Create the line items table

The steps to create the Line Items table are a little different from what we did with the Products and Orders tables. Some fields in the Line Items table will have relationships with information stored in either the Products or Orders table, so we don't have to import that data from the spreadsheet.

Only create fields to hold the data that isn't in the other tables. If the data is in the other tables, we'll use lookups to pull it into this table.

Start by creating the table:

1. Click New Table
2. Click From scratch - Design your own table
3. Name the table: Line Items
4. Set A single record is called a: Line Item
5. Select an icon to represent your table
6. Provide a description
7. Click Create

When the Add New Fields dialogue opens, add any field that is a key field in another table or is unique to the line item:

This time we accept the default Record ID# as the key field for the Line Items table.

Relate the line items and products tables

Each line item in the Line Items table is related to a product in the Products table using the Product # field. To avoid typing errors, Quickbase will use this relationship to look up the valid Product # and put them into a dropdown list. Choosing an entry from a list is always easier then typing it in by hand. To make this work, connect the Line Items table to the Products table using a table-to-table relationship. To start building the relationship:

1. Click Table-to-table relationships in the Table Structure group
2. Click the blue New Relationship button

Configure the relationship.

1. Set Line Items connects to Products
2. Click the option that indicates Products may have many Line Items
3. Verify that the sentence Each Product has many Line Items appears
4. Click Next

Set the Product # as the reference field.

1. Set the line items reference field to Product #
2. Click Next

Identify the lookup fields.

1. Set Lookup 1 to Products-Product Name
2. Set Lookup 2 to Products-Unit Price
3. Click the Create Relationship button

Congratulations! You just created your first table-to-table relationship!

Relate the line items and orders tables

Create a table-to-table relationship between the Line Items table and the Orders table.

1. Click Table-to-table relationships in the Table Structure group
2. Click the blue New Relationship button

Create a relationship with the Orders table.

1. Set Line Items connects to to Orders
2. Click the option that indicates Orders may have many Line Items
3. Verify that the sentence Each Order has many Line Items appears
4. Click Next

Identify the reference field.

1. Set the line items reference field to Order #
2. Click Next

Identify the lookup fields.

1. Set Lookup 1 to Orders - Order Date
2. Set Lookup 2 to Orders - Customer Name
3. Select the Create Relationship button

Congratulations! You have related the Line Items table to the Products and Orders table!

Rename fields

Click Fields from the Table Structure group. Let's take a look at what happened in when we created those relationships.

Notice how long some of the field names are.

Let's clean up some of those field names so they're not so long. Here's the process for updating the fields.

Update each of the fields.

1. Click the field name that you want to change
2. Change the Label the name listed below
3. Click the green Save button

Oh, and since we're here, we should sum up the total for each line in the order. Quickbase uses a formula field to store the sum of two fields from the same record.

1. Click New Field
2. Field Label: Line Total
3. Select type: Formula - Numeric
4. Click Add

Notice that the Line Total field was added to the table configuration. Quickbase uses functions, field names (which are represented in square brackets), and mathematical operators in formulas. The common mathematical operators are:

Let's create the formula now. We want to multiply the quantity by the unit price to get the total for that line item.

1. Click Line Total to define the formula
2. Click in the formula editor box. The Choose fields & functions dropdown appears.
3. Select Quantity field from the dropdown
4. Type a * next to [Quantity] in the editor
5. Select Unit Price from the dropdown
6. Verify that the formula is: [Quantity]*[Unit Price]
7. Click Save

Congratulations! You just created your first formula field!

Remember that the formula field type is set to numeric, and we are storing currency in the field. We want the field to display as currency. So we'll set how the field is displayed in reports. Scroll down until you see the Display section appear in your browser window.

Configure the following:

1. Change Decimal places to 2
2. Change the Display as to Currency
3. Click the green Save button in the navigation bar

Import the line items data

You’ve created the Line Items table and connected it to both the Products table and the Orders table. We even added a column to hold the total for each line item. Now it's time to populate this table with the data from Drew’s spreadsheet. Then you can say goodbye to those spreadsheets!

1. Click the Import/Export button
2. Click Import into a table from a file
3. Confirm Select Table is set to Line Items
4. Confirm Select Merge Field is set to Record ID
5. Click Choose file and then select the OrderDetails.xlsx file
6. Click Import From File

Remember, we aren't importing all of the data this time, so make sure that you carefully select what to do with each column.

1. Configure the import as shown below
2. Select the Import button

The resulting page indicates the number of records and fields created.

Verify the import

Congrats! Your app now contains all the data from Drew’s spreadsheets. Click the Line Items table in the table nav and review that everything looks correct.

Verify the following:

• There are no blank fields in the records
• The product # CA8018 displays the correct product name
• The Line Total column is calculating correctly