How to create an effective UX in AppSheet

  • Post Author:
  • Post Category:AppSheet

In our last post we hooked all of our data tables together in order to create a cohesive application for our GTA HVAC service crew.

Thinking about and structuring our data effectively is extremely important. It lays the foundation for the eventual roll out of an AppSheet application that will help us streamline our operations.

Likely more important to the actual users of the application is the look, feel and flow of the application. And so we come now to creating our app’s user interface.

AppSheet UX menu

All of the user interface tweaks and adjustments are made from the UX menu in AppSheet. There are 3 types of views: primary, menu and reference.

Primary views are the views most often used within the app. An icon for each is located on the bottom bar of the phone or tablet screen. You can see an example in the emulator in the bottom right corner of the picture above.

Menu views are located in the drop down menu which is always accessible from the top left hand corner of the screen. Views that are used occasionally are best created as menu views.

Finally, reference views are usually system created although it is possible to create your own. They are views within views. So for example, if you have a customer view and a work order view,  could have a reference view of all service tickets pertinent to a specific customer from within your work order view.

Within the three views are eight view types: Deck, Table, Gallery, Detail, Map, Chart, Form and Dashboard. The AppSheet website has great documentation explaining use cases for each view type.

Now that we know the essentials, we can start to think about how we’d like our GTA HVAC Services work order app to look.

If we start from the beginning of the work order process, we should consider the dispatcher/coordinator position first. Ideally that person should be able to see everything. All customers, parts, technicians, work orders and purchase orders. One of the best views for a comprehensive view of everything is the Dashboard view type.

Let’s try to build a Dashboard view for our dispatcher. We’ll assume our dispatcher works from a desktop computer and will access the view through a browser rather than a phone/tablet app. She’ll have plenty of screen real estate to be able to view everything.

We’ll want to make this a primary view as our dispatcher will be using the view constantly.

The first step will be to click the Add New View icon in the UX menu of AppSheet. See the icon circled in yellow below.


AppSheet add new view

Clicking the Add New View icon will open a New View dropdown with a whack of configuration options. You can see what it looks like in the images below. We’ll name the view GTA HVAC Service Dashboard.

Initially you’ll see a “For this data” pull down immediately below the View name. When you click the dashboard view type, this menu will disappear in keeping with the fact the Dashboard view can pull in multiple pieces of data from your tables.

Setting up dashboard view
Setting up dashboard view - continued
The Position option determines where you’ll see the dashboard icon on your display. In this case, as our dispatcher will be using this view all the time, we’ve put it a primary view right in the center of the bottom bar.
Below this we have a variety of view options. The View entries is probably the most important. You can pick the data views you’d like to pull in to your dashboard and also the size/orientation of them. We’ve pulled in Technicians, Work orders, Map and Purchase orders.
Using tabs in mobile view is handy. It defaults to off but if you want to use a dashboard view on a cellphone or tablet, it’s worth it to turn it on. It will give you some tabs at the top of your screen to navigate between dashboard views so your screen doesn’t end up cluttered.
There is a ton of other options as you can see but they aren’t key to describing our views so we’ll leave them out for now.
Once complete, this is what your dashboard will look like.
Dashboard View

The sizing and orientation of each view within the dashboard can be adjusted within the View Options in the UX menu. One great feature is the ability for the end user to move the views around within their browser. Gives a bit of customization to those who might not have access to the back end of the app.

So this is great, one of the most useful views for sure.

But what about the technicians? It wouldn’t be beneficial for them to try to see a dashboard view on a mobile phone or tablet. It would be distracting if anything.

In my opinion one of the best views for a field tech is the Table View. So let’s set up a view for our technicians to see and fill out work orders in Table View. Furthermore, let’s split up the work orders by the Status field with options Open and Closed.

So let’s look at the data first. We have a variety of tables but GTA HVAC Services wants the technicians to focus on customer facing and billable work. We should keep the technician interface as uncluttered and streamlined as possible. A technician should be able to see open jobs to be completed, previously completed jobs for reference and a parts list.

So  how can we accomplish this?

If you recall in our Work Orders table, we have a Status column with the Enum values Open and Closed. There is a nice feature called Slices that allows you to create slices of tables to filter down info.

Appsheet slice

Looking at the image above, you see we can make a slice of Work order table rows that have the Status of Open based on a row filter condition we incorporate. We can do the same with Closed.

Once we have these Slices in place, we can then create interfaces that show open and closed work orders instead of all. Based on what we know about views, the technicians should be able to see Open and Closed views from the bottom navigation bar. But should not be able to see the views the dispatcher can.

