topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 8:58 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: SQL-based replacement for Filemaker Pro?  (Read 7962 times)

JavaJones

  • Review 2.0 Designer
  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 2,739
    • View Profile
    • Donate to Member
SQL-based replacement for Filemaker Pro?
« on: August 01, 2006, 12:52 AM »
Hi everyone, about 6 months ago I took a job as Technical Coordinator for a small nutrition education institution in Northern California. We currently use a fairly large and complex Filemaker Pro database to manage most of our info on students and other contacts. We have about 20,000 records, 15 or so active layouts (30 or so total) and several hundred database fields, including about 15-20 calculated fields. We use this database over our LAN as well as hosted to the outside world for remote use by our 2 other campuses.

We are soon to be embarking on a massive effort to move much of our business online. As part of that process we would very much like to sever our ties to running our own server for Filemaker, licensing Filemaker (we will need to pay for a costly upgrade soon if we want to expand to host more simultaneous users), and hopefully also end up with a greater ability to integrate our database with other parts of our business. We would like to work on automating a lot of things based on database info and changes.

To this end we are now looking at solutions for online databases and, more importantly, database management and front-ends. I know SQL can handle pretty much everything our Filemaker DB is doing as far as back-end storage is concerned. What we need - and what I am not yet aware of - is a replacement for the incredibly easy and powerful Filemaker Pro front end database manager and layout system. For those of you not familiar with Filemaker, it's basically like a more accessible version of Access. ;) The underlying structure is normal database fields, which are basically containers for data of a (possibly) specified type, or in some cases calculated values based on other fields. You enter data through fields, you can control how data is entered, data validation, etc. For our purposes it's also extremely important that there is powerful layout and visual formatting support. You can easily and fully control font, color, size, embossing, borders, text wrapping, draw boxes and lines, create buttons that lead to other layouts, etc, etc. For the low-end DB admin it's basically a dream come true. You can do many of the powerful things high-end DB's can do, but through a really simple interface.

So basically what we need is Filemaker but web-based and using SQL as the storage engine. This could be realized through any one of many different ways, but I know of none yet that is really an adequate solution. It may even be possible to make a local install of Filemaker interact with a remote SQL DB and use Filemaker as the front end - that would be great, but if that's possible to do well and seamlessly I'm not aware of it. Preferably we would find a tool that basically allowed everything Filemaker does, either totally online, or using a local management tool that interacted with the remote DB (like Drupal, or is it Plone?).

Is anyone aware of anything even remotely like this? If no such application exists it seems like there's probably a good market for it! I'll help fund development if someone wants to create it. ;) Basically combine an SQL management front end with a WYSIWYG web editor. :D

Any help is greatly appreciated and will be rewarded with credits!

- Oshyan

Rover

  • Master of Smilies
  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 632
    • View Profile
    • Donate to Member
Re: SQL-based replacement for Filemaker Pro?
« Reply #1 on: August 01, 2006, 07:51 AM »
Hey JJ -

Sounds like the Grail to me.  I'll be very interested in responses you get.  I would love to find something like you describe. :)
Insert Brilliant Sig line here

bmm

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 15
    • View Profile
    • Bent Møller Madsen
    • Donate to Member
Re: SQL-based replacement for Filemaker Pro?
« Reply #2 on: August 01, 2006, 11:32 AM »
I would recommend you to take a look at Oracle Database 10g Express Edition (XE). It is a completely free version of the Oracle Database with the catch that it is limited to 4 GB of user data and can only take advantage of 1 CPU and 1 GB ram no matter if the server has multiple CPU's and/or more ram. A number of other advanced features are also not available (check the FAQ at the link below) but it is still a very capable database for many types of applications.

Now the important feature of Oracle XE is Oracle Application Express (APEX) - a rapid web application development and administration tool that works entirely through a browser. Granted it is not entirely WYSIWYG as you define the properties of a page and APEX then renders the page based on these properties but it is still very easy to create an application through the use of the many wizards.

Oracle XE is available for both Windows and Linux.

Oracle XE:
http://otn.oracle.com/xe
Oracle APEX:
http://otn.oracle.com/apex
Screencast of Oracle APEX: http://www.oracle.co...icktour_viewlet.html
« Last Edit: August 01, 2006, 11:40 AM by bmm »

JavaJones

  • Review 2.0 Designer
  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 2,739
    • View Profile
    • Donate to Member
Re: SQL-based replacement for Filemaker Pro?
« Reply #3 on: August 01, 2006, 11:00 PM »
Hmm! I shall definitely check that out, thanks!

While I'm looking into Oracle, here are some other things I'm contemplating.

First, it seems like it might be fairly easy to integrate/link our existing FMP system with an SQL DB. This would allow us to interface some PHP-based web tools and systems that could operate on the data, while still using our existing FMP licenses, database, layouts, etc. So this isn't a full solution but might ultimately be the best as it means much less retraining. Really the best of both worlds. The only drawbacks are dual database formats, translation speed issues, and license costs of Filemaker.

Second, there is this interesting product I'm looking at: http://www.servoy.com/
Sounds like it might be a bit more complex/difficult than I'm hoping for, but if I learn the system it might be just right. It claims to have "WYSIWYG" form design, for example. And there's some kind of free trial available, so I'll definitely check that out.

Whatever we choose I'll report back here about it for those who are interested.

- Oshyan

mouser

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 40,896
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: SQL-based replacement for Filemaker Pro?
« Reply #4 on: August 02, 2006, 02:30 AM »
another thing to consider is something like Ruby on Rails, which has some pretty clever database management stuff that takes only a few lines of code to write.. depends how much custom scripting you want/need.

JavaJones

  • Review 2.0 Designer
  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 2,739
    • View Profile
    • Donate to Member
Re: SQL-based replacement for Filemaker Pro?
« Reply #5 on: August 02, 2006, 04:01 AM »
Ruby is great, but it's really totally wide open. It's a reasonably generalized coding environment, not a specific tool for creating Filemaker-like visual databases. If we just wanted a raw database I could probably manage that with simple direct SQL access and maybe output to one big form with all fields accessible or something (this basically becomes like a simplified version of PHPMyAdmin perhaps). What we need though is a way to organize, customize and visually style the database interaction, entry and display fields, buttons, etc. as well as created limited functionality like buttons that move from one "layout" (or "form") to the next or fields that calculate based on the values of other fields.

Filemaker Pro really is pretty much the ideal product for us so it makes a very good model for the kind of product features we need in a replacement. Filemaker really does almost everything we need from a database management perspective. It's just that we are coming to the point where lots of systems need access to the data to automate or expand services and functionality and Filemaker is not great at doing this, especially with web-based applications like forums, etc.

Anyway the suggestion is appreciated but I think using something as low-level as Ruby is really more than we need, and would entail more work than we have time to invest.

- Oshyan