Replacing FileMaker with Free Software?

jhealy1024 asks: "I'm looking for a way to replace our FileMaker DB solution with an open-source RDBMS. Problem is, FileMaker's GUI and report design tools are pretty darn good, and I can't find a suitable replacement. Anybody out there have a solution that doesn't require me to take a year off to hand-code a replacement solution?" "I'm the netadmin for a small private school. Since we're Mac-based, we've grown up storing all our data in FileMaker, including student information, grades, class assignments, gifts, inventory tracking, and just about anything else you can think of.

FileMaker is coming out with version 7, which is going to require us to tear all our databases to pieces and build them up again from scratch. While the new FileMaker is an improvement, it's still a toy as far as "real" databases go. (The latest update just introduced relational tables, for example). Also, data lock-in is becoming a problem; I'd like to have access to all our data from non-FileMaker interfaces (to populate our LDAP directory, for example). While we can work an export from FileMaker, it would be much better if the data were available in an open, standard database instead.

I figure, so long as we're rebuilding everything from scratch for version 7, why not use a "real" RDBMS (no flames about which, please). Problem is, FileMaker does two things very well:

1. Rapid development of front-end data entry screens (using a GUI for layout)
2. Ability to create printable layouts for reporting (mail merges, report cards, etc)

I can program data entry screens myself if I had to (either on the web or on the clients directly), but the printable layouts would kill me. Does anybody know of any package that will allow me to replicate FileMaker's easy interface for use with a RDBMS package such as PostgreSQL or MySQL?

Thus far, the only solution I've found is to use some kind of SQL access plug-in for FileMaker. This way, I get to keep the FileMaker interface but ditch its lousy relational model. Unfortunately, I'd still have to pay for FileMaker, and the SQL plug-in requires tons of extra coding to pass the data from FileMaker to SQL and back again.

I know other people have had to move from small, proprietary systems (FileMaker, Access, etc) before; what have you done to keep the simple user interface alive?"

Comments

There's an old saying...

[AKAImBatman]

If it ain't broke, don't fix it. If FileMaker has been good for your school, don't worry about replacing it with a "real" database. Many people don't need all the features of a "real" database, and all they'd get is more complexity and possibility of failure.

Re:There's an old saying...

[rjstanford]

Absolutely. At some point you have to decide whether you're in the education business, or in the software design and support business. I would stick with the solution that everybody already knows - even going through an upgrade, you'll be so much further ahead faster than you would be by throwing it all out and starting over. You know FileMaker's quirks, after all - and believe me, everything has 'em, so knowing one set well is a good place to be.

Re:There's an old saying...

[javaxman]

More to the point, why even upgrade to 7 ?

Yea, 7 has a lot of spiffy new features that make Filemaker less of a file-sharing system and more of a real server DB ( a *little* more, anyway ). But *must* you upgrade? They'll support your current version for a little while, right ?

How about just looking into SQL-interconnect services, if you want to explore new front-end options ?

If you want to put a 'real' database on the backend, first ask why. Is FileMaker too slow, or are you just looking to cut costs? If you're looking to cut costs, and your current system is working, just don't upgrade. If performance is a problem, then you have a big job- you'll have to learn a lot, and someone will have to do some design and coding. I'll recommend my favorite enterprise DB- PostgreSQL. Sure, there's a little bit of a learning curve, but it's really not bad, and you can do *anything*.

So you want easy form design and reporting? Is that your main criteria? You might want to stick with FileMaker, then. On the other hand, you have a couple of good options, especially if you are a good enough programmer and have enough time to learn Objective-C.

Your options, as I see them, are basically
1) train yourself to learn Cocoa,
2) annoy your users with web-based forms, or
3) continue to annoy them ( and you ) with FileMaker.

We've found that, especially for simple DB lookup stuff, throwing together a nice-looking data viewer or editor in Interface Builder is pretty darn easy, especially once you do it a couple of times ( can you say code reuse? ), and writing a real document-based app lets you do some stuff that'd be darn difficult and/or ugly in a web form ( or in FileMaker ).

Of course, I don't know that all of your clients are on OS X, you don't mention. If you have a few OS 9 holdouts, you may want to go web-based or stay FileMaker.

I'm sure some folks will jump all over me for suggesting doing real Cocoa programming as an actual option for you, but really- it's not that hard, folks, especially if you're doing fairly basic database stuff. This year I took a guy who had only ever written shell-level C programs, got him pointed in the right direction with Objective-C and Interface Builder, and he's written *several* great-looking, highly functional release-quality applications in the past 6 months. He wipped together one multi-user database app in, seriously, like 3 days. It's a real option and I'm a little sad that nobody has offered it up. On the other hand, it's not a simple 'no-programming' solution with auto-generated reports... one of the toughest things for us was learning how to do proper text layout for printing in Cocoa. Then again, once we worked out some details, we have results we couldn't get with FileMaker, and certainly wouldn't get with a web-based forms approach.

