Data Management optimize

Considerations for making FileMaker Pro your database solution

Derek Schauland describes his experience working with FileMaker Pro 11 and discusses some of the various versions of FileMaker that might be options for your database projects.

Given the amount of data that we all seem to be collecting these days, there needs to be some way to manage this stuff. There are big products, like Oracle and SQL Server that can help companies keep their information organized, but licensing and sheer size can be overwhelming. On the other end, there are applications like Microsoft Access that are geared toward individual users and often are looked at to allow teams to collaborate on projects. File-sharing and size limitations have caused me troubles for quite some time.

Then I heard about FileMaker. I have a colleague who is a FileMaker Certified Developer, and he mentioned some of the ways he has worked with it. This got me to thinking about projects I have coming up for which FileMaker might be a possibility. I started experimenting with the FileMaker stack, and put together this review.

Intro to FileMaker

FileMaker comes in a few flavors:

  • FileMaker Pro
  • FileMaker Pro Advanced
  • File Maker Server
  • File Maker Server Advanced
Note: There is also a version of FileMaker called Go for iOS, but I will be looking at that in a separate review.

There are free trials available for the Windows and Mac FileMaker products; App Store rules prohibit free trials of iOS applications.

Why consider FileMaker?

FileMaker, in my experience, is relatively easy to learn, and can be used to create custom applications based on the needs of your organization. For me, Filemaker is a good candidate because the project I am working on requires more than the small database currently being used, but I do not foresee this project growing enough to require a full-featured enterprise configuration like Microsoft SQL Server or Oracle. Because FileMaker tends to land in the middle of the "For the Desktop" and the  "For the Datacenter" database platforms, it is a perfect fit.

What types of things can FileMaker help you accomplish?

FileMaker can manage orders, invoices, and customer information for pretty much any size organization. In addition to researching the solution for my workplace, I am working on creating a database for all of the freelance clients I have (or will have) going forward. In addition to the sites and companies I write for, I am going to keep track of contacts at these organizations, copies of work submitted, publish dates, and invoicing.

As of this writing, I am still adding fields and tinkering with the structure for my project; thankfully, FileMaker comes with starter files that can help you get up to speed much faster than if you were starting from scratch on your own.

Note: For this post I will be working with the Research Notes Starter Solution.

When you start out in FileMaker 11, the application presents a quick start screen that helps to get you on the right path.

Figure A

FileMaker Quick Start

From this screen you can create new databases, open recent items, choose starter solutions, and learn a bit more about the FileMaker product. You can also choose to disable it and prevent it from starting every time. To bring it back, visit the Help menu, and select Quick Start Screen.

Once you have selected your project, it will be opened, or created by FileMaker. I selected Research Notes from the starter solutions pane and was asked to save the file to get started. Once the file was saved, the project appeared.

Figure B

Research Notes Starter Project

As you can see, it presents basic tabbed forms to enter information about projects you are working on; however, FileMaker allows files and images to be included directly within the database. This functionality is to be expected in solutions like SQL Server, but not so much in solutions like Access.

In addition to saving files within a database, FileMaker also allows for inclusion of programming that will allow the web to be browsed within a database. In the case of research notes, you might keep bookmarks to resources in the database which will then be available using FileMaker's Web Viewer (on the web viewer tab of the Research Notes database).

Figure C

Web Viewer content

There are a lot of things FileMaker can do that I was surprised by when I started clicking through the application and starter solutions. The inclusion of web items and full documents, were two of the biggest for sure. Because the form is available when the solution opens, I thought the form would allow immediate editing, but that is not the case, first you need to create a new record.

To insert a new record into the database, complete the following steps:

  1. Click the new record button (or select New Record from the File Menu).
  2. Complete the form.

Note: Form data is saved as entered, however the project will need to be saved to ensure nothing is lost.

Editing layouts

To customize a form in FileMaker, you select Edit Layout from the toolbar atop the screen. In here, you can customize what the selected layout looks like, from field placement to adding or removing fields. Figure D shows the Notes tab of the Starter Solution in Edit mode.

Figure D

Editing the Notes layout

In addition to allowing edits to happen easily, FileMaker also allows role-based control of a project. Administrative users (usually those who create and modify FileMaker databases) can restrict access to users and groups of users based on role. This can also be done using integration with Active Directory so that you can determine what rights a user has based on their Active Directory credentials used to login to FileMaker.

Note: Single Sign On with external sources like Microsoft's Active Directory is not supported.

Instantly online

FileMaker has an instant web publisher feature that allows a project to be published to the web for use in small group collaboration situations. This is great for small groups because with a few clicks of the mouse, the project you are working in can be visible in a browser for others to use, without them needing to have the FileMaker client installed. In addition to Instant Online for smaller projects, FileMaker Server and Advanced Server can support online use for larger web-ready projects using the PHP scripting language.

To use Instant Web Publishing, you need to enable it from the Sharing section of the FileMaker File menu. You will need to allow incoming connections to your application and system for this to work, but enabling the feature does the heavy lifting for you. Also, you can disable it at any time.

