This is a fairly technical solution to a fairly limited use-challenge.  But it’s not a scenario that is completely unheard of.  It’s something that we’ve faced here on multiple occasions, so I decided to finally take a swing at a somewhat user-friendly and somewhat elegant solution.

Here’s the challenge.  There are lots of instances–applications for fellowships, submissions for awards, proposals for a conference or journal, things like that, where we have multiple submissions that have to be reviewed by multiple reviewers.  Often the applications include basic contact or demographic data, categories or selections from a limited list, and lines or paragraphs of text, as well as sometimes much longer, formatted, statements or resumés. Then those applications have to be circulated to a full committee, each member of which has to review all the material, assign a numerical rating, and usually have written comments, too.

We used to do all this with stacks of paper, asking candidates to submit five (or more) copies of their whole packages, and then we’d have big fat envelopes and boxes of paper being shlepped around the city (and later shredded).  Those were fairly easy to read and review, but a giant pain to manage and store (or destroy), and hardly environmentally friendly, if that matters.

So a couple of years ago we turned to various online form solutions–drupal webforms, google forms, and so forth.  This was an improvement in some ways–each of them led to a spreadsheet that could be more easily shared (through google docs, usually).  But when the applications to be reviewed were complicated, the spreadsheets were immense and very difficult to navigate (especially in google docs).  And when there were attached PDFs (essays or statements or resumés or whatever), those were included in the spreadsheet as a link, meaning another click, another window, or a download, and some confusion about which went where.

There was also the constant issue (everyone who works with google docs with large numbers of people deals with this, I think) of determining exactly which email address had been invited to view the doc.  Was that your personal gmail address? It was? Which one did you log in with? Are they linked? You can see but not edit? Did you accept the invitation? It went to your college address?  You don’t see it? Google has gotten better with all of this more recently…but it still can be an issue, particularly for technologically unsophisticated folks.

What we wanted (what I wanted, really, but it was based on what others were asking for) was to have a simple system–I log in, and I see the proposals I have to review.  I can go through them one by one, and see all the materials right there, and enter my rating (and comments) right there.  Attractive, easy to navigate, and easy to export the results.

So, call me strange, but this is actually the kind of thing I like to make, and after a longish Saturday afternoon and part of Sunday morning, I think I got a pretty good solution!

A demo version that anyone can see and try is here macaulay.cuny.edu/community/reviewdemo.  Of course any real production version would be private and closed–not viewable by anyone except the committee doing the reviewing.

It does pretty much everything I want it to do, with a couple of caveats (scroll down to the end).

