Insider blindness: How to make this amount of data "usable"

I help a charity (called AT) with their IT, they are drowning in their data. I’d be grateful for some ideas on how best to help them. I’ve had some good ideas, but I need outside input because of insider blindness.

AT gives grants for humanitarian purposes. They use an online application form. The form is quite long with over 90 questions. There are about 500 applications a year, of which well under 100 will eventually receive a grant. Successful applicants from previous years are unlikely to be accepted again.

So far, AT has tried to keep track of the application data in one spreadsheet per year.
The sheer volume of data, combined with the number of applications, makes this spreadsheet almost impossible to read or understand by anyone other than the person who originally created it (and who still works intensively for AT).

We need easier access to the data in the applications,
need to group them into good/bad/rejected candidates
need to know easily who has applied before etc etc.

We have a Wordpress site, Gravity Forms, automatic PDFs are generated from each application and emailed to AT. Previous attempts with Jotform failed due to lack of usability. For the time being, any solution needs to work on both Mac and PC.

Any ideas are welcome and will be gratefully received.

The spreadsheet should be a database. If you’re using web forms to collect data via Wordpress the data is already in MySQL You just need a front end to the MySQL db and you’ll be able to do wonders. If you are proficient with Filemaker, you could make the MySQL db an external ODBC data source and design whatever interface you’d like in Filemaker with many, many sorting, searching and reporting options.

Depending on what you want to do, you might be able to achieve it with a simple MySQL db manager like Sequel Ace (formerly Sequel Pro).

3 Likes

Thanks @trilo
Unfortunately, I run into roadblocks with these suggestions.

  • Remote SQL access is limited to static IP numbers by our hosting company. I have one, but AT’s admin doesn’t.
  • A tool like Sequel Ace is for developers, how do I go from there to a user friendly interface?
  • Also, giving access straight into the entire website’s MySQL DB looks way too risky, or is it not? Even if not, I’d need something that also runs on Windows.
  • I did have a look at Filemaker, a product that has changed so much since I used it many years ago with FMP 8 or so, I don’t even understand anymore which portion of their offerings I need to look at. Cloud? Local? Just some Connector? The biggest problem I always had with FMP is the fact that data and structure are in the same file, difficult to get my head round to that because a tiny ‘programming error’ can lead to terrible and unfixable consequences for the data.
  • Gravity Form says one shouldn’t look at the SQL tables, but use their documented API instead. This is likely way to complex for me or too costly or both.

Am I seeing it all wrong here?

Gravity Forms allows us to export all data as CSV, a good start. But where do I go from there?

The static IP would be an issue but I imagine AT could also get one to resolve it.

As I said it depends on what you need to do if Sequel Ace would be useful. You don’t change the interface, you just work out whether what you need can be done with what they provide.

It’s not risky if it’s done right. You could make access read only and you would only give access to the explicit table which contained the form data. I’ve written a front end to our Wordpress site (110 gb site with millions of rows) and it uses the Wordpress JSON API to write directly to the site - works a treat but it’s not for the faint of heart.

Yeah, Filemaker has become a confusing mess of late but would almost certainly do everything you want. It runs on Windows so that’s not an issue. The other alternative, although more challenging, would be using something like XOJO to write your own front end (which is what we did). You would have absolute control over data access and security but it would be many levels more difficult - especially if you’re not a programmer.

I’m not familiar with Gravity Forms so have no knowledge of their API. It’s sounding a lot like CSV is going to be your only option. Of course FMP can import CSV and it wouldn’t be hard to automate the import and build a user-friendly GUI but that’s dependent on your FMP skills.

1 Like

It can make sense to build your own solution using any number of common database tools. I’ve seen some clever adaptions of project management tools like Asana, Teamwork, Trello, etc. to handle workflows that weren’t too far away from what you are asking about. That said, it is very important to think about long-term support for in-house solutions. If you are the IT resource or it is otherwise very small, or entirely volunteer, etc., what happens if the solution breaks and no one is available to fix it right away?

