FileMaker Pro Recursion PT 3

By Andy Persons:

*This article is part 2 of a 3 part series: part 1, part 2.

In my first post of this three part series, I posed a challenge: how do you update hundreds or thousands of tasks interconnected with tens of thousands of connections? In my second installment, we explored a possible solution: recursively looping through each task from the end-level tasks, tracing each one to its source, then returning back down the stack and updating the dates along the way.

However, one issue was still in the way. FileMaker recursion requires that each iteration maintain its “scope.” In this case, the current record and found set would have to be unique for each iteration. This would mean each iteration would have to open a new window; the proliferation of hundreds of windows would be excruciatingly slow and potentially would crash FileMaker.

In this final installment, we’ll explore an elegant solution that adds lightning-fast speed, avoids crashing FileMaker Pro and includes a free set of Ginsu knives if you read now*! *Ginsu knives not included, additional processing and handling charges may apply.

A Virtual Planetoid

The solution lies in caching (sometimes referred to as virtualizing). In general computing, caching refers to storing a duplicate of data in a more quickly accessible location. In FileMaker, caching usually refers to temporarily storing record data in global variables.

In our scenario, we’ll cache the dependency data (which tasks each task is dependent on) and the offset data (how long each task will take). This will allow us to do all our calculating without touching the database again until the very end. The benefits of this are twofold:

1. We can maintain scope in each iteration because we will no longer be relying on the FileMaker found set. We’ll be able to use the script’s inherent scope, in essence maintaining our own “found set” by storing a list of record ids in local variables.
2. Because we don’t touch the database engine until the very end, this approach is lightning fast. In my implementation, all tasks are updated within 1 to 2 seconds.

Download the article in its entirety as a PDF

*This article is provided for free and as-is, use and play at your own risk – but have fun! Excelisys does not not provide free support or assistance with any of the above. If you would like help or assistance, please consider retaining Excelisys’ FileMaker Pro consulting & development services.

* FileMaker and FileMaker Pro are registered trademarks and owned by FileMaker, Inc. in the US and other countries.