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:
- 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.
- 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 one to two 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.
We’ve assembled a team of brilliant developers creative problem solvers and consummate professionals, workhorses and web wizards, who work tirelessly to provide you with the best custom solutions to your data management needs. If you’re looking for spineless, Fritos-munching, Mountain-Dew-drinking, living-in-mommy’s-basement, tunnel-visioned order-takers, you won’t find them at Excelisys. We only hire the best.
And you should too.
Our team brings more than 200 years of combined experience in custom database development and management to bear on your solutions. In developer years, just like in dog years, that’s closer to 1,400 years. And 1,400 years ago the Byzantine Empire still existed. Think about it.
We’ve worked with everyone under the sun-from Adobe, the Ayn Rand Foundation, Chevron and Cisco Systems to the University of Chicago, Yoga Journal and Zenith Travel-and they all love us. We know you’re probably thinking, “Those are all huge companies with truckloads of money.” Don’t worry about that; from our perspective, no job is too small! In fact, size really doesn’t matter to us. And unlike most people who say it, we really mean it. Just ask Ted Cheeseman, CEO of Cheeseman’s Ecology Safaris, or Neil King, President of King Shots, Inc., or Todd Golebiewski, Manager of River View Campground & Canoe Livery. You can even call them. Just check our references section and you’ll find their numbers there.
We’ve received the most feedback and some of the highest scores on FindFileMakerDevelopers.com, the data management world’s Service Magic-esque client feedback interface. See what our clients are saying: http://filemakertoday.com/filemaker-integration/item/excelisys?category_id=20.
Call us geeks, but we love solving database management problems. Seriously. And our love for what we do shows in the solutions we provide to our clients. And our solutions make our clients love us back. In fact, we have a 95 percent client retention rate. They just keep coming back for more.Website: www.excelisys.com