Of course, I'm a bit biased towards a 'real programming' solution; before we went all OS X ( and for one project afterward where PC compatability was deemed worthwhile ), I was writing Java Swing database apps... which were totally useable, even with OS 9's antiquated JVM, and once the *design* was nailed down, implementation followed pretty quickly. If you're all Mac at your campus, your actual administration tasks should be light enough to let you do all that programming, right? ;-)

Really, though, that's what we've found. I work at an all-Macintosh business, and our IT staff seems to have a lot of time to work on programming projects, rather than applying patches and fighting viruses. Writing database apps in Cocoa gets to be a pretty quick process, and PostgreSQL handles large, complex queries like a champ. It's a great combo.

But honestly, it sounds to me like you shouldn't touch your system, unless it's too slow or has become hard to support. If you do replace it, do it slowly, in phases, and give yourself plenty of time to do the job right.

GLOM!

[tempest303]

How about Glom?

It has a nice, clean GTK interface, and uses PostgreSQL for its backend.

Good luck!

Re:http://cocoamysql.sourceforge.net/

[Shayde]

This is unfortunately not what they're looking for. There are any number of 'SQL front ends' - that let you do basically all the functions that a MySQL user can do from the command line. What this doesn't give you is a customziable front end with linked forms, back end processing, and data verification. YOu want to present the user base with a native, comfortable look and feel.

Others have recommended web-based solutions wth PHP, which are okay, but are difficult to maintain for the non-PHP literate.

Perhaps something like Rekall from theKompany would do it? It's not free, but it's a lot less expensive than most of the commercial front ends out there. It supports MySQL, is multi-platform, and has forms and front end scripting (using Python I believe).

also looking for easy, open src forms layout tool

[LodCrappo]

The meat of the article's question, which hasn't been addressed in the replies yet at least: is there an open source tool that makes generating forms (web based I would hope) as easy as you can do this in formmaker or access? For instance, is there a tool I can use to rapidly create data input screens with data validation or quickly throw together some screens that run queries with screen formatted results? The backend shouldn't matter too much, there are plenty of great open source tools to store and query data, but what about the user interface side? So far I haven't found a good solution that doesn't require manual html/php/perl/etc coding. (Not that I won't do that if I have too, but I'd rather have something more like ms access if it exists in open source, even if it's not as polished). any ideas?

Re:DIY

[Nos.]

There's a couple of reasons why (and its not just the FOSS community). The first is compatibility. I won't go into all the browser compatibility issues, but its easier to create a web page that works on multiple OS's than it is to create a desktop based application. Secondly, portability. A web based application means I can work from anywhere I have an internet connection. Now that's not to say a desktop application can't do the same thing.