So first we’ll add the Open and Closed slices as primary views.


Open Slice view

Looking at the image above, you can see that you’ll give the view a name which is what the tech will see on his or her navigation bar. Pick the Open slice for your data source and the Table view type is a nice tidy way to display the work order rows. Picking the left position will show the Open tab on the left side of the navigation bar. We followed the same process with the Closed slice with the exception of picking the right position.

We have all of these views now. But we want our dispatcher to see the overall dashboard and some views the technicians don’t need to see.

The best way to determine which user sees what view is to use the Show if field of the view. This allows you to show the view based on an if statement that gives a yes/no value. An easy way to accomplish our goal is to show view based on login email. Each technician will have a login email and the dispatcher will have her own as well. So, for instance, you could only show the Dashboard view to the dispatcher by showing if the login email is the dispatcher’s. See the image below for an example.

Show if display

If you look at the image on the right, you can see that the bottom navigation bar is tailored to a technician on a mobile phone now, with access to the Open and Closed tickets.

You’ve learned how to create a basic AppSheet interface now. The options you have to customize are far beyond what we’ve covered in this post but this gives you an idea specific to building a work order application.

In the final two posts of this series, we’ll cover some of the small details like filtering tickets by user and rolling out the application. The final post will cover how to create a workflow that automatically sends a completed work order to a customer.

Open Closed View
Continue Reading How to create an effective UX in AppSheet

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!

Continue Reading Connecting tables in AppSheet

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
Continue Reading How to use a Ref column in AppSheet

How to choose the correct AppSheet data types

  • Post Author:
  • Post Category:AppSheet

We’ve come a fair ways now. We’ve discussed how to think about business processes in the context of building an AppSheet app, set up our Google Sheet and connected Appsheet to our Google Sheet. It’s time to start hacking together our app!

AppSheet will infer your data types from your Google Sheet using a few different methods. Easy things like bold typing your headers and ensuring your entire column spreadsheet column has a consistent data format help. I’ve found that most of the time AppSheet gets it right. But you’ll want to double check them all to be sure.

AppSheet already has a ton of great documentation outlining all of its possible data types so we won’t review it here. But we will double check all of our table columns to ensure they are correct.

So how do we do this?

The first thing you’ll want to do is login to your AppSheet account, go to your My Apps tab and find your app. Click on the Edit button to open it.

Once your app opens, click on the Columns tab at the top and then the Work orders table.

The Work orders table will open and you’ll notice a left column titled Name that will match the headers of each column in your Google Sheet. The column immediately to the right will be titled Type. Each row will have a type associated to the column name. AppSheet figured these out when you connected your Google Sheet!

So we can unpack this a bit. We have the following columns and data types in our app:

_RowNumber – you didn’t add this column explicitly in your spreadsheet but AppSheet will add it so it is able to identify the row number being referenced in the table at any time. It is as it reads, the number of the row.

Date – let’s look deeper into this one. In the image above we can see AppSheet picked the data type of Date for date. Sounds about right. If you click on the square with the pencil overlaid immediately to the left of the Name column, it will allow you to drill deeper into the details of that column.You can see a couple of examples below.



AppSheet date type
Key Column

In the left image above, you can see the column name, a Show? toggle switch from which you can choose to allow users to see the column or not, and a Type pull down menu. The date type can be long date format or a numeric year/month/date format. There are options for determining if the data is valid or not and auto-compute options, one of which we are using, an initial value of TODAY(). This inputs today’s date into the date field of any new work order but also allows the user to change it if needed.

In the right image above, there are some further options, the Key option being the one I want to quickly highlight. When you connect a Google Sheet, AppSheet will try to determine a key column for any table or sheet. This column should have a value in each row that uniquely identifies the row. This ensures that when data is added, deleted or changed, AppSheet ensures it picks the correct row for the action to occur. Common sense tells us that multiple work orders can occur on a single date and we could end up with many rows having the same key. In this case AppSheet is incorrect and we’ll turn the Key toggle switch off. The next column, Job number would make more sense as a key column instead.

Job number – this is a data type of Number which make sense. We’ve also now made this our key column.

Assigned to technician name – this is an Email type because we input the technician email address into the initial rows in our Google Sheet. Maybe we’ll need to change the column heading to email instead of name. Or maybe we can figure out a way to show the name when referencing the email address in a row. We’ll leave this for now.

Customer name – Name type which makes sense.

Customer address – Address type which makes sense.

