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).
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.
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:
@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.
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 creates 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.
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.
Fortunately, I took a database theory course in college, so for that one time I needed use use Access, I understood what was taking place under the covers. Without that knowledge, it would’ve been a horrible nightmare.
But relational databases (that is, databases consisting of multiple inter-related tables) are not easy in general and do require some degree of expertise in order to properly design and implement. A big example is the concept of normalization. A database where the tables are not sufficiently normalized will be difficult to access using the engine’s query system, and may require application code to post-process the results of queries in order to get the desired results.
At minimum, if you’re working with an SQL-like relational system, you should make sure your data is at least in first normal form (and preferably also second and third normal forms), in order to avoid unnecessary duplication of data.
Note also, that this contradicts the design principles of a multi-value database, which will typically violate first-normal form. (Although FileMaker is generally considered relational, one could consider it multi-value, because you can store multiple values in a single field through use of repeating fields.)
Another vote for Filemaker. The same database file can run under Windows and Mac. The data can be external, a local file or contained within the database file (so you do not need to have data in the same file as the application).
Also it is easy to import Excel spreadsheet data into the database so this could be one simple way to utlise the data in its current form.
The programming language for managing data is in the form of scripts that are triggered by objects on forms. I find this tedious compared with a high-level programming language but have always been able to achieve desired data analysis.
There are likely to be Filemaker developers who have created solutions similar to your needs. The costs of customising to your needs might be reasonable.
Finally, as mentioned above, the free FileMaker Go iOS app can run solutions developed on a Mac or Windows. Here is an iPad snapshot of a Filemaker solution that I developed for identifying gemstone fossicking locations around Australia.
Many thanks indeed for numerous contributions which threw me into a most welcome reading frenzy.
First thoughts:
AT cannot afford to splash out thousands for a grant making software package, but @fearghas pointed already to OpenSource SW, The list from G2 shows however many “free” solutions, but they mean often just a free demo. I’ll read up a bit more.
AT cannot also not afford to develop their own proper solution. Emphasis on “proper” as in complete or covering everything fully. This means that we can’t develop anything on MySQL, MariaDB etc. These DBMS are tempting because you can do anything, but we’d also need further complex tools for a usable UI.
I’ll provide more first (or rather second) thoughts about my thinking process soon.
Also about either including or excluding FMP.
How about a better spreadsheet? I know that’s heresy but putting together something that everyone understands rather than just one person might not be a bad idea.
