This Week in Technology [WK 10]

This week in technology, we developed a new online, paperless, workflow for behavior referrals from the Transportation Department. We call them TBRs, or Transportation Behavior Referrals.

Previously, school bus drivers would fill out a form by hand, and turn in to the Transportation Supervisor. In looking at the form, we suspected that it originally had a different workflow attached to it, since long gone.


The form was designed to function in quadruple – the white copy was the return copy, the yellow for parents, pink for principals, and goldenrod reserved for Transportation’s copy.

Needless to say, this was not happening now, as forms were used single-sheet (to reserve paper), and with little to no feedback to anyone involved in the process, especially the reporting driver. This produced a pain point for the drivers, as they felt ignored in the process. The outcome of the referral process was compartmented and separated, and seemed less than ideal.

Time for improvement!

We spent some time brainstorming, asking questions (the 5 Whys of root cause analysis, etc.) and learning more about the process, and the desired outcome.

We heard from the drivers loud and clear that they were disappointed in not being apart of the notification cycle as to what happens with the behavior referral. They wanted to know what, if anything, was being done to address the reported behavior on their bus.

We also asked a few principals what they thought, and what would expedite the matter from their side. They shared a cumbersome process involving scanning pieces of paper, emailing left and right, and overall spending too much time (and therefore often not sending feedback backwards to the Transportation team). Lots of opportunity for improvement!

Here’s a flowchart of the process…

TBR Workflow

Step 1 – Recreate the Form using Google Forms

We built a new form using Google Forms (naturally!), and used functions like automatically capturing the submitting person’s email, as well as identifying data like what building the student is in, grade level, etc. to better be able to analyse the data later.

TBR Initial Form

Also, by collecting the building, we are able to send notifications to the appropriate principal later in the workflow using something like this:


Well. We actually created 3 forms, and using the pre-filled link functionality within Google Forms, combined with the data from the first form, kept all the workflow for the end user in a Form, as opposed to having to engage with a spreadsheet.

Here’s what the forms do…

Form 1 takes the initial report, notifies the driver that the form as been submitted and is under review, builds a pre-filled link for Form 2, and notifies the Transportation Supervisor that a TBR (Transportation Behavior Referral) has been submitted, and provides the link to Form 2 (with pre-filled data from Form 1).

Form 2 allows the supervisor to take action, provide comments, and depending on if a principal needs to become involved, repeats this workflow, and communicates backwards to the driver as to status of the TBR, and forwards to principal, again with pre-filled link from Form 2, for them to take action on.

The pre-filled link is a bit complex. It involves creating the future form (so you have to think ahead a bit in planning this), like Form 2, and then creating a pre-filled link, and from that, parse out each question, and replace it with data from the spreadsheet. Connecting it simply using =CONCATENATE works best for me, but there are other solutions as well.

Here’s the initial pre-filled link from one of the forms (with a few characters left out so it’s a dead form…). Notice the &entry.123456 tags used – they represent the actual form question value, so it’s just a matter of replacing that.'s+First+Name&entry.1066398853=Student's+Grade+Level&entry.971139394=Student's+School+Building&entry.1918527762=School+Bus+%23&entry.76414843=Reporting+Bus+Driver+Email&entry.788379148=2018-02-26&entry.2022629268=Main+Reason(s)+for+Referral&entry.1861937173=Driver's+Comments&entry.1435616669&entry.500332380=Reviewed+and+Noted

Here’s what a concatenated statement looks like (a sample), where C2, D2, and so on represents data from cells as submitted by Form 1:


Form 3 is the final form, with data from Form 1 and Form 2 (Driver’s report, and Supervisor’s actions and comments, respectively) and asks a principal to indicate outcome of the TBR, comments, etc.

TBR Form 3

Once Form 3 is completed, the feedback cycle repeats, and sends the outcome backwards to the submitting driver, and supervisor. The TBR loop is now complete.

Sounds complicated, I know. But worth the time to build, and automate!

Step 2- Build an Automatic Communication Loop using FormMule

In addition to building a form cycle using three forms with pre-filled link functionality, we wanted to make sure each user (driver, supervisor, principal) was properly informed along the way. For example, a driver should know when their TBR is under review, and whether or not a principal has seen it, and what, if any, action was taken.

*It’s worth noting here, that drivers and supervisors may not be privy to some of the details of any potential student discipline, but communicating that action had been taken was the main concern in the first place.

We used FormMule, a Google Spreadsheet Add-On to create various workflows using, triggered or not, data from the spreadsheet.

Basically, each time the form is submitted, a workflow triggers, and sends the appropriate data to each user. Initially, the driver receives confirmation that the TBR is under review by the supervisor, and so on.

Email, constructed with appropriate subject lines, makes it easy to filter and sort, and find later, so we naturally named each TBR notice something like this (using FormMule tags as well):

Email Subject:
TBR | Update on Bus <<School Bus #>> | <<Student's First Name>> <<Student's Last Name>>

Email Body:
TBR Review for <<Student's First Name>> <<Student's Last Name>> was performed on <<Timestamp>> by <<Email Address>> with the following outcomes:

Transportation Supervisor's Action Taken:
<<Action Taken>>

Transportation Supervisor's Comments:
<<Transportation Supervisor's Comments>>

Recommend for Principal Review & Follow Up:
<<Recommend for Principal Review & Follow Up>>

If yes, the referral has moved on to the appropriate building principal, and you will hear back when they make a final decision. Otherwise, the matter is now closed.

The email triggered to the principal, adds something like this:

You may view the full details, including the Driver's report, the Supervisors review & comments, and report your action, by opening the full form.

<a href="<<PRE-FILL LINK>>">Click here to open and review the TBR.</a>

The PRE-FILL LINK above is the concatenated link using the pre-fill link to form function from earlier…

Step 3 – Create a Permanent Record for the Reported Behavior

Of course, there was demand for a permanent file / document that could be attached, and printed (grrr!), to the student’s record.

So we tapped into another Add-On in Sheets called Autocrat, that will automatically generate a PDF once all workflows have run, and send back to principals for record keeping.

More on Autocrat another time, as we’re using it for other workflows around the district, and it deserves its own story.

Step 4 – Provide Online Access to Drivers (to fill out form)

The drivers in our Transportation department needed a place to fill out the TBR, if it’s no longer on paper, so we provisioned and deployed 4 chromeboxes for their exclusive use in the Driver room (picture only shows 2 of the 4).

driver chromeboxes

Additionally, we manage the drivers in an OU in our domain, and so we automatically force-loaded the URLs (in Device Management > Chrome > User Settings > Startup) for the TBR to their chrome profiles, which means each time they login, Gmail, a blank TBR, and other applicable URLs will load in the browser – all decreasing the time it takes to get to the TBR, email, etc.


Admin_console on startup

Step 5 – Train and Follow Up

Once we train all the drivers (next week!), we will go into review / check phase of the Deming Cycle of Continuous Improvement, and collect feedback from all users – drivers, supervisors, and principals. If if something isn’t’ working, we’ll fix it!

The TBR will go into effect starting on Monday March 12, 2018.

Ever improving!

– Andreas

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.