Customer contact phone number – Text type. If we click the Type pull down menu we’ll find a Phone type. We’ll change it to this.

Reported issue – Text type. Sounds fine. If we dig deeper though, we can find a LongText option which we’ll choose instead to give us the ability to enter more details regarding the service issue. It essentially gives us a bigger text box.

Drive time start – Time data type. Makes perfect sense.

Site time start – Time data type. Ditto.

Site time end – Time data type. Ditto.

Drive time end – Time data type. Ditto.

Parts – Text data type. This is fine if you want the technicians to be manually typing in parts details on each work order. But it would be more efficient to have them be able to pick inventory parts from your Google Sheet. To do this we’ll review adding a Parts table and using a Ref column in our next post. For now we’ll keep this as Text.

Status – Text. This is ok but it would require having a technician type in a phrase to determine if a ticket is open or closed. It would be better to pick what AppSheet calls an Enum type. This will allow the app creator to enter phrases as options that can be picked from within the work order. We’ll change this to Enum and enter Open and Closed as options. Check out the images below.


AppSheet Enum data type
AppSheet Enum type choices

You can see we picked Enum from the pull down. In the right hand image, there is a Values field where we enter Open and Closed as our values. You can allow other values via a toggle switch but if you want to tie any events such as sending a work order completion form to your job status, it’s best we only allow certain values. We’ve picked Buttons as our Input mode at the bottom of the image.

We’ve gone through all the data types in our first app table. All other tables as you add them would follow the same process. We’ll do a short post next showing how to add a new table and reference it to another. Specifically to allow our technicians to pick inventory parts from a pull down menu in their service tickets.

Continue Reading How to choose the correct AppSheet data types

How to create an Appsheet app from a Google Sheet

  • Post Author:
  • Post Category:AppSheet

Originally I was going to cover AppSheet data types in this post. It dawned on me as I was mulling that over that if we are building an actual app in this series, it would be better to create the app first.

Last post, we set up our Google Sheets spreadsheet for GTA HVAC Services. Let’s pivot away from the nitty gritty of AppSheet data and instead set up our AppSheet app.

There are two good ways to do this. The first is to use a Google Sheets plug-in and the second is to create an AppSheet account and then connect the spreadsheet. Essentially you can go from sheet to app or app to sheet.

Let’s start with how to use the Google Sheets plug-in.

Creating your AppSheet app using the Google Sheets plug-in

The question we’ll try to answer here is how to utilize the Google Sheets plug-in to create your AppSheet app from your Google Sheets data.

If you take a look at your spreadsheet menus, you’ll see a menu labeled Add-ons.

Google Sheet Add-ons menu

If you click on the menu, there is a Get add-ons option.

Clicking on Get add-ons will open up a GSuite Marketplace window from which you can search for different add-ons that will work with Google Sheets. Type AppSheet in the search field at the top of the window and click enter.

Clicking enter will bring up a single search result looking like the image below.

Click the search result icon and install it. When you are asked by AppSheet for permission to start installing, click Continue.

Another window will pop up asking you to sign in with your Google login credentials. Use the same login you’ve used to create your Google Sheet. We’ll sign in with our GTA HVAC Services Google Sheets credentials. Once you do this, you’ll be notified that AppSheet wants to access your Google account and an explainer as to the privacy risks. Click Allow.

Once the installation is complete, a pop up window saying “AppSheet has been installed!” will appear. Click Done and close the GSuite Marketplace window that remains by clicking the X in the top right hand corner.

Now, if you click on the same Add-ons menu in Google Sheets we started with, you’ll see an option for AppSheet.
Click it and you’ll see a sub-menu with options labeled Launch and How To.
Click How To and you’ll see a short five point tutorial outlining how to build and AppSheet app.

Click Launch and AppSheet will automatically start the process of connecting your spreadsheet.

If there is an issue, it will alert you so it can be fixed. Because we only have headers and no data, AppSheet is asking us to enter at least 3 rows or data records before proceeding.

It does this so that it can attempt to automatically detect data types when setting up the app. It will save us time later on.

Once we’ve entered three rows of data, we run the add-on again. This time it brings us to a Google login page that asks us to pick a login to continue on to AppSheet. Same process as when we installed the add-on. I’ll pick my test Gmail I’m using for this series.

Once signed in, you’ll be directed to a new screen where you are asked for permission to allow AppSheet to “talk” to your Google account. Click Allow. Again same process as when we installed the add-on.

