Make any database into your very own iPad app with FileMaker

Archaeologist Michael Jennings records the Jericho site he's excavating in the FileMaker Go iPad app he helped build. Credit: Filemaker

If you need to hack around with some data, unless you're a specialist, you put it in Excel. It's a universal data-munging engine that lets you add, average, sort, filter, and process your data to see what the patterns might be. Excel is enormously powerful. But it's a terrible place to keep your data, still less to capture it. Typing and tabbing between fiddly little cells where copy and paste doesn't work the way it does in any other software makes data entry painful when you have a mouse and keyboard; unless you have a Windows 8 tablet with a pen like Surface Pro, you probably wouldn't attempt filling in more than the odd number in Excel on a tablet. If you need to store an inventory, a catalog, or a list of anything, what you need is a database.

Enter the venerable FileMaker, which I've been using for about two decades.

FileMaker Pro is an unusual combination. It's a database that's powerful enough to build database solutions in. You can connect to Oracle, SQL, ODBC, and XML data, create workflow and business logic, set up scripts, and do data validation and conditional formatting. Large companies like the Austin Convention Center and Lee Medical are using FileMaker to create iPad systems that replace pen and paper for construction workers who need to see what booth goes where at a trade show, or nurses who need to see patient records and prescriptions.

But if you don't need the more complex features, FileMaker is as easy to use as Excel (for a lot of things, it's easier in fact). You can work on a Mac or PC. You can even start with an Excel spreadsheet that has column titles and convert it into a basic database (with the column titles as the field names) just by dragging the file into FileMaker. That gives you a layout that looks very like Excel where you can search and sort your data. Then you can drag the fields around on screen and add buttons and scripts to create different layouts. Lastly, you can use the free FileMaker Go software to open and update your database on an iPad, an iPhone, iPod Touch -- or most Web browsers. 

That's what Chicago University Ph.D student and archaeologist Michael Jennings did for the dig he's part of in Palestine. In the 11th century the Islamic castle at Khirbet al-Mafjar, near Jericho, was a lavish palace with mosaic-decorated bath houses full of decorative wall carvings, sculptures, and frescoes.

Usually, the archaeologists at the side take photos and draw sketches, then keep them in large and heavy binders. Paper records can go astray; in fact all the records of the Jordanian archaeologists who excavated the northern area of the Jericho site are missing, so the team has been taking new notes on site. They had to lug the binders around the site and then stuff them in their carry-ons before they fly back to their university departments, where they would scan and type the information in a database to make sure they don't get lost again.

As that database was already in FileMaker, Jennings and the field director Jehad Yasin were able to make an iPad version very quickly. They can take pictures using the iPad camera and draw a sketch on screen. Because the database already had details of things like the kind of coins found in some buildings, it was easy to create drop-down lists to choose from; that's not just faster than writing it down, it makes the records more consistent so they're easier to analyze.

Credit: Screenshot/FileMaker
Container fields can access iPad hardware -- like the camera

After they started using the database on the site on iPads (in protective cases) the archaeologists realized it would be useful to add more controls, like a radio button to mark whether the blocks in a wall showed signs of having been taken from an older building and reused.

That's easy to add (back on the Mac or PC) because FileMaker Pro includes layouts, controls, and themes that are designed for the iOS interface larger fields and buttons that are easy to touch. Themes let you pick a color and style, and get the right fonts; FileMaker has basically turned the Apple design guidelines for iOS into themes and there are "stencils" that make the layout the right size for portrait and landscape views for iPhone and iPad screens.

Credit: Screenshot/FileMaker
FileMaker knows the right screen sizes for iPad and iPhone

You can add signature fields to forms so people can sign right on the iPad, give them a button to take a photo, or have the form automatically save their location.

Credit: Screenshot/FileMaker
A signature field in FileMaker To Go

If you need more than a simple list of fields, you can have repeating fields, which you lay out as a portal, with calculations fields to add up totals or averages.

So, for instance, if you have a list of equipment with prices and you need to record how much equipment everyone in your team is using to see if any of them are over budget, you can make a repeating list for the equipment each person is using that gives you an automatic total. You can use conditional formatting to make any amount over budget show up red and in bold so it's easier to spot. You can also write simple scripts, like switching to a different layout if you pick a date that's more than a week old.

If you need ideas for what works well, FileMaker Pro includes several "starter solutions" for useful apps like an inventory list.

When you're done, there are a few different ways to get your FileMaker database onto your iOS device. The simplest is just to export the records you want and mail them to yourself; open them in FileMaker Go and you'll get the database and layouts. If you add more records on your iPhone or iPad, you can just mail the updated database back to your desktop, put it on a website, or save it to a cloud storage service like Dropbox or SkyDrive.

If you've got a few people needing to take the database with them and you don't want to deal with exporting, distributing, and importing changes afterwards (or if you're working with confidential data and you want an encrypted connection to the devices you could set up the optional FileMaker Server and they can open databases directly in FileMaker Go. If they leave the office, you can write a script that syncs their changes back to the database as soon as they connect again. Or you can use the Web publishing option in FileMaker Pro to create a web version of the database; if you want people to be able to update that live in their browser, you can publish to one of the many FileMaker hosting providers.

Depending on what you need, you can keep things simple or set up a more powerful way of distributing your database automatically. That's exactly what FileMaker offers: you can make a very simple system for viewing your database and updating it, or you can add in more features to get something more powerful without having to become a database expert.

Instead of hunting for an iPad tracking app that has all the fields you need, you can make your own and just start using it.

From CIO: 8 Free Online Courses to Grow Your Tech Skills
View Comments
Join the discussion
Be the first to comment on this article. Our Commenting Policies