We’re about to release version 3.0 of the iCare solution for our customer, Lullaboo Childcare. It’s a large release, with a lot of new functionality. One of the new modules incorporates their ability to track employee hours for payroll purposes.
One of the challenges we faced was in deciding on architecture. Lullaboo have, at latest count, 7 childcare campuses in the Toronto area. Due to requirements of the Ministry of Education, each campus has its own database. (Basically, the Ministry doesn’t want them to be dependent on Internet access for access to their child records.) So we had to design the system in such a way that data from all the campuses could be rolled up to a central location for payroll processing.
This is pretty easy in FileMaker; you can just create another database file to hold the data. Getting the data from one FileMaker file to another, until version 17, had basically a few options:
- Just link the two databases together. I can put table occurrences from one database on the Relationships Graph of another, and directly access it. Easy peasy. But there’s a catch: I have to be able to open the other database directly in the client (whether it’s the desktop or an iOS device). That was a problem for two reasons. First, we didn’t want to allow the non-supervisory staff to have access to the central payroll repository. Second, there was that Ministry requirement not to be dependent on an Internet connection.
- Use export / import. This means basically dumping the data out to a file from one database, and then importing it into the other. It’s feasible, but a little clunky. You have to deal with directory permissions, figure out how to handle file bloat (what happens when the number of files you’ve dumped gets really big?), and so forth. Possible, but not ideal.
- Use Open URL and run a script in the target database. This works, but it does involve opening the target database on the client, and window management gets kind of ugly.
So where does that leave us? Fortunately, we have a couple of new developments in FileMaker 16 and 17 that give us a very workable fourth option. Version 16 brought us JSON parsing functions (which we’ve mentioned before), and a beta version of the FileMaker Data API (Application Program Interface). Version 17 gave us the production version of the API. Now, we have a very effective way to push data from one database to another. Here’s how it works:
- Open an API session in the target database. This basically involves obtaining an access token from the server, which is used for all actions you take during that session. (Tokens expire 15 minutes after their last use, to prevent sessions from stacking up on the server and to prevent security issues.)
- Perform another API call to push the data to the target database. This could be either creating one or more new records, or updating an existing record. (The API supports several other functions, but we’re only interested in delivering data at this point.)
- Close the API session. While not strictly necessary (remember tokens and their sessions expire 15 minutes after last use), it’s a good practice to clean up after yourself.
This workflow is perfect for our configuration. We never have to open the target database on the client (all the work is done on the server), so we don’t have to worry about windowing or user accesses. The API directly accesses the database itself, obviating the need for directory permissions or sharing. And best of all, it’s blazing fast.
To make the process smoother, we implemented a data queue inside the database. That way, when we push data in, all we have to do is create one record in that table. The queue record contains a JSON object with all the data to be updated. A server script will come around every few minutes and process any outstanding queue records.
I’ll add another post a little later, detailing how we structured process and some of the details of the coding involved. In the meantime, please contact us if you’d like to hear more. Happy FileMaking!
