Travel form: dIGITIZING A WORK FLOW

The basic problem of paper is that it has to move from hand to hand. In this process, what we have done is created a new system that removes the paper option. Here was the old work flow.

When someone needed to travel they were required to fill out a form. This form had them place all the details of travel on there and it was sent through the secretary to the admiistrator. The administrator would then give it back to the secretary to forward it to the right people. 

I have to give credit where credit is due and thank Gretta Rogne who presented at the last TXGOO with her work on using Google Forms for administration. I used her approach for most of the first part to create this, but the generation of the documents and the use of the formulas that I used are mine. 

The solution: Google Apps

This presents a unique challenge to me, as the process has several steps. The business office cannot just use a spreadsheet and needs a form. There is also the need for approval. There is also the need for calculated fields. These field require that the form values enter are added together correctly, or that the number of nights in a hotel is multiplied by the rate. This was all being donw by hand before. 

We start with a google form. Notice that the first page has several options depending on person that needs to either request or approve the option.  

The APPS

We start with the app on the form that I use to autofill in the teachers and the email addresses. This is called FormRanger, and can be added through the add-on's page. This is pretty easy to set up and probably the easiest part of the entire piece.

FormRanger

As you can see from the picture to the side the form ranger menu allows you to pick which field that you want to use and then use another sheet to populate the information. Saving you time. These update as you update the sheet.

Some tips:
1. You can use formulas to limit options on your pull down.
2. You have to have the spreadsheet already set up and you can use the same sheet for different lists.
3. It pulls in the column not the entire sheet.

Next I used on the spreadsheet the Add on called Copydown. This is a great add on that allows you the functionality of creating formulas in a form spreadsheet and copy them down to other rows as people fill in your form. 

CopyDown

So as you can see from the screenshot. I had several formulas. For those that are not up on the excel formulas I will talk about 1 formula that I used to help have the system make a decision. I wanted the system to autogenerate an email when the date and time passed so that the requestor could request reimbursement. The easiest way to do this is with a formula. I will get how you add on to the email when I talk about the Form Mule add on. 

Formula used:

=if(and(now()>F2,AF2="Yes"),1,0)



What this means is that if the date now is greater then when they turned in the form, and it has been approved then it will show a 1 otherwise it will show a 0. As long as the field reads a 0 then it will not do anything. Once it changes to a 1 the Form Mule add on will take over and send out an email with the link to enter their actual expenses. 


This does not seem to have a limit for the number of formulas that it can copy down. This has so many uses, and I am really excited about it. 

The screen shot to the left shows that I am calculating their total meal expenses and then the student meal expenses with two differen formulas. 

FORM MULE: THE HEAVY LIFTER

This is probably one of the most versatile add ons that I have seen. It is really good, and really helps to simplify workflows. Students or parents could go to a form and request information that would then be emailed to the person at school. This is the basis of the TXGOO presentation that I went to, without all of the extra formulas that I used to help spruce it up.

Click to set custom HTML

The key to this is the log the edit URL for each submission. This allows you to go in and edit the response. This way you can have a administrator approve it without all the hassle. Notice it also says that you can use the copydown add on to add this information.

Step 2

This part is important. You title the email that you want to send. For my case I had 6 that I wanted to use. The last one is still being build. The six actions that I wanted to complete were

1. The requestor has requested a district vehicle, as their are extra steps that have to be taken for this to happen.
2. The requestor has a request that has been approved. (NOT NULL here means that the field called "Do you Approve this request" is filled.
3. The administrator needs to approve a request.
4. The travel date has passed and the requestor needs to fill out the form for reimbursement. (This is where the copy down formula comes in that we covered earlier on.
5. The information has been entered by the requestor and it needs to be approved
6. The document has been updated and needs attention (this is a work in progress)

Step 3

This is the part where I set up the template for the emails. I have included the more complicated one below and show where the EDIT Link comes in handy. 

The fields are on the right the grey buttons. All you have to do is drag and drop. All columns in the first row are here so you can even take in the calculated values into the email. The form edit is important. This option did not come up the first few times. I had to enter in a dummy response to get the option to appear. 

After I build all of the templates it was time for the last part, the autocrat add on. 

Autocrat

This add on is really great for anythng that you need to automate completely. Progress reports, scheduling, whatever you need it is there. I used it to generate two documents. One functions as a sort of a receipt, and the other functions as a document that will go to the business office. 

Merging ahead

The two documents below are for different reasons. Setting up a Merge job requires a few things. The first is a document that you want to merge. I created one with MERGE TAGS. These are tags that help the program to register the fact that it can be replaced. This is the same logic as Form Mule used in the email. <<Name>> would replace with the name if my field from my form was called Name. A few tips

1. You can do more than one document per line.
2. You can have the document autogenerate with a field.
3. The link to the document is stored in the spreadsheet.
4. You control the level of access. 

The second point is really important because it allows for you to create a formula that will then create the document. I created another formula to wait until the information had been entered for reimbursement before it generated the request. This is up to you. These documents feed back into Form Mule to help send emails out when documents are ready. This means that you can send out the link to the document. All of these points are really helpful, and took a little while to map out. 

Field Mapping

This process seen above helps to match the correct fields within the document. It will only pick out the fields that are formatted as a merge field. Merge settings is where you can set the conditional creation of the documents. 

Once all of this has been done it will email and share the documents for you. 

A lengthy project but well worth the effort.