Figure E shows the Instant Web Publishing configuration dialog.

Figure E

Instant Web Publishing setup

Other sharing options include JDBC/ODBC and FileMaker network. The former allows traditional ODBC connectivity to FileMaker databases and the latter is used for FileMaker client connections and does use TCP/IP, but is managed completely by the application.

Sharing, but not online

In addition to Instant Web Publishing, FileMaker also allows exporting of information to Excel or PDF. Using these methods will allow a FileMaker user to share their information with others who may not have FileMaker installed. Because the native export of PDF is baked in, this can be a great feature for sharing reports with others in your office. However this method is very static and once the information is out, if it gets changed, the exported information is old.

Enter Snapshot Links. Sending a snapshot link creates a file that can retrieve information from the original source in the event that the original information is updated following the creation of the link. This is very handy for some types of reporting if the information changes frequently. The recipient of the file can then see changes made later to a fixed set of data.

Customizable

With FileMaker there are many ways to skin the proverbial cat, you can create custom form items, buttons, and fields. Experiment with the tools and see what works for you. Once you get started, you will see that the platform can grow however you need it to.

Jumping in to the FileMaker platform

The initial costs for an organization can seem steep at first, but when compared with similar solutions, FileMaker is pretty reasonable.

The licensing costs for FileMaker are as follows (list, of course):

Application Full Version Upgrade Version
File Maker Pro 199.00 179.00
File Maker Pro Advanced 499.00 299.00
File Maker Server 999.00 599.00
File Maker Server Adv. 2999.00 1799.00

In addition to buying copies of FileMaker individually, you can also purchase volume licensing to better and more affordably meet your needs. For more information about volume licensing, visit http://store.filemaker.com/US/ENG/LIC/

Adding a server version or not

Deciding on whether or not to add a FileMaker server (and which version) to your environment will depend on what types of tasks you are going to accomplish with the application. My organization is looking at FileMaker to make a better user experience and overall application for some data collection than we have today. If this was done solely by one person (or maybe two people) FileMaker Pro on the desktop would likely be hefty enough to make the process better; however, because the application we currently have is used across the network by up to 10 people and much of the use is simultaneous, we are considering a server version of FileMaker. This way, the sharing and contiguous access will no longer be an issue for these users.

This was my big pitch to move to the server version of FileMaker, but your needs may be different. Be sure to consider where your project is currently and where it might grow in the future to make an accurate decision about adding server side resources. The same thing holds true for the advanced version of server. If your project will involve a lot of Internet-based activity, like collecting information for an e-commerce site, the advanced version of server might be the way to go, but again, this depends on your project and budget.

Resources for help

The folks at FileMaker have created the FileMaker Technical Network as a place where members can learn and share things they are working on for the bargain basement price of free. At the time of this writing, there was also a promotion running until January 31, 2012 that allows the FileMaker Training Series to be downloaded for $9.99 -- the usual cost of this download is $99. For one-tenth the cost, it's not a bad deal. Visit http://fmdev.filemaker.com for more information.

I would like to thank Mark Cyrulik, a FileMaker Certified Developer who was a great resource for pesky questions during my research. He maintains a blog here.

About

Derek Schauland has been tinkering with Windows systems since 1997. He has supported Windows NT 4, worked phone support for an ISP, and is currently the IT Manager for a manufacturing company in Wisconsin.

4 comments
adornoe
adornoe

alternatives, like MySql and PostgresSql ,which are a lot more robust and very compliant with standards. In fact, Postgresql is in the same ballpark as Oracle and DB2 and MSSQL as far as capabilities, and it's completely free, except for support, which is through 3rd parties, as with any software, free or not.

Gisabun
Gisabun

Buy a database when there are free [open source] alternatives? Ever heard of MySQL for one? You will find many more developers who can use MySQL over FileMaker.

kennethpeace
kennethpeace

Can i easily develop an application using MySQL? That is, drag and drop objects, pick & configure scripts? I thought to develop MySQL application requires programming knowledge and programming. i think Filemaker and MySQL are of different category. And that is why the former is more expensive. It takes away the complexity for the user who just want to quickly create an application and not worry too much about programming or technical aspect; though i must say basic programming knowledge/logic is still needed to create better applications.

adornoe
adornoe

Makes no difference. Everything has a learning curve. Filemaker has a learning curve, and nothing is as simple as it might first appear. MySQL has a learning curve, and so does Postgreql, but they are more widely supported than Filemaker. And, once you learn MySql or Postgresql, you'll be able to work on just about every other SQL package out there, because, they are based on standard SQL92 and have a huge variety of development packages and easy GUIs that make it a cinch to create complete databases. For programming more robust applications, even Filemaker would need the same kind of development skills, and the simple results that come with the ready made reports and other simple screens, won't be enough to impress even the most basic of desktop or internet application users. In the long run, MySQL or Postgresql are much better options, but, they cost a heck of a lot less than Filemaker and you can do more with them.