Don't telework with spreadsheets: build a request tracker using Quickbase

In another article (which you may want to read first if this one seems too technical), I made a case for why you shouldn't rely on spreadsheets to manage support your new teleworking regime. In this article, I will walk you through a very high level example of moving a spreadsheet based tracker to a more robust online platform.

In this case, I will show you how to do that with Quickbase but there are many other products out there that will do broadly the same thing.

The use case here is a customer request tracker for a small to medium sized organization or department of a larger organization. It could be something like a customer calling for service on some equipment or a business user making a request of their internal support staff. It's completely made up but is a good representative example of the kind of thing that organizations have had to put in place very rapidly over the last few weeks.

The idea is that customers have service requests that come in and need to go through a certain workflow. Work is assigned to different staff who handle different aspects of the workflow.

When a request comes in, the call center worker gets details. That request is then passed on to a supervisor who assigns it to either one or two staff members depending on the request type. This is because one customer request can require more than one assigned task to complete.

Once the tasks related to a request are completed, the request is considered complete and the customer is notified.

That has been the main challenge with the spreadsheet as it's difficult to keep track of when multiple tasks per request and it's also not easy for a technician to be able to see what they are currently signed up for.

So far, so simple. Very basic kind of workflow:

1. Request comes in

2. Request is assigned by supervisor to one or more technicians

3. Request is complete when the assigned tasks are completedSpCurr

Current State: spreadsheet tracking

Here's the spreadsheet with three example customer requests. one results in one assigned task, the other to two different assigned tasks. I didn't bother with some of the details fields like notes etc just to avoid cluttering things up.

There is an overall status for the request itself as well as one for each of the potential tasks. There are some fields tracking customer details and others tracking the request details.

Right away, we can see how this spreadsheet can get a little challenging to use. First off, there is the requirement to type in the customer information instead of choosing it from a list. That can be fixed in excel but it's a little kludgy.

Another thing is that while the spreadsheet will suffice for handling the requests, reporting on the requests after the fact will be a little more challenging, especially if there are data entry errors as there inevitably will be.

In addition, if there are requests that need more than two tasks, more columns will need to be added which could be unwieldy for more and more tasks. Also by making this a spreadsheet, especially one that is a shared document on OneDrive or something similar, you are limited to what can be done via a spreadsheet interface.

Filtering, etc is easy but any kind of data validation or dashboarding is not available. And forget about setting up permissions so certain people can only see certain data. So something as simple as seeing all my assigned tasks is not very easy to do when multiple people are working on the same document.

Future State: online tracking database

So let's see how we could enhance this by bringing it into Quickbase. Note, I am not going to give detailed, step by step instructions as this is not a tutorial. But my goal is to provide enough detail that you can take it from there as well as to demonstrate the power of using a database instead of a spreadsheet.

Here's a summary of the tasks (assumes you already have a Quickbase account and blank Quickbase app created*)

1. Decide on basic data model

2. Create table in Quickbase

3. Prepare spreadsheet and load into the table

4. Convert certain fields into parent tables

5. Transform your business

Step 1 - Decide on basic data model

What we need to do is create a table in Quickbase and import the spreadsheet. But in order to do that most effectively we need to make some initial design decisions.

Here's where we will take a little about the concept of a data model. If you understand what this is, feel free to skip to step 2

A data model is exactly what it sounds like, it's a version (ie "model") of your business translated into data tables and relationships between those tables. For example, if you have a school you may have a students table, a teacher table, a course table, a class table, and a schedule table. Each teacher may have one or more courses they teach. Each course would have one or more class sessions, each class would have one or more students and each student would have one or more classes.

If you were to do this in a spreadsheet you would have each one of these entities as a set of columns in your sheet. Because many teachers can teach many classes which have many students, you would have a lot of repeated data in those columns so that updating teacher data would be a nightmare.

Choosing the right data model, especially for a simple case like this example, is not difficult but it can be a little tricky in terms of where you start. For this example, it's kind of obvious. Each customer can have one or more requests which can have one or more tasks. Hence:

Step 2 - Create table in Quickbase

This is important to know because in Quickbase we create the lowest level table in our data model first and then extract out the other fields into their own tables. With other platforms, you will still need to extract the data out into separate tables but you will likely need to do that outside the application and then load each table separately.