In my experience (even if you're developing for a standard desktop environment) web based apps can be build faster. Then of course there's the issue of upgrades. Its easier to upgrade/update a website than multiple client machines.

It has always been relational....

[greed]

If you read the linked article on FileMaker's website, it says:

Now store multiple database tables in one file instead of having to break them up into multiple files.

I've been using FileMaker at home since it was made by Claris, back at version 3.0. It's always been relational. You build relations between files--one file is one table. Now you can have multiple tables in one file. And you can still build a relation to a table in another file, so you've got the best of both.

In fact, using the free demo of 3.0, I built a database with about 25 relations in it, entirely without the manual. Consequently, I was out to the store the next day and bought the real thing. I've upgraded to 5.0 and 7.0 since.

I'm not sure how much "re-writing" is required to upgrade, I just load all my databases from the old version into the new one and let it create new files in the current format. I've never had to change the database definitions.

(It would be nice to turn a couple of my DBs into a "single file with multiple tables", but hey, it works fine in multiple file mode, so like others say, why break it?)

There are times when it Would Be Nice to throw some really grotty SQL into the system. But they're fairly rare.

Knoda

[mpieters]

I think Knoda sounds like an exact match. Feature list:

* define and delete databases;
* create, alter and delete tables and indices;
* add, change and delete data in tables;
* define, execute and store sql queries;
* define, execute and store queries with a "query by example" GUI;
* import and export CSV data;
* define and use forms;
* define and print reports; and
* write your own extensions using the integrated Python interpreter as scripting language

This is the Open Source equivalent of MS Access and Filemaker, except that it can use any database backend (native MySQL, PostgreSQL and SQLite support, plus ODBC). The report and form designers are full WYSIWYG GUIs, like the commercial counterparts.

Possible disadvantage? It requires KDE3, so it does require quite some extra bagage you don't normally find on a Mac OS X system, but it *should* work.

Re:DIY

[BrynM]

I think you'd be best off writing your own stuff. MySQL + PHP

This answer irks me. You know, people always mention this, but have you ever attempted to do it? Just "sit down and code a web based version in a weekend" type stuff is horribly unrealistic. First, coding a secure and bug free PHP version of some app your coworkers have used forever is a bitch. Everyone wants their ideas implemented, people refuse to work because you haven't "trained" them yet, managers are (rightly) aftraid of you building it and then being hit by a truck tomorrow... This is not just some hodge-podge personal website to be coded on your own terms. Finally, this guy is probably not a PHP programmer and it's silly for you to assume he is.

People usually ask these kinds of "ask slashdot" questions because they can't just sit down and roll their own. They are looking for genuine alternatives. Answers like this are akin to "You don't like your Ford? Just get a welding torch and some grease and make your own car..." A better answer would have been to point him to some coding resources directly related to what he's trying to do if you really wanted to provide an answer like "If you use such and such implemented in PHP, you'll be able to consider coding your own solution." Any moron can just say "make your own" without knowing what that really involves.

Re:Recode for Filemaker 7

[NatasRevol]

Ditto the parent.

I've created some horribly complicated db's in FileMaker. The kind that become huge systems as end users ask for lots of little parts to be added over the years.

FM7 converted all of those with only minor issues. Usually just GUI issues.

Gaping hole in the Open Source Software

[apropos]

This is one of those big, gaping holes in open source software. I swear most open source programmers don't even understand the question. Let me try:

Alice is an expert in some area of business. She can even wrap her head around simple databases. How can she write database apps without having to call Bob, the resident Unix hacker who doesn't want to waste his time coding simple data entry screens.

What tools can Alice use? Open Office is workable now, and although pretty clumsy, compares to the VB .net "way". Alice has to learn quite a lot to get there, though.

There's http://pythoncard.sourceforge.net/samples/custdb.h tmlPythonCard, which is looking very nice: Python is a very newbie friendly language. If you use this, then ReportLab (http://www.reportlab.org/) might be a good choice for reporting tools.

There's Rekall, I've not used it at all, although it looks pretty good.

And then there is GNU Enterprise http://gnue.org/. It is eventually supposed to be an ERP system, but currently the project team is working on what appears to be a very sweet set of db app development tools. Rumor is that it's at a usable point, but I've never been able to crawl through the install process (even on Debian).

There are more, but I haven't found any really good ones.

Re:Gaping hole in the Open Source Software

[Anonymous Coward]

For God's sake chain Alice down! Force her to go to Bob! Here's the problem, for you lurking management types:

The biggest clean-up, hurry-up, clear-the-decks projects I've had to do are "office" databases written by some schmoe who showed it to an executive and now has to scale it up to the whole enterprise. The poor guy never planned for it to be multi-user, and he has to go hand-clean his data every couple of days because he didn't put constraints on the input, and there's no way he can support ten people much less ten thousand.

Get IT pros involved in the beginning -- sure we'll ask nasty questions like "do you really want to store the name all in one field" and we'll make the project take 5 times as long. Of course we'll raise questions about whether it's legal to keep this information and who the intended users are. But that's why they keep us around -- we're paid to think of the hard questions in advance, when you haven't committed anything to the project yet.

When you have to scale it up from 1 to 10,000 users and all we do is edit a couple of files and say "ok, done!" you'll thank us.

This is not about justifying my salary. This is about limiting the unplanned demands on IT and keeping you from propping your business unit up on a shakey foundation.

Re:take a year off vs. pay for FM7 ?!?!

[Anonymous Coward]

I use Filemaker a lot - have several hundred databases that I have set up using FM and I use them several hours a day. Been using FM since it was a Nashoba product in 1986 and have databases that I set up 15 years ago that I still keep up to date.

I started out with Filemaker 1, upgraded to Filemaker 2, and finally settled on Filemaker 4.

Every time Apple comes out with a new version, I look at the features, try out the demo, and ask myself Question 1 "Do I need to upgrade at all?"

So far the answer has been no and I'm still using Filemaker 4.

Re:DIY

[tacocat]

I think you've jumped into a few really bad assumptions on this one.

First, you obviously missed the part about not taking a year off to hand code it.

Second, is MySQL and PHP always the best solution to a problem? I find it useful, but for someone who is trying to get performance out of a server, I seriously doubt that MySQL + PHP is going to be the right answer. I've found the work I have done on a HTML::Mason web server to be easily 10 times faster than it's PHP counterpart.

Third, I don't know much about the project, but I think the question was in regards to the RDMS and not the web pages themselves. I am going to assume that most of the work here is in the database structure.

If this is the case, then even a hand migration shouldn't be that difficult to do. All you have to do is write the scripts to create the tables based on their current definition and procede to dump/load the data. It would be well worth it to develop scripts to do all of this and test them out so that you can migrate the entire back end in one shot.

I don't have an answer to a easy print interface, but I can venture some guesses on what might work. OpenOffice or StarOffice has some kind of ODBC connectivity and maybe that can be capitalized with it's capabilities. Alternatively, and maybe lastly, perl has some printing capabilities that would make for somewhat simple reports but at very high speed.