Data Entry is The Worst. Help?
January 27, 2017 8:38 AM   Subscribe

I manage a not-for-profit mobile outreach program that sees a large number of people in a short period of time (about 150+ in a 3 hour time period) twice per week. Currently, our stats are paper-based, followed by data entry into a Google Docs spreadsheet. This is time consuming and doesn't always lead to the most accurate end results. We want to improve this process but we're not sure how best to do it.

I'm going to try to provide as much info as possible, but I'm happy to answer questions if needed. Apologies for the length.

Everything that we do during a shift has to be tracked. Currently, we use a legal-sized print-out of our spreadsheet and we hand-print the details onto it. Each week, assuming we can read the handwriting of our stats-taker, we then enter all of that data into a shared Google Docs spreadsheet - it needs to be readily and easily accessible to a number of people for funding, planning, reporting, and other purposes. (We're a small agency, so there's no central server on which to store documents for multiple people to use. We all have our own laptops or desktops, so there's no common computer being used, either.)

The vehicle we work out of has 2 separate sections - in one area, people are getting food/hygiene products/clothing, and in the other area they're picking up or returning harm reduction supplies. There is often overlap between visitors between the 2 sections - meaning that someone may be counted on both spreadsheets. This requires us to do a bit of extra work pre-data entry in order to not artificially increase the visitor count each week.

Each visitor has a unique code. After they provide that code to us, we track 40+ things on our spreadsheet - the majority of which are numeric entries. Sometimes we enter only 2 or 3 things, sometimes we enter 40. Columns include things like '# of snacks provided", "# of hygiene products", "# of syringes distributed", "# of sharps containers returned" "# of socks". Other than the unique code area, there is only one free-form area where we include things like "requested items" and "feedback" - generally very brief notes are made in this section.

We currently have an Android tablet that we can use - and we have the budget to purchase a second one. We can, if needed, tether to our cellphones - but would prefer to collect the data offline and upload it later when we're back at the office (to prevent any issues if we lose cell access and to keep the costs down).

The person entering the stats may be staff - but could also be a student or volunteer. Even amongst staff (sigh) there is no set level of comfort with technology, so we want things to be as straight-forward as possible. The more steps involved in entering data, the more likely we are to have people skip/lose/forget to do it thoroughly.

In my dream world this is what we'd have: a pretty GUI of some sort that was very user-friendly. I would set the entire thing up ahead of time (based on our current spreadsheet) and it would be protected in some way to prevent people from accidentally editing it while we're out and about. People could enter the various bits of data in an appropriate way - there would be a few text-entry spaces and the rest numeric only. People could skip between the fields as needed by tapping and entering data - they wouldn't need to tab through 40+ options. Everything would be on one screen (scrolling down a long screen is fine) with a 'submit' option to be used after each client's details have been tracked. On hitting submit, the data would be saved and the GUI would immediately be ready to enter the next person's data.

The data would be stored offline (on the tablet) in a spreadsheet format during the shift. At the end of a shift, we could upload the spreadsheets somewhere and do the manual work of prettying things up and removing duplicates. We have NO problem spending an hour or two each month cleaning things up.

Keeping costs low is really important, but there is a small budget if needed. If we can use the Android tablet we have, and somehow have this work with Google Docs, that would be ideal. If we can use free apps to do this, great. If there's a fantastic option that will cost some money, I can deal with it.

Assume that I have a decent amount of comfort fiddling around with things (once you point me in the right direction) and that I also have access to some additional nerdy skills through my friends.

Any thoughts on how I can make this happen?
posted by VioletU to Computers & Internet (19 answers total) 3 users marked this as a favorite
 
In terms of making the data-entry front end as user-friendly as possible, you could set up a Google Form that feeds into your Google Sheet. We use a similar setup as a production checklist for a sheet with about two dozen fields. Our non-production people just fill out the form on the front and never have to deal with the data spreadsheet itself.
posted by Pandora Kouti at 8:51 AM on January 27, 2017 [3 favorites]


It would require internet/data access (though not much) but you could probably work this up in Google Forms--stop using the paper all together. Accessible by a simple link, every entry sends to the same google spreadsheet, and then all the data is right there for all your people to use.
posted by phunniemee at 8:51 AM on January 27, 2017 [1 favorite]


In my dream world this is what we'd have: a pretty GUI of some sort that was very user-friendly. I would set the entire thing up ahead of time (based on our current spreadsheet) and it would be protected in some way to prevent people from accidentally editing it while we're out and about. People could enter the various bits of data in an appropriate way - there would be a few text-entry spaces and the rest numeric only. People could skip between the fields as needed by tapping and entering data - they wouldn't need to tab through 40+ options. Everything would be on one screen (scrolling down a long screen is fine) with a 'submit' option to be used after each client's details have been tracked. On hitting submit, the data would be saved and the GUI would immediately be ready to enter the next person's data.

You can do all of this with Google Forms. I'm not sure how that would play with your internet access. I've had limited success with Google apps running offline, but then again I haven't been trying very hard.
posted by Elizabeth the Thirteenth at 8:52 AM on January 27, 2017


I'm doing this with the Android app Memento and Google Sheets. Create the database in Memento and use it to collect data in the field. When you get back to wifi, sync Memento to Google Sheets.
posted by irisclara at 8:59 AM on January 27, 2017 [3 favorites]


If you were ok with the system failing when internet access goes down, you are exactly describing a Google Form. I'd have to think about how to accommodate offline access, though.
posted by Salvor Hardin at 9:06 AM on January 27, 2017


I haven't used Memento, but the way irisclara describes it, that's exactly what you're looking for. A quick Google search turns up DeviceMagic as well.
posted by radioamy at 9:19 AM on January 27, 2017


I think Google Forms works online, so it's not going to meet OP's needs.

You basically need something digital so you can manipulate it and get it into the right format. Even a Word doc would be better than what you're doing now, right? Less room for human error.

If you must do offline data entry you could set up a spreadsheet with some validation to allow only certain kinds of entries (eg a number) and have the person entering it create a new column or row or whatever when they're moving to the next person. A programmer could probably make a little script. But the online tools already exist and are user friendly--depending on where you are you could perhaps buy a wifi card to plug into your device. Or can you set up a local network?

If you can find a way to work via wifi while entering the data, I think that gives you additional security in case the tablet is lost or it breaks. Typeform works, too, in that case. Super user friendly.

Another option to save time if you stick with handwriting is hiring out the data entry work.
posted by ramenopres at 9:49 AM on January 27, 2017


If you really want offline access and are willing to learn VBA, you can create an Excel workbook that has all of your fields in an easy to read collection form (that could have prepopulated dropdowns, protected fields, etc) and then use macros to convert the entries into rows on another tab in the same workbook. At the end of the day, all of the consistently formatted rows of data can then be easily copied into Google Sheets. We use a more convoluted version of this at my organization, this tutorial seems to be close to what I'm thinking of: http://www.contextures.com/xlForm02.html

I don't think macros work on mobile devices, so in those cases, you'd need create a separate workbook for each client, send them all in to someone with a computer, and then run the macro afterwards (could write something to batch process all of the individual workbooks in one go). I have not done this myself but know that it can be done.
posted by yeahlikethat at 9:58 AM on January 27, 2017


Oh, remembered something else that I have done. You can create fillable PDF forms in Adobe Acrobat Pro (or whatever the new equivalent is) and use their form distribution tools to compile the responses and export it all as a CSV.

Basic steps are create a form, "distribute manually" to create the response file, providers fill out and save a copy of the form for each client, forms get sent back to whoever has the Adobe license, Adobemaster adds the forms to the response file and exports the results.

The nice thing is that it's very easy on the provider's end, and if you're good with computers not that bad on the backend either. The bad thing is it's a little bit of a PITA if you need to frequently update the form because there are any mismatches in the fields the import needs babying and you'll inevitably end up with five slightly different poorly named versions floating around and no way to tell them apart by glancing.
posted by yeahlikethat at 10:16 AM on January 27, 2017


QuickTapSurvey works offline.
posted by jcatus at 10:22 AM on January 27, 2017


Yeah, the functionality you're describing is met by a lot of survey packages: a series of predefined questions where you need basic answers and easy use is exactly what they're good at. Offline Surveys is a random Google hit that uses the open source LimeSurvey platform to create the surveys.
posted by Homeboy Trouble at 10:50 AM on January 27, 2017


I think people do this in iOS with airtable
posted by oceanjesse at 10:54 AM on January 27, 2017


I think Airtable (which many people really love) is not usable offline.
posted by mean square error at 11:25 AM on January 27, 2017


There are open source medical records projects that might work for you. Some are designed to be used in undeveloped areas where there is no internet, so they work offline. They then sync when you do have internet. Such as:

Hospital Run

There are other open source medical records listed here. I don't know how many work offline. You can look them over and see if any make sense for you.

There are also things on Source Forge that might work: linky
posted by Michele in California at 11:33 AM on January 27, 2017


There are also articles on how to use Google Docs offline on an Android device, such as: this
posted by Michele in California at 12:16 PM on January 27, 2017


Best answer: Not sure if self-linking is okay, so mods feel free to delete, but I work on software that does exactly this.

You'd create an application with a form with the necessary questions, then put that app on your tablet and use the tablet to collect data. The data collection works offline, and then at the end of a shift, you'd go online and that shift's data would be be sent to the server, where you can download it to excel. You can customize the forms pretty extensively - restrict to numeric entry, add logic (don't ask X unless they answered Y on the previous question, etc.), add validation, etc. Since you're seeing people repeatedly, you can set up a "registration" form to enter a new person, and then on subsequent visits you'd select that person from a list (or search by their code) and update their unique record. The base tier is free, and I suspect that would be enough for what you need ($ is for when you have a lot of users, or when you need more advanced features). There's plenty of docs on getting up & running, and a good-sized user group for asking questions (also monitored by, well, me). Feel free to memail me.
posted by orangejenny at 5:34 PM on January 27, 2017 [2 favorites]


