Simple FileMaker Transactions Video

This video is part 2 of a series on FileMaker Transactions. Part 1, titled “Fixing Slow FileMaker Reports” demonstrated one of the many reasons why transactions are an important part of building multi-user FileMaker apps.  In this video, we break down the simple FileMaker Transaction that we briefly see in part 1.  The video continues with a brief introduction to the low level FileMaker features that are used to build transactions safe scripts.

We are offering an online course on FileMaker Transactions. Sign up to be notified when the next session starts up.

Download Demo

This is the demo file used for this video

Video Series

 

Fixing Slow FileMaker Reports

This video is part 1 in a series on FileMaker Transactions and why you should use them. In this video, we look at a slow FileMaker Report. The report is slow because it is based on unstored calculations. We look at a way to speed up the report, by storing data closer to where the calculation needs to occur.  This lets us create our report using less unstored calculations. The result is a report that is much faster.

However, this opens our solution up to database integrity problems because no we have the same data stored in many places. In theory this is a bad practice. But in the real world of multi-user FileMaker applications running over the internet, it turns out to be something you will need to do.

We solve this problem by employing FileMaker Transactions to make sure that we can reliably keep the de-normalized data in sync.

Part 2. is now available. Simple FileMaker Transactions

We are offering an online course on FileMaker Transactions. Sign up to be notified when the next session starts up.

Download Demo

This is the demo file used for this video

Video Series

Building Multi-User FileMaker Apps

Building Multi-User FileMaker Applications FileMaker is a great platform for building multi-user applications. It’s easy to get started, but powerful enough to build applications that can run an entire business. One of the reasons it is so easy and powerful is that it does a very good job of handling the complicated issues that arise from allowing many users access to the same data over a network.  However it can’t do it all by itself. It’s up to the applications […]

FileMaker Transactions

FileMaker Transactions

Although it isn’t talked about much, FileMaker does have solid support for database transactions. You can write scripts with full commit and rollback support, just like MySQL and most other Relational Database Management Systems (RDBMS). This is a Very Good Thing. Without this support it would be very difficult to build robust multi-user apps with the FileMaker Platform.

FileMaker Transactions are not explicit. There is no “Start Transaction” script step. Rather, they are implicit under certain conditions. If you know how to set those conditions up, your FileMaker script will use database transactions to save your changes to the database. The result is much more robust, and scalable solution.

Anytime you need to edit more than one record with a script, you need FileMaker Transactions!

Why do you need FileMaker Transactions?

I went into detail in another article about the harsh realities of building multi-user, networked apps, but the short answer is that without them you can’t be certain about what happens when a FileMaker script runs. A networked multi-user application can run into many problems that stem from its very nature. It’s an application where multiple users may be fighting over the same records, with rules that may prevent editing, over a network that may die. To make matters worse, the operating system may force the script to abort, or the computer may crash, or the application itself may crash.

Transactions can’t stop these bad things from happening, but what they can do is make sure that the integrity of your database is not adversely affected when they do. They do this by saving all the edits you want to make to the database into batch, and then committing that batch in single atomic operation. That operation is guaranteed to either completely fail or completely succeed. Partial success is not possible.

Complete success or complete failure are both acceptable outcomes. They both provide complete certainty about the state of your data. There is no need to guess or to try to determine what changes succeeded and what didn’t. The data in your database moves from one known state to another known state. Database transactions make it impossible for some in between state to occur.

History of FileMaker Transactions

FileMaker has had good support for transactions since FileMaker 7. If you could look back in time and see the press releases for FileMaker 7, you won’t find any mention of “Transactions”. It wasn’t talked about as a feature at all. But some of the new behaviors hinted that some major changes had been made under the hood effecting how FileMaker saved data to the disk.

The behavior change that tipped me off was with Revert Record. Starting in FileMaker 7, Revert Record, reverted changes to all the edited related records. This was a major change. Prior to 7, the most records you could have open at any given time was three. If you edited a record in a portal, you locked open that record, the parent record, and the first record in the portal. Therefore you could only ever undo the changes made to three records. But with FileMaker 7 you could have unlimited open related records, and Revert Record would revert all of them.

I suspected that the most likely way for FileMaker to do this would be through the use of true database transactions. At DevCon, I asked Sean Flisakowksi, who was then one of the lead engineers at FileMaker, if this was true. He confirmed that it was. FileMaker’s new database engine was indeed using true database transactions to save data to the disk.

Using FileMaker Transactions

FileMaker Transactions

The key is to make changes to your database through relationships. Changes made to related records are cached until the parent record is committed. At any time, you can use “Revert Record” to rollback the changes you have made to the parent record and all its related records. When you commit that record, all the changes you made to all the related records will be committed at once.

You can create records through relationships when the “Allow creation of related records…” check box is checked. Many FileMaker users know about using a portal setup on such a relationship to create new records. You can use a script to Go To Portal Row Last and set fields on the other side of the relationship. But there is another way as well. You don’t need to use a portal as long as there are no related records in the target table yet. Setting a field through that relationship will create a new record as well. There are some very interesting things you can do with that little trick.

FileMaker Transactions

Editing records through a relationship is easy. All you need is relationship to the record you want to edit. If you don’t have a natural relationship to the record you want to edit, just create one. You can create a relationship to any record in a table if you have its primary key. I call these special purpose built relationships “ad hoc” relationships. I posted a Transactions module to Modular FileMaker that has my code for using up these “ad hoc” transactions.

