How to use a Ref column in AppSheet

Yesterday we filtered through the data types in our AppSheet based, GTA HVAC Services work order app. Our Parts used column in the work order table was set to a Text data type. This would require a technician to manually type in parts used on a work order every time. A process that would be time consuming and prone to errors. AppSheet has a better data type option for a workflow like this called a Ref, short for reference.

So what is a Ref data type?

Remember that our AppSheet app is made up of a Google Sheet, our database essentially, and the worksheets in that main sheet. Our Google Sheets worksheet, Work orders, functions as our table and Parts used is one of the columns in the table.

But we can have more than a single table/worksheet in an AppSheet app. We can add as many as we’d like. We can add a new worksheet for Parts used and in that worksheet a dedicated row for each part. Take a look at the images below, you’ll see a new tab/worksheet for Parts and some sample data in that worksheet.

Add a Parts sheet to your Google Sheet
Sample data in Parts sheet

This is where the Ref comes in to play. If we change the Parts used column in the Work orders table to a Ref data type we are creating a connection or relationship between the two tables/worksheets. It allows the Parts used column to now reference all of the rows in the Parts table.

At the end of the day, this means the technician filling out the work order can pick from a list of parts rather than typing them in. It also means from within our AppSheet app we can see relationships that might help us make informed business decisions.

For example, within a parts record we can see how many work orders that particular part was replaced in. This could help us identify scenarios such as a faulty installation procedure that needs to be revised. Or we could potentially identify a batch of a particular part that seems to fail more than normal.

So how do we make this all happen in AppSheet?

You get the gist of how the back-end Google Sheet is structured now but how do we put this into play in AppSheet. Well, AppSheet makes it very easy to implement.

Once the Parts worksheet is in your main Google Sheet, you’ll notice that there will be an option to “Add a table for “Parts” in the Tables menu of AppSheet. If you click it, AppSheet will do all of the heavy lifting and add the table. Double check your data types in the table to be sure they are all correct. If they are, click on the Work order table and then the Part used column. Change the Type to Ref from Text. When you do this a Type Details menu will allow you to pick the source table for your Ref that will be Parts. Pick Parts, click Done and Save. This is all you have to do.

Now when you test out a work order, you’ll have a drop down menu for Part used that will contain all of the parts available in the Parts table!

This is all there is to it. Now you know some of the advantages of Ref data types in AppSheet and how to implement them.

In our next post, we’ll create some more Refs in our AppSheet app and start to bring it all together.

AppSheet has some great documentation running through all the ins and outs of Ref types that’s worth spending some time going through.


Parts used field