And that’s it, AppSheet has logged you in and created the framework of an application from your spreadsheet structure and data. From here you can adjust and tailor it to meet your needs.

Creating your AppSheet app directly from AppSheet

The question we’ll try to answer here is how to build your AppSheet app directly from AppSheet by connecting it to an existing Google Sheet.
This time we start directly from the AppSheet website. You’ll need to login. The easiest way is to click the Start for Free button at the top right hand corner of the website.
When you click the button, you’ll be taken to a page where you can click on a number of options for data providers. We’ll be picking the Google Sheets and Forms option.
You’ll be directed to the same Google login page as before. I’ll again pick my test Gmail I’m using for this series.
And again, you’ll have to give AppSheet permission to “talk” to your Google account. Click Allow.
This is where things get a bit different. You’ll now see a My Apps screen from with the AppSheet website. You’ll click the Make a new app icon under the Quick Start heading.
A Create a new app pop up will appear with 3 different options. We’ll choose the Start with your own data option.
The pop up will change to a Set up your new app display. For the App Name, we can enter GTA HVAC Services work order app. For category we can pick Field Service. Then click the Next step: choose your data button.
Your Google Drive contents will pop up. Pick your spreadsheet. In our case it will be the GTA HVAC Services work order app Google Sheet.
Give it a few seconds and voila, you’re app framework is set up!

Where do we go from here?

Now that we’ve created our app framework and set up our spreadsheet, we can get to work going through the process of building our work order application. We’ve figured out our process flows as well, so next we’ll start investigating the different data types we can use through AppSheet to best structure our application data!

Continue Reading How to create an Appsheet app from a Google Sheet

Part 2: Setting up your AppSheet spreadsheet

  • Post Author:
  • Post Category:AppSheet

In this post we’ll be setting up our AppSheet spreadsheet.

AppSheet supports Excel, Google Sheets and Smartsheet as spreadsheet plug ins. We’ll be using Google Sheets in this series.

You can think of the spreadsheet as your database and AppSheet as your interface.

When organizing your data into your Google Sheet, AppSheet recommends thinking about structure in terms of entities and their properties. For example, an entity could be a customer and properties of the customer could be name, address, contact person, etc.

In Part 1 of this series, we discussed how to figure out what types of information would be useful in an AppSheet app for our fictional company, GTA HVAC Services. What could we pull out as some entities that would be useful? Here’s my list:

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

Fleshing it out further, some useful properties of our entities:

For work orders:

  • Date
  • Job number
  • Assigned to technicians name
  • Customer name
  • Customer address
  • Customer contact name
  • Customer contact phone number
  • Reported issue
  • Status
  • Drive time start
  • Site time start
  • Site time end
  • Drive time end
  • Parts used

For parts:

  • Part number
  • Part description
  • Part barcode
  • Inventory quantity

For technicians:

  • Technician name
  • Technician location
  • Technician email

For customers:

  • Customer name
  • Customer address
  • Customer contact info

For purchase orders:

  • PO number
  • Order date
  • Vendor name
  • Requested by
  • Items ordered
  • Items received

The good thing about figuring out entities and their properties is that they can be changed relatively easily when developing your application. It’s easiest if you take a list like this and just get started. Things will always change and adapt over time, so don’t wait until you think your structure is perfect before starting.

Building the spreadsheet

What’s next?

Let’s build our initial spreadsheet. I’m assuming you know how to set up a Google account and use a Google Sheet.

When setting up the spreadsheet you can think of each “sheet” within your main spreadsheet as a table. In AppSheet each entity should have its own table. Let’s call our spreadsheet “GTA HVAC Services work order app”.

We’ll set up our sheets and label them as the 5 entities we’ve decided we currently need. You can change the color on your sheet labels if you helps you visually organize.

Next we need to plug in our properties. In AppSheet, each property should have its own column header in the appropriate sheet.

Take a look at the example image showing headers for the work order table and properties.

This is all we need to do to start! Your spreadsheet is set up.

There’s a few quick items that will help you save time later on.

  • Format your column header text in bold type. This will help AppSheet better detect your property names and types when it sets up the app from your spreadsheet.
  • For smaller applications, keep all of your headers in the default “Sheet 1”. AppSheet will automatically detect your data types from your headers. When using multiple sheets, subsequent sheets, ie. “Sheet 2,” will require a double check to ensure data types are chosen correctly by AppSheet. We’ll go over what these data types are in the next lesson.
  • If you have 5 or 10 rows of data to plug into each of your tables, it will assist AppSheet in determining the data types of your column headers.

