Simple Database and Applications

In this lab exercise, you will create a simple e-commerce database and applications using Google AppSheet.

Create the Database​

1. Sign in the Google account that you want to use for this exercise.
2. Go to Google AppSheet.
3. Go to "Create | Database | New database".
4. If this is your first time using AppShet:
   1. Click the "Get Started" button.
   2. Click through the tutorial, if you like.
5. Click on "+ Add column".
6. For "Name", enter "Email".
7. For "Type", choose "Email".
8. Click on the "Save" button.
9. Click on "+ Add column".
10. For "Name", enter "Quantity".
11. For "Type", choose "Number".
12. Click on the "Save" button.
13. Click on "+ Add column".
14. For "Name", enter "Colour".
15. For "Type", choose "Color".
16. Check all the colours.
17. Click on the "Save" button.
18. Click on "+ Add column".
19. For "Name", enter "Item".
20. For "Type", choose "Enum".
21. Click on the "Add value" button.
22. Enter "Plate".
23. Click on the "Add value" button.
24. Enter "Bowl".
25. Click on the "Add value" button.
26. Enter "Cup".
27. Uncheck "Allow other values".
28. Click on the "Save" button.
29. Drag the existing "Status" column to the very end.
30. Click the 3 dots next to the "Assignee" column and choose "Delete column".
31. Delete the "Date" column.
32. Click the 3 dots next to the "Email" column and choose "Use column as label".
33. Delete the "Title" column.
34. Right-click just to the left of "1" for row 1 and choose "Delete row".
35. Delete the other 3 rows.
36. Click the 3 dots next to "Table 1" and choose "Table settings".
37. Change the table name to "Order".
38. Click on the "Save" button.
39. Click inside "Untitled database".
40. Change the database name to "Store".
41. Click on the main AppSheet icon.

Create the Order Application​

1. Go to "Create | App | Start with existing data".
2. For "App name", enter "New Order".
3. For "Category", choose "Other".
4. Click the "Choose your data" button.
5. Choose "AppSheet Database".
6. Choose "Store".
7. We only want this app to add new order records, so uncheck "Update" and "Delete" to the right of "Order".
8. Confirm that only "Add" shows to the right of "Order".
9. Click on the "Create app" button.
10. Click on the "Customize with AppSheet" button.
11. Click on the "Views" icon on the far left toolbar (it looks like a little mobile phone).
12. Under "View type", choose "form". This becuase we want to present a fill-out form by default to the customer.
13. Under "Finish view" choose "Order_Detail". This will summarize the order for the customer.

Customize the Email field​

1. Hover over the "Email" field in the app preview. Click on the edit icon (looks like a little pencil). Choose "Edit Column".
2. Expand the "Data Validity" section.
3. Check the "Require?" checkbox.
4. Expand the "Auto Compute" section.
5. Click inside the "Initial value" field area.
6. Choose the "Other" tab.
7. Way over to the far right of the "USEREMAIL()" pattern, choose "Insert".
8. Click on the "Save" button.
9. Expand the "Update Behavior" section.
10. Uncheck "Editable?". We just want to fill in the email field with the customer's logged in email. We don't want them to change it.
11. Scroll to the top of the dialog box.
12. Click the "Done" button at the top right.

Customize the Quantity field​

1. Hover over the "Quantity" field in the app preview. Click on the edit icon (looks like a little pencil). Choose "Edit Column".
2. For "Maximum value" enter "10".
3. For "Minimum value" enter "1".
4. For "Increase/decrease step" enter "1".
5. Expand the "Data Validity" section.
6. Check the "Require?" checkbox.
7. Expand the "Auto Compute" section.
8. Click inside the "Initial value" field area.
9. In the "Enter an expression" area, enter "1" (no quotes).
10. Click on the "Save" button.
11. Scroll to the top of the dialog box.
12. Click the "Done" button at the top right.

Customize the Colour field​

1. Hover over the "Colour" field in the app preview. Click on the edit icon (looks like a little pencil). Choose "Edit Column".
2. Expand the "Data Validity" section.
3. Check the "Require?" checkbox.
4. Expand the "Auto Compute" section.
5. Click inside the "Initial value" field area.
6. In the "Enter an expression" area, enter "Red" (yes, include the quotes here).
7. Click on the "Save" button.
8. Scroll to the top of the dialog box.
9. Click the "Done" button at the top right.

Customize the Item field​

1. Hover over the "Item" field in the app preview. Click on the edit icon (looks like a little pencil). Choose "Edit Column".
2. Expand the "Data Validity" section.
3. Check the "Require?" checkbox.
4. Expand the "Auto Compute" section.
5. Click inside the "Initial value" field area.
6. In the "Enter an expression" area, enter "Plate" (yes, include the quotes here).
7. Click on the "Save" button.
8. Scroll to the top of the dialog box.
9. Click the "Done" button at the top right.