But back to Quickbase, we need to create this first table. Basically each column in the spreadsheet becomes a field in the table.

Here's the tricky bit. While it may seem that you are tracking requests, looking at the data model the lowest entity in your table is actually tasks. Even though requests are what triggers your process and each row in your sheet is a particular request, what you are really tracking is the individual tasks that need to be done to fulfill them. So the spreadsheet is actually a list of tasks even if that list is actually being stored in columns as well as rows.

But hold on. That last statement is actually important. While you could just load the spreadsheet as is into the table that would leave your tasks, your actual lowest level table, as columns. So unfortunately you have to convert them to rows.

This means that your task table looks like this:

Notice no separate "task 1" or "task 2" fields because now your tasks will be in a separate table that will have either one or two rows related back to each row in the Requests table.

The reason why we create the table first and then prep the data is so that we know what the target is for how we need to reformat the data in the spreadsheet.

Step 3 - Prepare spreadsheet and load into table

In our case, it's not too bad. Simply take all the requests which have two tasks and repeat them, moving the task assignee and status into the same column:

Notice that the request rows that had only 1 task are unchanged. For those that had two tasks, we now have two corresponding rows. This makes the "hidden" relationship between requests and tasks more clear. Now it's obvious that we are tracking tasks and not requests.

This is the hardest part of the exercise and if you have a more complex spreadsheet, it will take you the longest time. It's pretty standard in working with any kind of data engineering or analytics that prepping the data is 90% of the work.

An alternative is to just start fresh in the new system: use the spreadsheet as a guide but don't actually load any old data into the new system. I generally don't like to do that because the old data is information on your business that you may want to look at for trend analysis etc.

But sometimes the juice is not worth the squeeze, especially if you have really poor data quality in the spreadsheets.

Step 4 - Convert certain fields into parent tables

Here is where the decision of what table is at the bottom of your hierarchy becomes important, at least with Quickbase. Recall our data model from above: each customer could have one or more requests which could have one or more tasks.

One problem with the spreadsheet is that the data for these "parent" tables (the Customer table being the "parent" to the Requests table which is the "parent" to the Tasks table) is repeated.

Having a real database means that we can just pick the customer to add a request to rather than typing it all in each time. We can also add additional attributes to the customer (address, contact information, customer type, etc) without having to repeat it endlessly across a bunch of rows.

As mentioned above, you will need to extract out the data from these parent tables in order to load them into the other tables. With other platforms, you will likely have to manually create the Request and Customer tables, extract those bits of data out from the spreadsheet, create some kind of request id and customer id fields to tie the tables together, and then load those tables separately.

Luckily for us, Quickbase makes this all easy to do by providing a way to create the parent tables and extract the relevant data columns into those tables all in one step. You simply select the option to create a "New table based on this field..."

First we extract out the request table since that is the next level up. The key is to realize that since there are tables above this request table, when we pull this out we need to pull out all the fields that are above tasks including these higher level tables, ie the customer columns which for our simple example is really just one column "Customer Name". Once we extract the requests into their own table we then extract the customer table out of that.

Once we've extracted the requests into their own requests table, we extract the customer information (ie just the name in this case) into it's own table. At this point we have three tables, Customers, Requests, and Tasks.

Step 5 - Transform your business

To a certain extent all we have done now is just create a better organized spreadsheet. But now is where we can bring in some of the power of using an actual database. we can add roles so that when our technicians log in, they will only be able to see the tasks that are assigned to them:

We can also create a dashboard so that they can organize their work.

We can add a formula field for Request status that gets set to "complete" when all the attached tasks are completed:

We can add a postal address and google map for customers:

We can create an automatic email notification to the customer when their request is completed:

Or many, many more things that will help speed up our workflow and get us the data we need to make decisions based on facts.

Quickbase is a very versatile tool and makes this kind of thing very easy to do. It's not perfect but it's definitely my go to tool for any kind of workflow or other data tracking tool and I'm always happy to chat about how it can help streamline your business. In a followup article I will show you how you can set up a virtual call center by integrating AWS connect and Quickbase.

But even if you don't use Quickbase, moving to a more formal database like this will help to you to leverage your data to move from teleworking as a necessity to teleworking as a competitive advantage.