Next we’ll dig into how to ensure your entities and properties have have the correct data types!

Continue Reading Part 2: Setting up your AppSheet spreadsheet

Part 1: Business process flow in an AppSheet app

  • Post Author:
  • Post Category:AppSheet

My goal with this upcoming series of posts is to go through the process of creating an AppSheet application to help streamline a small service business. 

Bear with me through Part 1. It’s a bit long and wordy. The purpose, though, is to show you how to get down on paper (or screen) an idea of how your business processes will flow within your AppSheet app.

Start by figuring out your inefficiencies

We’ll create an imaginary HVAC service business, we can call it GTA HVAC Services. Let’s say it services the greater Toronto area in Canada, has 10 mobile technicians, each with their own company supplied service vehicle and parts inventory. Office staff would include a dispatcher/coordinator responsible for managing day-to-day of the field technicians and a shipper/receiver responsible for managing the overall company parts inventory.

GTA HVAC Services currently utilizes paper based work orders for all technicians. Any incoming calls to dispatch are written on a paper-based work order, physically given to the appropriate technician, filled out upon completion of a call and then physically given back to the dispatcher. GTA HVAC Services, from this point on known as GTA, has recognized several inefficiencies with this process.

  1. The dispatcher will often have to “chase” technicians to get work orders back in a timely fashion with good information.
  2. Getting mid-day work orders into the technician’s hands requires non-billable drive time back to the office.
  3. Different technicians will often call the same parts different names leading to confusion when allocating inventory to jobs and invoicing parts.

GTA has decided to use the AppSheet platform to create and test a work order application with the objective of improving these three inefficiencies.

Thinking about information/workflows in an AppSheet app

So what’s this look like? How do we get started? Let’s break it down into a few initial steps.

  1. Determine what information is important.
  2. Determine the type of information each employee needs to see.
  3. Determine what the work flow looks like for each employee.

OK, so let’s start with the first and second steps as they go hand in hand. 

The dispatcher should ideally have an overview of everything that’s happening. All open and closed work orders, the location of each of the 10 mobile technicians, the parts inventory on each of their vehicles and shop parts inventory. To have a real time or even close to real time overview of these items would go a long to improving our big three inefficiencies.

The shipper/receiver should have a view of the current overall inventory, including how it’s dispersed between shop and different service vehicles. They should also have a view of any open purchase orders. A formalized, barcode based process would be important to improving this part of the business and allowing it to scale.

Each mobile technician should be able to see their open and closed work orders. GTA believes their main focus should be to execute on site work, other items within the application could become a distraction. Info input from the dispatcher is important. Customer address accuracy and a good description of the dispatched issue go along way to setting up the technician for success. A tie-in to the shipping/receiving barcode system would reduce errors allocated inventory.

So this gives us somewhere to start. Things grow and change over time of course, but we now have a simple outline for what type of information each employee type needs.

So the third step would be to figure out how the information we’ve identified as being important in steps one and two can flow efficiently. 

I know the wisdom of our time says organizational hierarchies are passe. When thinking about workflows within AppSheet they can still be helpful. We can imagine our dispatcher and our shipper/receiver as the foundation of our “pyramid” and our ten technicians above them. Those at the base of the pyramid, when operating smoothly, setup those above to succeed. And, as you know, a weak crumbly base will bring the top of the pyramid crashing down. We don’t want that. So let’s just brainstorm a bit.

How can we ensure the base is strong? 

The dispatcher needs an easy way to enter a work order in our application and an easy way to decide which technician to send the order to. This should take very little time or thought on the part of the dispatcher. If a job is to be scheduled requiring inventory parts or special order parts, the dispatcher should be able to tell from the application if the parts are available and where they are. A basic workflow then should have two prongs for the dispatcher. An “emergency” call workflow that allows the dispatcher to easily enter a work order and dispatch it to a chosen technician. There should be a reverse workflow that allows the technician to send the completed work order back to the dispatcher for close out and invoicing. There should also be a “scheduled, parts required” call workflow that allows the dispatcher to see when parts are available for an open ticket. Again, there should be a reverse workflow that allows the technician to send the completed work order back to the dispatcher for close out and invoicing.

Let’s move to our shipper/receiver. The position requires an easy way to enter purchase orders for both inventory and job specific orders. It also requires an easy way to receive the orders. Both of these processes have to be tightly integrated with dispatcher processes for visibility. The shipper/receiver also needs to have a near real-time view of parts inventory in any location. Ideally this would flow such that when a technician’s vehicle is low on a required part, the shipper/receiver is alerted so the parts can be pulled for pick up by the technician at the beginning of the next work day. We’ll leave it at this for now. If we can get this to work out, we’ll be way ahead.

