Create your first Quickbase table
Quickbase stores data in tables. Tables are like spreadsheets, but better. Tables organize data in rows and columns, and you can use data from one table in another, so you don't have to copy and paste or reenter data like Drew did. Understanding how tables work is the first step in your app building journey.
Review the product list spreadsheet
Drew provided 3 spreadsheet files. Since our first goal is to correct a product name, let's start with the Product List spreadsheet. Opening the
ProductList.xlsx file, we see that it has data in 3 columns, and the name of each column is stored in the first row.
Reviewing the spreadsheet, you write down the following notes:
|Product #||The product SKU||text|
|Product Name||The name of the product||text|
|Unit Price||The price||currency|
To add a table to Quickbase, first you describe what a table is going to store before filling it with data. Let's take what we learned from the spreadsheet and configure a table to hold the product information. Let's get started! 1. Click the New Table button from the table navigation bar. 2. Select From scratch — Design your own table.
Configure the table by giving it a name, record name, icon, and a description to describe how it's used.
- Name the table: Products
- A single record is called a: Product
- Select an icon to represent your table — we chose the first suggested icon
- Provide a description: Product List
- Click the Create button
Great! Creating the table is the first half of the configuration process. Next, add fields to the table and identify what kind of data they store. Remember, field names are like the column headers in a spreadsheet. We'll add the fields based on what we observed in Drew's spreadsheet.
- Fill in a field label for each of the column names from the spreadsheet
- Select the data type for each field based on what we observed
- Click the Add button
Congratulations! You just created your first Quickbase table!
Now you know the steps to create and configure any table that you want in Quickbase. You can return to the Fields settings page anytime to make changes.
Import the products spreadsheet
Now that we have a place to store our data, let's import the products from the ProductList.xlsx file that Drew gave us.
- In the top right section of the page, click Import/Export
- In Choose Action, click Import into a table from a file
- Leave Select Merge Field set to Record ID#
- Click the Choose file button and navigate to the ProductList.xlsx
- Click Import from file
The Import dialogue allows you to configure how the file is imported.
Now that we have set the configuration let's look at how it will be imported.
- The first row of Drew's spreadsheet contains the field names. Make sure this is checked.
- We've already defined the fields and we want the data imported to them
- Look at the data and field names and make sure they match the spreadsheet
- Click the Import button
Once all the data is loaded, the import results page opens to show the results: 14 rows were read, 14 records were added, no records were updated, and there were no data rows with errors.
Success! You've imported your first spreadsheet to Quickbase.
As an aside, what do you think would happen if you accidentally uploaded that same spreadsheet with the same list of products into the app a second time? Would every product appear in the table twice, like shown here?
Yes, and we don't want that! Quickbase requires that each table contains 1 field to use as a unique value for each record. This field is called the key field. If the uploaded data contains the same value of an item in the key field, it'll update the record instead of adding a new one. In our example, if Product # is the key field and the table already contains a record with
Product # QC2019, Quickbase won't add another record with this value during an upload. Instead, it'll update the existing record. This is a convenient way to quickly update values of many existing records, such as when you need to update pricing.
By default, Quickbase automatically creates a
Record ID# field for every table and sets it as the key field. This is useful if your data doesn't contain a field that will always contain unique values. But because our Product # is always a unique SKU, we can make that the key field and avoid duplicate entries in the future.
We are already on the Products table so we just need to go to the Products table's settings to change it. Here's how:
- Click the gear icon in the blue box next to the Products > Products Home breadcrumb
- Click Fields (8) in the Table Structure group
Let's set the Product # field to be the key for the Products table.
- Select the checkbox for Product #
- Click Set Key
- Click the Set Key button in the pop-up
- Verify that the gold key moved to the Product # field
- Click Exit Settings.
Your future self will thank you for setting this up now.
View the table report
Ok, almost done with the Products table. Our last step is to take a look at what was imported and update that mislabeled product name! Start by clicking the Products table icon to view the homepage for this table. Note that whenever you click a table button in the table nav bar, it will display the default table report on the table's Home page.
Update a record
We're looking for Product #
CA8018 which has the wrong product name
Cat 9 Cable 10ft. It should be a Cat 8 cable. With the table report open, we quickly spot the wrong product name. We can correct the product name in the product list from the home page.
- Click the Grid Edit button in the app nav bar
- Double click the wrong product name
- Correct the product name
- Click the green Save button
- Verify that the product name was updated
In just a few clicks, you updated the product name. From now on, it'll be correct in all the orders. Notice that the updated record is now at the top of the table. This is because the default sort order the last modified by field.