I ask these questions because it sounds like the charity has grown to the point where a commercial grantmaking platform may be a better fit. At first glance, such platforms may seem expensive, but if you factor in the value of staff/volunteer time, the value of a smooth, professional experience for applicants and decision-makers, and the value of on-demand professional support, you may find that spending a few thousand dollars a year on a well-supported platform is very sensible, especially when balanced against the value of grants that are managed.

I did a grants management consulting project for a medical research foundation a few years ago, and here are some of the vendors that I recall, in no particular order:

It’s a few years old, but I can recommend Tech Impact’s A Consumer’s Guide to Grants Management Systems as a very useful resource for exploring this space.

2 Likes

@josehill Thank you very much for your questions and comments. I had no idea that grant making software exists, but it makes perfect sense. The AT charity doesn’t have funds to run a system ,that cost several thousand $/year, however, your links make for an interesting read. Unfortunately, the “Consumers guide…” is not available, their webpage requires registration and when I try this it only throws me into WP admin login, for which I obviously don’t have access. Is there any remote chance you have said guide as a document and could send it to me in a DM?

AT is entirely volunteer based, just three people plus a group of trustees who approve applications. No fundraising, grants all come from the proceeds of investments the original founders once made. AT can and does spend on IT support, but not on the level of thousands per year for just one software.

@trilo I fear that development on the basis of SQL access won’t be an option (and we can’t get a static IP in the first place) You said already not for the faint hearted. I’m an IT man, but not a developer. I might give it another shot with FMP, confused as I am at this point which of their products I should actually look at.

Is changing the application process an option ?

Rather than everyone filling in a large form x500, do a screening form and then only request full data from a shorter list ?

I did a quick google search and this list of “free” / not as expensive options came up:

https://www.g2.com/categories/grant-management/free

For an organisation your size you really need to use off the shelf software IMO.

Good luck

f

1 Like

I am an extremely big fan of Claris’ FileMaker. And Claris is owned by Apple.

I had VERY BAD EXPERIENCES with other database applications over the years, with both extremely small and extremely large databases.

And Claris has also has a FileMaker Go option that crates database availability for iOS devices you can use when you are out and about. They also have discounts for non profits and educationals. They also have many excellent free templates, as well as a very helpful staff and a user friendly community.

However, FileMaker is expensive, like all paid Apple products. But IMHO it is worth checking out. There is a cross platform option. My guess is Apple realized that they could make a lot of money by making FileMaker also available to the much larger Windows market.

FWIW, cloud services and web apps generally use an SQL server back-end. Several popular open source packages are:

But these kinds of systems generally have a very minimal user interface. They’re meant to be back-ends to other applications. (e.g. the MediaWiki software at the core of sites like Wikipedia uses one of the above databases to store the Wiki’s raw data.) There are usually command-line tools where you can manually issue SQL commands, but that’s really only useful for education, testing and debugging. You would need to purchase, download or develop and appropriate front-end for your application in order make your databases user-friendly.

Another free software possibility is SQLite. This is an SQL database that doesn’t use a client-server model, but instead works directly with locally-stored database files. But like other SQL systems, it doesn’t have much in terms of a friendly user interface. It is typically embedded within another application in order to provide data management services to that app. (e.g. Firefox uses SQLite databases for storing much of the content in a user profile).

Unfortunately, it’s been my experience that applications intended to provide generic graphical access to an SQL database (like Microsoft Access on Windows) tend to be hard to use, and require quite a bit of SQL knowledge in order to get good results.

I’ve also found FileMaker to be one of the very very few database packages that are both powerful and include a very friendly user interface. I don’t like the high price, but I pay for it (infrequently - only when a macOS or hardware upgrade breaks my current installation), because I haven’t found anything better. Maybe I’ll write specialized apps for the small number of databases I use, which can be based on an open source SQL system, but I don’t have the time or desire to do that much work.

1 Like