For anyone who wants the details of how it works–it’s fairly easy to replicate, I think, and of course easy to customize and easy to improve. (This is a VERY long description, because I wanted to try to document as fully as possible.  If you want to just skip it and see the result, go right ahead! macaulay.cuny.edu/community/reviewdemo

Here’s the process:

Collecting the Entries

For this I use a WordPress site (can be a site that you reuse for each application process, or a new page on a common site.  Doesn’t really matter).  The site can be attractive, have background information and requirements, and somewhere on there you can have the application that candidates fill out.  An example would be this site macaulay.cuny.edu/eportfolios/itfprogram, except that you can’t see the application form there because the application period is now over for that position.  The magic that makes this all possible (and most of the whole thing) is from the great Gravity Forms plugin.  This is not a free plugin. For one site it’s $39 and it goes up from there. But it’s a plugin that is worth paying for.

Gravity Forms collects all the information, with all kinds of fields that you can completely customize, including the ability to let applicants upload files (limit the filetypes, of course!) or to write long or short open-ended responses.  It then spits all that information out into a csv file (well, you have to tell it to do that, but it’s an easy couple of clicks).

Processing the Entries

This took a little bit of csv magic. The idea is that the data from that csv spit out from Gravity Forms has to be processed a bit to take and make the right format for turning it into WordPress posts.  In this process, Excel’s “concatenate” function is the big secret.  (Of course, Numbers on a Mac, or Open Office Calc on just about any platform would do the same thing.  I used Excel on a Mac, but–more about this below–I would have been better off using Numbers).  The plugin (CSV Importer) we use to turn the csv file into posts wants columns for  the post title, the post body, the post type (a post or a page–for this purpose, we really want page), and excerpt, categories, tags and date.  Any other columns become custom fields.

So if you have a spreadsheet from Gravity Forms with columns for (for example) first name, last name, academic discipline, dissertation title, personal statement, CV, teaching experience, skills (or whatever), you need to concatenate last name with first name, with a comma and a space in between, and make that a new column for post title.  Then (here’s where I got really fancy) concatenate academic discipline, dissertation title, personal statement, CV, teaching experience and skills all into the post body.  But put all your html formatting into here, too.  I inserted h2 tags  with the name of the entry (for example, <h2>Academic Discipline</h2> followed by the cell number for that entry, to make it look nice in the final post.  And…for the personal statement and CV, which were URLs pointing to PDF files, I added the shortcode [gview file=] to make the google doc embedder plugin show those PDFs right there in the page itself.

That all took a while to figure out and set up, but once you get it right once, it does the whole spreadsheet in a couple of seconds.

Importing the Entries

Now that csv is clean and ready to import, it’s very easy.  Just make a new WordPress site and install and activate the plugin CSV Importer. Once that’s activated, you go to its menu item in the dashboard, navigate to your nice clean saved csv, upload it, and bingo. You’ve got a page for each candidate, titled with her name, with all her information, including the linked PDFs, displaying nicely right on that page. (One important caution.  If you use Excel on a Mac, you’re going to find that the csv it produces is not properly formatted–you’ll get “0 Entries” when you try to import.  I’ve run into this problem before in other cases, so I knew about it, but it can drive you crazy if you’re not expecting it.  There’s something wrong–somebody more aware of these things can explain it exactly–with the way that Excel on the Mac saves csv files.  Something with the line breaks, I think.  It’s probably possible to fix it somehow in Excel settings, and that would be smart, but not being smart, I just open the csv in Numbers, and then export from Numbers to a csv again, overwriting the original.  And that’s the file I use.  Wouldn’t it be smarter to just work in Numbers from the beginning, and save that step? Yes.  But as I said, not being smart…)

You can, if you like, fiddle with your CSS to make the display of those pages even nicer and prettier.  I actually ended up using a custom theme (or a customized child of a theme, really), so I could have refinements like a “next” and “previous” link at the bottom of each page, and a dropdown list of all the candidates at the top of each page, and I used colors and font styles that I liked.  Depending on your theme, you could easily do this with custom menus, too.  I leave all that as an exercise for the reader.

For privacy’s sake, you do need to set the site to be viewable only to registered subscribers.  Then you just create accounts for only the members of the committee which will be reviewing.  All kinds of other refinements are possible.  You could use categories to make sub-committees if your reviewers are broken up that way.  You can get as fancy as you need to, since WordPress is so flexible in this kind of thing.

Reviewing the Entries

Now on our new reviewing site, we’re going to be returning to Gravity Forms again (a note about pricing here–Gravity Forms’ single user license means one install, period.  Two WordPress sites in a single multi-site install would count as two sites, not one.  So if you’re trying to go as inexpensive as possible, you’re going to have to get a little creative–in other words, your site for collecting applications will have to be the same site you use for reviewing applications.  You can’t have a new site.  You will then have to work with password protected pages, or changing the site’s privacy settings after the application period and before the review period, or something.  Totally doable.)

We use a new form for collecting the reviews.  I did fields for reviewer’s name (auto-populated from the user’s login), candidate’s name (auto-populated from the page title), comments, and numeric score (you can limit to just 1-5, or 0-10, or whatever.  There isn’t any way I could find to prohibit fractional (decimal) scores.

You can put that form in a sidebar widget (or a footer sidebar, which is what I did), so it’s right there on each page.  Or you could put it on a separate page which reviewers would then need to have open in a separate tab or window.   Gravity Forms provides the widget as part of the install, and that seems like a really efficient solution, so that’s what I did.   Reviewers look at all the candidate’s information, and enter the score and comments right there without even going to another page.  Efficient!

Some reviewers like to be able to see what other reviewers are saying or have said about candidates.  Or in other cases you might not want to allow that, preferring all reviewers to work independently.  It’s really up to you, and I know that I will be doing it each of these different ways in different instances.  If you do want to let reviewers see other reviewers responses, there’s another plugin I used.  This is a (free) plugin for a plugin.  It’s the Gravity Forms Directory add on (and it’s handy for lots of other purposes, too). This lets you take the input from any field in a Gravity Forms form and display it on a WordPress page or post. It updates live and in real time, and you choose which fields you want to be included in the directory, and lots of other options, too.  It’s a very sophisticated and effective add-on for Gravity Forms, and it is free (of course it requires Gravity Forms in order to work), but donation driven.  I recommend you make a donation, if you use it.  I did.  Well-deserved.  You can see this on my demo site macaulay.cuny.edu/community/reviewdemo/results-so-far/ and if you enter a rating on one of those demos, you’ll see your own results show up, too.

Final Steps (reviewing the reviews)

When we’re all done, when all reviewers have completed all reviews, we’ll have another csv file, which we can easily download and sort and manipulate to get the high scorers, review the comments, and so forth.  That csv file may need some more concatenation, or more likely, convert text to columns to un-concatenate (for example, I’ve got names of reviewers entered as just first name and last name in one single field.  I’m going to want to convert that to last name and first name in two separate columns, so I can sort the list alphabetically.  Same for names of candidates–I’ll want to split those up).  But that’s easy csv work, really.

Then we’re ready to start interviews, or select awardees, or whatever the final step is!

Lacks and Caveats

There is a way to let reviewers edit their responses.  I have not yet been able to make that work.  It would be good to have, but until then, I’m just asking people, when they want to make an edit, to just re-review and in the comments make a note like “USE THIS ONE–THE OTHER ONE SHOULD BE DISCARDED.”  Eventually I’ll get it working and then logged in users (the only kind of users we’ll have) will be able to just hit a button marked “edit” if they want to change one of their scores.

I haven’t tried this with more than about 60 entries.  It should still work fine, since WordPress can (usually) handle 100s of pages without an issue.  But that would need to be tested carefully first.

Sometimes the google doc embedder craps out and the nice embedded pdfs (or docs or powerpoints or whatever) don’t show up.  I’ve had especially bad luck with getting it to work for docx or pptx files.  But even if that happens, there’s still a download link so you can fall back on the less convenient downloading the file and reading it.  And you can limit filetypes to just pdfs, which is probably a good thing to do.

The field for paragraph text allows you to limit the length of entries, but only by maximum characters, not by word count.  This is often the way these forms work, and people are pretty used to it, but I wonder why word count is really so hard to do.

Check it out

So that’s about it.  It was really a lot easier to do than this sounds (in fact, describing the whole process was almost harder than doing it, but I thought it might be good to have it documented–for when I forget, as well as for others). I’m sure I’ve forgotten a few points, but nothing too critical, I hope.

That link again, to check out the demo (and I do apologize for the silly fishy content there, but it fills the pages!)

macaulay.cuny.edu/community/reviewdemo