Connecting tables in AppSheet

At the beginning of our AppSheet app series, we reviewed the current business processes of of our fictional business, GTA HVAC Services. From that review, we were able to determine a few key business processes we wanted to improve. In the second post in this series, we came up with some important tables we thought we might need in our AppSheet app as part of this process improvement project. We came up with the following tables:

  1. Work orders
  2. Parts
  3. Technicians
  4. Customers
  5. Purchase orders

For each of the tables, we came up with some key attributes which are now the column headers in our Google Sheet. We then learned about data types and Ref columns. Ref columns in particular play an important part in connecting pieces of our application together.

In this post we’re going to run with that and start bringing in our other tables and connecting them.

AppSheet tables

In the above image, you can see that we have five tabs at the bottom of our Google Sheet which is our AppSheet back end for this project. Right now we only have two tables in our app, GTA HVAC Services work order app – Work orders and Parts.

If you recall, we connected the two tables together in our last article via a Ref column so a technician filling out a work order could pick from a list of known parts rather than manually type in a part name and number each time.

Now what we want to do is pull in our remaining Google Sheets to make our tables in AppSheet and see if there are any further references that might be useful to us.

 

Select new AppSheet tables

Referencing the images above, from the Data menu, Tables tab, we can see we have the Work orders and Parts tables added. If you click on the Add New Table option, circled in yellow above, it will bring up a Create a new table window from which you can pick your source Google Sheet and the any found worksheet within.

If we pick our Technicians sheet, a 3rd menu, Are updates allowed?, will appear along with a Add This Table button. There are some considerations regarding updates to a table we will discuss in a different post. Right now we’ll leave as default as greyed out above.

Once you click the Add This Table button, your Data menu will show the Technicians table. It will map the columns as best as it can. Checking on the table, we found that the Technician name column was assigned a Name data type, Technician location was assigned text and Technician email assigned email. That all works fine for now.

We’ll do the same now with our Customers and Purchase orders tables.

All tables added

Now that we’ve added all of our tables, what are some connections that might be important to us?

Thinking back, the reason we added a Ref column in our Work orders table for Parts is that we wanted some efficiencies there. We wanted our technicians to pick parts rather than manually type them in. This saves time and eliminates any chance of mistakes.

From an efficiency standpoint, wouldn’t it also be nice if the technician assigned to a work order could be picked from a list of know techs. Customer info on a work order could be picked from the Customers table? Parts on a purchase order picked from the Parts table? Once we create all of these references based on the process I showed you in the last post, we can use the handy app preview tool from within AppSheet to see the effects of our Refs.

Updated work order
Assigned to techs

In the example above, you can see that the Assigned to technician name field is no longer a text entry field. Clicking on it yields a multiple choice menu populated with the technicians found in the Technicians table! We’ve created all of our Refs and table connections now to function in this way.

In the next post, we are going to utilize the AppSheet preview tool to start creating our user interface for our app!