Customize the Status field​

1. Hover over the "Status" field in the app preview. Click on the edit icon (looks like a little pencil). Choose "Edit Column".
2. Expand the "Data Validity" section.
3. Check the "Require?" checkbox.
4. Expand the "Auto Compute" section.
5. Click inside the "Initial value" field area.
6. In the "Enter an expression" area, enter "Not Started" (yes, include the quotes here).
7. Click on the "Save" button.
8. Expand the "Update Behavior" section.
9. Uncheck "Editable?". The initial status for an order is always "Not Started" and we don't want the customer to be able to change it.
10. Scroll to the top of the dialog box.
11. Click the "Done" button at the top right.

Finishing up the App​

1. Click the big "SAVE" button at the top right to save your changes so far.
2. In the toolbar on the far left, click on the "Data" icon (the one just above the "Views" icon).
3. For the "Status" field, uncheck the "Show?" attribute. The customer doesn't need to see this field.
4. In the toolbar on the far left, click on the "Settings" icon (looks like a gear).
5. Under "App Properties" turn on the "Personal use only?" setting.
6. Click the big "SAVE" button at the top right to save your changes.

Deploying the App​

1. In the toolbar on the far left, click on the "Deploy" icon (looks like a rocket).
2. Click "Deployment Check".
3. Scroll down. It looks like there is an error. You can click the "ERROR" button to see the details. For personal use apps, this is not a problem.
4. Click the "Move app to deployed state despite errors" button.

Running the App​

1. In the toolbar at the top, click on the "Share" icon (looks like a "+" sign next to a head-and-shoulders icon).
2. Click on "Copy sharing links" and copy "Browser Link".
3. In a new browser tab, paste the link.
4. Click on "Save" to place the first order (1 red plate).
5. Use the back arrow to go back to the order form.
6. Change the fields for 3 green Cups.
7. Click on "Save" to place the second order.
8. In another tab, go back to the AppSheet main page.
9. Go to "Databases | Store".
10. Verify that there are 2 orders in the Order table.

Create the Process Order Application​

1. Click on the main AppSheet icon.
2. Go to "Create | App | Start with existing data".
3. For "App name", enter "Process Order".
4. For "Category", choose "Other".
5. Click the "Choose your data" button.
6. Choose "AppSheet Database".
7. Choose "Store".
8. We only want this app to update existing order records, so uncheck "Add" and "Delete" to the right of "Order".
9. Confirm that only "Update" shows to the right of "Order".
10. Click on the "Create app" button.
11. Click on the "Customize with AppSheet" button.
12. In the toolbar on the far left, click on the "Data" icon (the one just above the "Views" icon).
13. Uncheck "EDITABLE?" for "Email", "Quantity", "Colour", and "Item" fields.
14. Only Status should be "EDITABLE?" checked.

Only Show "Not Started" Orders​

1. Click on the "Views" icon on the far left toolbar (it looks like a little mobile phone).
2. Click on the "Use slices to filter your data" link.
3. Click the "Create a new slice for Order" button.
4. Click in the "Row filter condition" field.
5. Click the "Create a custom expression" link.
6. In the "Enter an expression" area, enter:

   [Status] = "Not Started"
   
7. Click the "Save" button.
8. Click the "Done" button at the top right.
9. Click the big "SAVE" button at the top right.

Finishing up the App​

1. In the toolbar on the far left, click on the "Settings" icon (looks like a gear).
2. Under "App Properties" turn on the "Personal use only?" setting.
3. Click the big "SAVE" button at the top right to save your changes.

Deploying the App​

1. In the toolbar on the far left, click on the "Deploy" icon (looks like a rocket).
2. Click "Deployment Check".
3. Scroll down. It looks like there is an error. You can click the "ERROR" button to see the details. For personal use apps, this is not a problem.
4. Click the "Move app to deployed state despite errors" button.

Running the App​

1. In the toolbar at the top, click on the "Share" icon (looks like a "+" sign next to a head-and-shoulders icon).
2. Click on "Copy sharing links" and copy "Browser Link".
3. In a new browser tab, paste the link.
4. Select one of the "Not Started" orders.
5. Click on the "Edit" button.
6. Change the "Status" to "Complete".
7. Click the "Save" button.
8. Notice the order disappears from the "Not Started" list!
9. In another tab, go back to the AppSheet main page.
10. Go to "Databases | Store".
11. Review the "Status" of each order.