Setting up a digital application form

About this video

CISV\’s Philly Chapter moved from paper applications sent in the mail to google forms accessed from the website, philly.cisvusa.org.

1. The Applicant wants to know – \”Was my application received?\”
2. The Program chair wants to know – \”What applications have we received?\”
3. People providing references want to know – \”Did someone get my referral?\”
4. Applicants want to know – \”Did my references follow through?\”

I made a few longer videos about this and ended up with a six minute video that outlines the process. This process is a little more involved than I had intended it to be. 

I had made the application forms using google forms and put them on the website. Getting this set up was the easy part. 

Communicating the receipt of the data, formatting the results, and automatically sending confirmations to everyone concerned was a bit harder.  Also, while programs share some of the same forms, I needed to set up unique forms for every program. 

1. Forms
2. Sheets
3. Doc Templates
4. Automation

A checklist helps

There were so many forms, sheets, docs and integrations, that I made a google sheet checklist.

Here\’s why the video is important

We all make assumptions, and things are always easier if someone else does them.  If I don\’t explain my approach to setting this up, then next year the CISV Chapter will have to reinvent the process rather than to improve on a process. It could be me updating the system, and I\’ll want to review the videos to see what I was thinking.

Sharing the process with others

People in an organization change, and next year there will be new volunteers.  These volunteers might wonder about the application process.  Or, I may be gone and someone else will have to deal with the website and the application process.  The video can save a lot of time.

Why you should watch it.

We all have different levels of technical awareness.  In the past, I\’ve set up systems that made my life easier but were unpleasant for others to use. They preferred paper.

You may like paper.  You may also see the value in this and how much effort it saves.  Watching this video will save you time if you\’re involved with CISV.

Implementing the system

So, today is November 28.  A lot of the system works.  My first hope is people in our CISV chapter will watch the video in the next two days.  So, if they have a question, I can answer it.  The system will be tweaked and fully functional by then.

Following up three weeks later

Some people will like the automated system.  Others will criticize its imperfections before and won\’t really look at the overall improvements. 

It is not enough to set up a form and collect data in a spreadsheet because people can\’t use that data.  It is not enough to collect information in a system where multiple people need to be notified that a form has been filled in. It\’s not enough to create a template to present the data, because there will be multiple users of the results.

Here are specific issues I addressed

Gender

Previous forms has space for mother/father/custodian.  Families can be mother/father, mother/mother, father/father, or even mother/mother/father, etc. They can be single parent.

On some forms, I addressed this as \”parent 1\” and \”parent 2\”.  On some forms, I addressed this as mother/custodian, father/custodian.  While filling out a form for another organization, I saw that they used parent 1 and parent 2.

Conflicting field names

If you ask the same question to two different parents, then the resulting spreadsheet has duplicate headers.  This creates problems with form automation later on.  

I addressed this by changing the question to include (parent 1) and (parent 2) at the end.

Some questions took thought (time)

You might sit down to fill out a form, only to pause where the form requires a short essay.  It\’s more than you want to do in one sitting.  And then you lose the information that you\’ve already filled in. Ugh!

I addressed this by creating a link to a google doc that summarized the questions asked.  More can be done for this.  The doc should be formatted nicely, with open ended questions highlighted.

\”Is it my name or my child\’s?\”

A parent, not the child, might be filling out the Youth Delegate Application Form.  I didn\’t word the questions specifically enough. This resulted in a parent filling in her own name instead of her child\’s.

I haven\’t addressed this yet.  I also noticed some redundancy for things like phone numbers.  The form needs to be tested and, well, maybe this year\’s the test.

Some fields should be split into two

Name should be two fields, \”first name\” and \”last name\”. This way, you can address an automated email to the person.  For example: \”Dear Steve\” vs \”Dear Steve Spindler\”.

Addresses should be broken into street address and \”city, state, zip\”.  People sometimes just wrote their street address and assumed that they didn\’t need to add the \”city, state, zip\”.

Really, only one person should be able to edit the survey form

When I gave program managers access to the forms or spreadsheets, changes were made that broke the automations.  I should just have provided the final pdfs.

Photo sizes that people upload need consistency

People uploaded multiple sizes of  photos of their kids.  As a result, the photos displayed weird in merged documents. I couldn\’t just resize the photo automatically.

I need to provide guidance on the form for photo uploads and restrict the image type to .jpg.  We received uploads as jpg, pdf, HEIC, and a html link.  Ugh…

So, I downloaded the photos in bulk, edited each one so that it was 300 px wide, and re-uploaded them to the google drive folder with a modified name. 

Modifying the Google sheet so that photos display on the merged google doc

Two changes were necessary. 

1. Use the correct Unique ID for the photo in the spreadsheet
2. Change the URL in the spreadsheet from
   Old – https://drive.google.com/open?id=
   To 
   https://drive.google.com/uc?export=view&id=

The first change is done by finding the file on the google drive, sharing it, and copying the ID

Then copy the ID, which is to the right of the equals sign:

The second change is done on the google sheet, where you make sure the link is correct.  So, a link that was originally this in the google sheet or drive:  

https://drive.google.com/open?id=1ZlM6wTnSASr4VH20joEuPl7y0LnbUCgj . 

Would now be 

https://drive.google.com/uc?export=view&id=1ZlM6wTnSASr4VH20joEuPl7y0LnbUCgj 

The data in the sheet is accessed through AutoCrat with the following setting of Image.  I leave the image URL, width and height to the default.

If the folder containing the image files is not set to share publicly, then the image merge process won\’t work.  

Layout notes

• I placed the photo on the last page so that it wouldn\’t interfere with other information. 
• I placed the applicant\’s name in the footer. You never know how many pages an application will be. 
• I added a CISV logo for branding and created a descriptive header for each form. 
• The data was formated with an indent, and the questions are in bold. 

These decisions make the form easy to review.

Thinking about forms for next year

Important things I would change:

1. Use a single YDAF form, and save that form to folders based on the program. Program chairs could access the files from folders that they have access to.
2. Use a single reference form, and save that form to folders based on the program.
3. Require people to upload a photo that is 600 pixels wide and 72dpi.  This would go on the last page of the application document.
4. Compile phone and address information for all applicants of all programs onto a single page. This would be done by merging google sheets before running AutoCrat
5. There needs to be some validation that the document has been filled in correctly.  And I may need to create a field that converts the image url string so that I don\’t have to do this manually.
6. Use a flow chart checklist earlier in the process.

I\’ve spent way too much time on this, but I do enjoy doing it.