Response by poster: Thank you so much to everyone who has answered so far!! Of course, feel free to add more ideas if you have them. I am going to spend time this weekend experimenting and trying things out to see what will work for us.. with my fingers crossed.
posted by VioletU at 9:23 AM on January 28, 2017 [1 favorite]


Storing your clients' drug use info on a Google spreadsheet gives me a bad feeling.

You are doing a fairly large-scale database project, collecting what sounds like super sensitive PHI info, but there is hardly any mention of words like 'database', 'privacy', or 'security'.

It sounds like you are doing important charity / service work, (which is good) but this is no reason to put your clients at risk due to lack of budget or skills.

Two serious questions:

1. do you really need to be collecting any of this data, which could harm your clients? Have you run risk/benefit analyses?

2. if you do think it's necessary, can you find professional pro-bono security/database help to do it right?
posted by soylent00FF00 at 7:40 PM on January 28, 2017


Response by poster: soylent00FF00, I definitely appreciate your questions and I realize I didn't do a great job of clarifying those issues in my ask. Our agency is half health care clinic and half social service agency. There are a lot of legal restrictions and regulations with which we comply and we are very, very concerned with privacy even outside of the legal requirements.

We do need to collect all of the data - and we've eliminated (or never bothered with) collection of any data that isn't crucial.

The 'code' we collect for each person visiting is a code they generate themselves. This allows us to track the number of individuals visiting during a particular shift but does not allow us to identify them. We never ever record someone's name, health card #, address, phone #, or anything else beyond preferred gender (again, for stats). All of the stats (food items, harm reduction supplies, etc.) are tied to that code only for the single visit - there is no client file maintained. The only person who would be able to identify a specific client within that spreadsheet/data would be the client themselves.

While we do provide some health care during mobile outreach (blood testing, hep C treatment, etc.) those stats and details about patients are kept separately in our (extremely secure) electronic medical record system - a system only available to our medical staff. We do not tie any of the other services (food, harm reduction, clothing, etc.) to those medical records at all.
posted by VioletU at 7:30 AM on January 29, 2017


« Older Long distance second opinions   |   What companies make good quality stuff without... Newer »
This thread is closed to new comments.