Probably most important, is the technician’s workflow. As GTA bills out its technicians on a time and materials basis, this workflow must be optimized. It’s critical the work order arrives on the technicians device with the proper information. Customer addressing and contact information need to be proper. There needs to be a notification system in place so the work order isn’t missed. The work order needs to be intuitive and easy to fill out. It can’t be frustrating to use. Having access to information about the equipment on site and the ability to send equipment info back to head office would also be beneficial. This is more of an information requirement but ties into the flow in terms of how the technician prepares for the call. Once the billable time for a call has ended, a single button push or screen tap should enable the call to be closed and trigger a work order to be sent to the dispatcher.

Where do we go from here?

This is all a bit dense and messy but it’s important before starting to build your AppSheet app that you have an idea of where you are headed. Putting it all on paper is a good way to process your thoughts. It doesn’t have to be perfect, it just needs to be a clear starting point once complete. It will save you significant time as you build. And, as we all know, time is money.

We’ve fleshed out what we think we need at this point. Moving forward, the series will be more of a how-to structure. We’ll start with how to build a data framework for your application!

Continue Reading Part 1: Business process flow in an AppSheet app

Improve your business processes with AppSheet

  • Post Author:
  • Post Category:AppSheet

There’s no doubt about it, service businesses are hard to do well. One of the biggest struggles smaller service oriented companies often have is maintaining solid internal processes with tight human resource capacity. In tandem with chasing revenue generating business. Double edged sword.

The service business struggle

My day job reached a point in the past year or so where the workload exceeded the capacity of our team to manage well. It became a giant snowball rolling down a hill, compound interest on a loan putting us in the red instead of the black – internal processes fell apart, leading to poor field execution and angry customers. Productive time sourcing further work was now consumed by reacting to the inevitable fires that occur from such chaos.

This can only happen for a short time before a business or business unit implodes and we knew we had to find a way to improve. For this reason, we searched high and low for a solution that would allow us to improve our internal processes while keeping our staffing number the same. We still had hopes of becoming a “well oiled machine”. 

A possible service business solution

After some searching, we decided to try a platform by the name of Appsheet. The platform allows a useful business process-oriented application to be built quickly. Requiring little to no coding ability (although logical thinking skills and spreadsheet expertise help). Cost wise, for small businesses, it hands down beat any of the other platforms we investigated.

Our initial goal was simply to take our paper based work orders which we used across the country and create an application that would digitize them. Enabling our dispatcher to send a work order to a service technician easily and instantly. And having the same tech send it back to our dispatcher, immediately after the call was complete.

As we worked through this project, we found a big benefit of Appsheet is that you can prototype and test an application with a few users for free. We quickly built a test work order application with Google Sheets as a back end and tested it with several users. It worked well and enabled us to trim down the average invoicing time from time of dispatch to time of final invoice to client from over a month to a couple of days. At no monetary cost and at most a couple of evenings of learning. Essentially an immediate payoff. 

The next steps

Since then, we’ve rolled out the application to all of our employed technicians and major subcontractors. We’ve tweaked it as our needs have dictated and time has permitted. We’re currently getting into the final stages of developing an upgraded work order app that ties into our parts inventory in all of our service locations. The work order for each technician is integrated with their parts inventory which is integrated into our main distribution hub parts inventory. We can now be automatically notified when a part is low anywhere in the country allowing us proper inventory control as well as the ability to respond to customer needs in a timelier manner. We expect to roll this out in the next couple of months. We’ll take the next process that needs work at that point and likely develop another application to fine tune it!

How can you do something similar

Our second generation work order application has been challenging to create. It’s been a challenge to think through how it should work in a distributed business and pull that abstract notion into an application. Tough to figure out how to structure the logic to tie inventory numbers and work order fields together. It’s been a challenge to tie everything together in a way that is customized for every user and field friendly. It took a long time for us to figure everything out but the Appsheet community and the help section of Google Sheets were invaluable in expediting what would have been an even longer process. 

As we finish the final touches, I thought it would be useful to document the process of building a similar application for those in the Appsheet community just starting down the path of a similar project. I won’t use our exact application but I’ll break down the process of developing the “guts” of something similar so others can have an idea of how powerful the platform can be.

I’ll break everything down into a series of posts, look for the first post tomorrow!

Continue Reading Improve your business processes with AppSheet