FileMaker Pro Recursion PT 3 - A Wrinkle in Timeliness: The Final Reckoning, By Andy Persons

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. Recursion requires that each iteration maintain it’s “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.This is not the end, there is more! Download the full article by Andy in PDF from Excelisys’ Tips-n-Tricks page!

Andy Persons is a Senior Lead FileMaker Pro Developer with Excelisys: Andy has been an industry leading FileMaker Pro developer creating FileMaker Pro solutions for over 17 years. In addition to being one of the lead developers of three top-rated and most-downloaded FileMaker Pro solutions of all-time; the FileMaker Business Tracker and the Excelisys eX-BizTracker & eX-BizTracker Pro jump-start solutions, he has shared his incredible and advanced talents by authoring numerous Tips-n-Tricks files and white papers, including Hierarchical Portals, Recursive Calcs, Audit Logs and Drag-and-Drop using FileMaker Pro.

*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.

Source