You can delete records through a portal as well. You will need to make sure that “Allow deletion of portal records” is checked in the Portal setup. Use the “Go To Portal Row” script step to select the related record you want to delete. Then use the “Delete Portal Row” script step.

Operations that don’t use FileMaker Transactions

Batch edit operations, like replace and import, do not use FileMaker transactions. If something happens halfway through either of these operations, some of the changes will have been committed and some won’t. If a replace encounters a locked record it will not be able to edit it. You will get an error. But it will not tell you which record wasn’t edited.

Importing records does not use FileMaker Transactions

If your script does anything to commit the record before it gets to the end, you will break the transaction. You will want to avoid Script Steps that implicitly commit the record.  These include but are not limited to Go To Layout, Enter Find Mode, Close Window, and Go To Record.

Stay on one layout. Create the relationships you need to target the tables and records you need to. Do that and you won’t have any trouble keeping the records open until you are ready to commit them.

Caveats

FileMaker Transactions are very solid. But there are a couple of caveats. If the server crashes at just the right moment, a transaction may be damaged.  But as long as a the server doesn’t crash you can be confident that everything worked.

You may have to change the way you think about writing FileMaker scripts. Doing everything in the context of a single layout and under lying table occurrence does take some getting used to. Sometimes you will have to get creative. But I have yet to find a scenario that I couldn’t make into a single transaction.

Flexible, Scalable, Fault Tolerant

You can create, edit, and delete as many records as you want in a single FileMaker Transaction. You are only limited by the memory constraints of the machine that is executing the transaction safe script. You could create a million records, edit a million more, then commit everything in a single operation. Or revert the whole thing and the database will be left untouched.

Using transactions will make it easier for you to use techniques like de-normalizing and posting to speed up your database. They will reduce the number of unexplained issues you have. They will make your complex, multi-user, networked apps better able to survive record locking, crashes, and shaky networks. They may even help you sleep better at night. 🙂

FileMaker Transactions work on all FileMaker Clients including FileMaker Pro, FileMaker Go, WebDirect, Server Side Scripting, and Perform Script On Server.

FileMaker Barcode

Announcing Barcode Creator

Announcing Barcode Creator

Geist Interactive is excited to release Barcode Creator v1.0 for FileMaker. Barcode Creator can generate 14 different types of barcodes  all without using plugins, fonts, or web services. It’s pure, 100%, native FileMaker. It works on the entire Filemaker 13 platform, including WebDirect. FileMaker 12 is supported as well. Now you can make and scan barcodes with FileMaker Go.

Easy to Use

There is nothing but FileMaker code to deal with. You don’t have to worry about problems installing fonts, or plugins, or connecting to web services. Barcode Creator doesn’t even need a connection to the internet. It takes seconds to install, and just a couple of minutes to generate your first barcode. Extensive documentation and video instructions are available.

Try it Before You Buy ItFileMaker Barcode

Download a demo file that lets you see just Barcode Creator in action. When you buy it you get the fully unlocked file containing all the code and examples of how to use it.

Pricing

Barcode Creator is available starting at $199 for a Site License. FileMaker Developer Licenses and Redistribution Licenses are available as well.

Barcode Creator is a collaboration between Jeremy Bante and Geist Interactive.

Learn more…

Using BaseElements FileMaker Plugin to Generate Code – Video

BaseElements FileMaker Plugin

BaseElements is very powerful free plugin from Goya . Among it’s dozens of useful features is the ability to create FileMaker code in the form of ClipBoard objects and place them on the clipboard so you can paste them into your solution.  We used this to add a hidden feature to our GoSign Configurator to make integrations even easier.

Read more

What Can WordPress Teach FileMaker?

WordPress is the dominant web platform on the planet.  It’s ecosystem is orders of magnitude larger than FileMaker’s. Clearly they are doing something right.  Is there anything that FileMaker and FileMaker Developers can learn from WordPress’s success? Read more

Announcing GoSign v3, Scriptable, FileMaker Signature Capture

FileMaker Signature Capture

Newbury Park, California, Feb 5th, 2014 – Geist Interactive is pleased to introduce GoSign 3.0 for FileMaker Go and FileMaker Pro. GoSign 3.0 is a major rewrite of the popular FileMaker Signature Capture add-on for FileMaker Go. GoSign allows FileMaker Signature Capture to happen anywhere in the layout, including in new FileMaker 13 PopOver Controls and Slide Panels. Read more

FileMaker Unit Testing Video – Make Collaboration Easier

Tim Anderson reported an awesome bug with HyperList version 2.0.  I wanted to jump right in there and start fixing but the main HyperList script is a bit complicated. So you need to be careful.  One of the ways I try to make sure that I don’t break existing codes, is to use FileMaker Unit Testing.  I also use unit tests to make sure that I have actually fixed the bug. In this video, I take a look at solving Tim’s bug using FileMaker Unit Testing. I wrote about this a while back, but I thought this would be a good opportunity to bring up the subject again.

Read more

Clearing DreamPress Cache In One Step

DreamPress is a supercharged, optimized for WordPress VPS from Dreamhost.  We run geistinteractive.com and modularfilemaker.org on it. It uses Varnish and Memcached to keep WordPress just zipping along. Overall I’d say we are pretty happy with it. But when you are developing your site, the aggressive caching can really throw you.  I have sent a lot time yelling at the browser, trying to figure out why something wasn’t showing up as it should.  I finally figured out a good way to deal with it.

Read more