Builder Challenge-Order Tracker Exercise
Hello Quick Base problem solvers! This tutorial describes how to create a Quick Base app by simply uploading a spreadsheet.
(If any of the images are hard to read you can click the image to see them in full size in a new window)
If you have important data saved in an Excel or CSV spreadsheet, you can replace that spreadsheet with a Quick Base App that:
In this tutorial, you will:
1.Go to https://www.quickbase.com/builder-program and sign in at the top right of the page
Note: If you are new Quick Base, click "Join for Free" and set up your free account.
2. Open the My Apps page in Quick Base.
3. Select+ Create new app at the top right corner of the page.
4. Select Import a spreadsheet and then select Next.
5. Click on the following link to open the Line Items.xlsx Excel file:
Get Spreadsheet
6. Highlight all the data in the file (including the headers) and copy the data
7. Select Copy/Paste to paste the data from the .xlsx file into the empty dialog box in Quick Base, and then select Next.
8. From the data review page, you can change the field type for each field.
Carefully review the data for possible changes. In this spreadsheet, the Product Price field at the end of the list may have a field type value of Text-Multiple Choice. From the drop-down, change the field type to Numeric Ð Currency.
You can always change the field type later.
9. After reviewing your data changes, select Create.
10. In the Application Name field, rename the app to Order Tracker, and enter Line Items for Items in this Table are called.
Tables are containers for your data and are very important to any Quick Base app. Later in this tutorial, you will create a few more tables. To learn more about tables, you can visit Quick Base University.
11. Select OK.
The home page of your new Order Tracker app displays. Because the app has multiple choice fields, Quick Base automatically created sample reports for you.
You've just completed creating the new Track Orders app by importing a spreadsheet!
Creating Tables
You are off to a great start; however, to make it easy for users to add products to new orders, you need to make a few changes to the Track Orders app by creating two tables.
To continue the tutorial and create the first of two tables:
1. Select the Line Items table from the Order Tracker app.
As currently designed, to add another product to your order, you would have to enter the Product Name, Product SKU, and Product Price.
However, why should you enter that information when it's already available in your app?
You can save time by creating a table for your orders and products by selecting data from an existing list instead of having to re-enter data.
2. Select the Order # column header, and then select the arrow that appears next to column header
3. From the drop-down, select New table based on this field to extract the Order # field into a separate table.
4. From the dialog that opens, three records (Order # 1006, Order # 1007 and Order #1008) will be created containing the Order #. However, to add more fields to the table, select the Additional Fields button.
5. In addition to the Order # field, move the Order Date and Customer Name fields to the Fields to Extract column.
6. Select OK. Three records are created; one for each order in the spreadsheet containing: Order #, Order Date, and Customer Name.
7. Select Next, and then select Continue.
8. A pop-up appears warning that the fields you selected are being moved into a new table. This is exactly what you want, so, select Continue.
9. Enter Orders as the name for the new table, and any new items in this table will also be Orders.
The Order # field is now a link. Select the link to open the order record. The order record now has an embedded report of products linked to that order, with a button to add additional line items.
You just finished creating the first table, Orders.
To continue the tutorial, create a second table named Products following the same steps (1 through 9) as you did to create the Orders table.
Use the following data for the new Products table:
You have now set up your Order Tracker application to create orders and easily add products to them.
Testing the Order Tracker App
To test your new and improved Order Tracker app:
1. Create a new order for the ABC Co. by selecting the Orders table and selecting New.
Use this data for the ABC Co.:
3. From the Line Items table, you can add a product to the order.
If you want to add more Line Items, you can select the arrow in the green button and select Save and New to keep adding line items.
4. Once you are finished adding line items, select Save and Close.
You can now select from the list of products, as well as add any new products that the company might carry.
You can return the Order Tracker app home page to see that your report was updated with your additional data. when you added new data.
Creating a Formula Field
Our app is working great! However, we need a new field that will calculate the Line Item Total. To do that we will need to create a formula field that multiplies the [Product Price] by the [Quantity].
1. From the Line Items table, select the blue gear (Settings) in the top left of your screen to go into the table settings.
2. Under Table Structure, select the + New to add a new field.
3. Under Field Label, enter Line Item Total.
4. For the field Type, select Formula-Numeric.
5. Select Add.
You can now add the formula to the field by clicking on the field label. The Field Settings page displays.
6. You can either select the ÒSelect fields & functionsÓ drop-down, or enter information into the formula dialog box to create the following formula:
7. Now look at your new field on the Line Items table report
Creating and Modifying Reports
1. From the Line Items Table, select Reports & Charts.
2. Select +New.
3. Select Summary, and then select Create.
4. Enter Overdue Orders as the report name.
5. Under Summarize Data, change the selection from [# of Line Items] to [Line Item Total] in the Summarize field.
6. Under Grouping and cross tabs, group by [Order #] then by [Customer Name].
7. Scroll down to the Filters section. Under the Initial filters, select Filter line items.
8. Set the filter to only include line items where the [Order Date] is on or before today.
9. Select Save.
Depending on when you complete this tutorial, there should be at least one order where the [Order Date] is in the past.
Notice that this report type does not list all Line Items, but rather shows the overall total of the order. You can click on the Order # to drill down and see the specific Line Items.
Modifying the Home Page
Now that you have created a new report, letÕs add it to our dashboard to make it easily accessible.
1. Click the Home icon.
Every time you create an app, there will be a Default Dashboard. You can modify this dashboard, as well as, create several dashboards and assign them to groups of users.
2. Select customize this report.
3. Hover over the 1st Pie Chart and select the cog wheel icon.
4. In the Report drop-down, select the Overdue Orders report you just created.
5. Verify that the text above the report says Overdue Orders.
6. Select Save.
Create a Notification Email
Your last task is to create a Notification Email that will alert you whenever a new Line Item is added.
1. From the Line Items table, select the blue gear (Settings) in the top left of your screen to go into the table settings.
2. Under Workflow & Permissions, select the + New to add a new Notification.
3. Select Notification for the email type and select Create.
4. Set the Notification to be sent when a line item is added and select Save.
Congratulations!
LetÕs summarize what youÕve learned in this tutorial:
(If any of the images are hard to read you can click the image to see them in full size in a new window)
If you have important data saved in an Excel or CSV spreadsheet, you can replace that spreadsheet with a Quick Base App that:
- Eliminates duplicate data entry
- Calculates values automatically using formula fields
- Enables you to generate reports that update when your data changes
- Alerts you when data changes
In this tutorial, you will:
- Create a Quick Base app to track orders by importing a spreadsheet
- Edit the newly created Track Orders app and create tables to streamline data entry
- Create a formula field that allows you to calculate the Order Total
- Build a report and add it to a custom dashboard
- Create an email notification
1.Go to https://www.quickbase.com/builder-program and sign in at the top right of the page
Note: If you are new Quick Base, click "Join for Free" and set up your free account.
2. Open the My Apps page in Quick Base.
3. Select+ Create new app at the top right corner of the page.
4. Select Import a spreadsheet and then select Next.
5. Click on the following link to open the Line Items.xlsx Excel file:
Get Spreadsheet
6. Highlight all the data in the file (including the headers) and copy the data
7. Select Copy/Paste to paste the data from the .xlsx file into the empty dialog box in Quick Base, and then select Next.
8. From the data review page, you can change the field type for each field.
Carefully review the data for possible changes. In this spreadsheet, the Product Price field at the end of the list may have a field type value of Text-Multiple Choice. From the drop-down, change the field type to Numeric Ð Currency.
You can always change the field type later.
9. After reviewing your data changes, select Create.
10. In the Application Name field, rename the app to Order Tracker, and enter Line Items for Items in this Table are called.
Tables are containers for your data and are very important to any Quick Base app. Later in this tutorial, you will create a few more tables. To learn more about tables, you can visit Quick Base University.
11. Select OK.
The home page of your new Order Tracker app displays. Because the app has multiple choice fields, Quick Base automatically created sample reports for you.
You've just completed creating the new Track Orders app by importing a spreadsheet!
Creating Tables
You are off to a great start; however, to make it easy for users to add products to new orders, you need to make a few changes to the Track Orders app by creating two tables.
To continue the tutorial and create the first of two tables:
1. Select the Line Items table from the Order Tracker app.
As currently designed, to add another product to your order, you would have to enter the Product Name, Product SKU, and Product Price.
However, why should you enter that information when it's already available in your app?
You can save time by creating a table for your orders and products by selecting data from an existing list instead of having to re-enter data.
2. Select the Order # column header, and then select the arrow that appears next to column header
3. From the drop-down, select New table based on this field to extract the Order # field into a separate table.
4. From the dialog that opens, three records (Order # 1006, Order # 1007 and Order #1008) will be created containing the Order #. However, to add more fields to the table, select the Additional Fields button.
5. In addition to the Order # field, move the Order Date and Customer Name fields to the Fields to Extract column.
6. Select OK. Three records are created; one for each order in the spreadsheet containing: Order #, Order Date, and Customer Name.
7. Select Next, and then select Continue.
8. A pop-up appears warning that the fields you selected are being moved into a new table. This is exactly what you want, so, select Continue.
9. Enter Orders as the name for the new table, and any new items in this table will also be Orders.
The Order # field is now a link. Select the link to open the order record. The order record now has an embedded report of products linked to that order, with a button to add additional line items.
You just finished creating the first table, Orders.
To continue the tutorial, create a second table named Products following the same steps (1 through 9) as you did to create the Orders table.
Use the following data for the new Products table:
- Select the Product SKU column header from the Line Items table of the Order Tracker app. (See Steps 1 through 3)
- Select Product Name and Product Price as Additional Fields to move and extract. (See Steps 4 through 8)
- Enter Products as the name for the new table, and any new items in this table will also be Products. (See Step 9)
You have now set up your Order Tracker application to create orders and easily add products to them.
Testing the Order Tracker App
To test your new and improved Order Tracker app:
1. Create a new order for the ABC Co. by selecting the Orders table and selecting New.
Use this data for the ABC Co.:
- Order # = 1009
- Customer Name = ABC Co.
- Order Date is 04-25-2019
3. From the Line Items table, you can add a product to the order.
If you want to add more Line Items, you can select the arrow in the green button and select Save and New to keep adding line items.
4. Once you are finished adding line items, select Save and Close.
You can now select from the list of products, as well as add any new products that the company might carry.
You can return the Order Tracker app home page to see that your report was updated with your additional data. when you added new data.
Creating a Formula Field
Our app is working great! However, we need a new field that will calculate the Line Item Total. To do that we will need to create a formula field that multiplies the [Product Price] by the [Quantity].
1. From the Line Items table, select the blue gear (Settings) in the top left of your screen to go into the table settings.
2. Under Table Structure, select the + New to add a new field.
3. Under Field Label, enter Line Item Total.
4. For the field Type, select Formula-Numeric.
5. Select Add.
You can now add the formula to the field by clicking on the field label. The Field Settings page displays.
6. You can either select the ÒSelect fields & functionsÓ drop-down, or enter information into the formula dialog box to create the following formula:
[Product Price]*[Quantity]
7. Now look at your new field on the Line Items table report
Creating and Modifying Reports
1. From the Line Items Table, select Reports & Charts.
2. Select +New.
3. Select Summary, and then select Create.
4. Enter Overdue Orders as the report name.
5. Under Summarize Data, change the selection from [# of Line Items] to [Line Item Total] in the Summarize field.
6. Under Grouping and cross tabs, group by [Order #] then by [Customer Name].
7. Scroll down to the Filters section. Under the Initial filters, select Filter line items.
8. Set the filter to only include line items where the [Order Date] is on or before today.
9. Select Save.
Depending on when you complete this tutorial, there should be at least one order where the [Order Date] is in the past.
Notice that this report type does not list all Line Items, but rather shows the overall total of the order. You can click on the Order # to drill down and see the specific Line Items.
Modifying the Home Page
Now that you have created a new report, letÕs add it to our dashboard to make it easily accessible.
1. Click the Home icon.
Every time you create an app, there will be a Default Dashboard. You can modify this dashboard, as well as, create several dashboards and assign them to groups of users.
2. Select customize this report.
3. Hover over the 1st Pie Chart and select the cog wheel icon.
4. In the Report drop-down, select the Overdue Orders report you just created.
5. Verify that the text above the report says Overdue Orders.
6. Select Save.
Create a Notification Email
Your last task is to create a Notification Email that will alert you whenever a new Line Item is added.
1. From the Line Items table, select the blue gear (Settings) in the top left of your screen to go into the table settings.
2. Under Workflow & Permissions, select the + New to add a new Notification.
3. Select Notification for the email type and select Create.
4. Set the Notification to be sent when a line item is added and select Save.
Congratulations!
LetÕs summarize what youÕve learned in this tutorial:
- Imported data to create a new app
- Used the New table based on this field feature to create and test new tables and relationships
- Created a formula field
- Created a report
- Modified a home page
- Created a notification
