Welcome to the fourth and final installment in our series on Radical Separation. Today’s article assumes familiarity with parts 1, 2 & 3, and continues in the direction we were headed at the end of part 3. Specifically, we will finish up our exploration of “virtual calculations” by examing an intriguing approach suggested by Barry Isakson to a) reduce the field count, b) solve the “define more fields than you’ll ever need” problem, and c) accommodate summary fields, and I invite you to follow along in today’s demo file, Virtual Calculations, Part 4, if you are so inclined.
Last October I began a series on Outer Joins, which explored a number of different ways to display summarized information in a grid, and at the time I concluded that the “fastest” method was to leverage the FileMaker relational model. And it was plenty fast, locally… and not too bad on a LAN… and technically, it was the fastest method on a WAN but only because the other methods we looked at were even dog-slower than it was.
I like to test solutions on a WAN, even if they’re only going to be deployed on a LAN, because it’s a great way to uncover performance bottlenecks. But recently I needed to deploy a summarized grid on a WAN, and was incentivised to come up with something faster… and after a bit (well, okay, a lot) of trial and error settled on the approach we’re going to look at today. To cut to the chase, with a million records in the test file, the previous best grid rendering time of 11 seconds on a WAN has been reduced by a factor of 10, to just over one second.
Today we’re going to delve further into the concept of virtual calculations, picking up where we left off last time, and with the assumption that readers are familiar with the material in part 1 and part 2 . We’ll look at some ways to make this technique less brittle (prone to breakage if objects are renamed), and also less opaque to DDR analysis tools such as BaseElements and Inspector. We’ll also see if the technique can be applied to auto-enter calc fields, and finally, we’ll explore some ways to make the technique easier to implement.
In part 1 of this series, we defined radical separation as a separation model scenario in which the developer no longer has access to a data file once a solution had been deployed. Updates to the solution are delivered in the standard separation model manner: by swapping in a new interface file.
We explored the concept of “virtual calculations”, where certain (unstored) calculated fields in a data file derive their definitions from syntax stored as data in a special table in the interface file. The advantage of this being that calculation logic can be redefined programatically by the simple expedient of replacing the interface file.
In the six weeks that have gone by since I posted part 1, I have made a couple improvements to the technique, one of which which we’ll examine in today’s demo file: Virtual Calcs, Part 2
I’ve said before, and no doubt will say again, that one of my favorite things about this blog is how much I learn from your feedback and the demo files you send me.
Recently I received a file from Otmar Kramnis of the Hochschule Luzern demonstrating the fastest SQL-based method I have yet seen to solve the challenge we looked at in part 1 and in part 2, and with a few minor modifications, this is the demo we’re going to focus on today: Outer Join Demo 7
As you may recall, the aim is to show a week’s worth of daily sales totals for all employees whether they had any sales or not.
Or, to restate the problem in more generic terms: we need to show all values from table A, whether or not there are any matching values in table B. This is known as an “outer join”, or more precisely a “left outer join”, since we want to see all values in the “left” table (Employees), whether or not they have corresponding matches in the “right” table (Sales).
Earlier this month I had the honor and the privilege to do a presentation on the topic of Radical Separation at the PauseOnError un-conference in Portland, Oregon, which included a demo file resembling this one: virtual-calcs-part-1-v2
Before the conference I posted a pseudo-F.A.Q. which included the following…
- Q. What’s your experience with the Separation Model?
A. I’ve used it heavily over the last seven years, for a variety of vertical market applications, custom projects and, recently, on a vertical market FMGo app.
- Continue Reading →
Has this ever happened to you? You have a number of identically named fields in two different tables…
…but when you copy one of those fields from a layout based on the first table…
… and paste it onto a layout based on the second one…
…the result is not what you might have wished for.
A while back my youngest son, who is an avid Go player, asked me, “Is it true there are more possible Go games than there are atoms in the universe?”
“Absolutely,” I replied, “Let’s fire up FileMaker Pro and prove it.” (I wasn’t about to let a rare teachable moment slip by.) “If memory serves, the number of atoms in the universe is estimated be roughly 10^80. Since a Go board is 19 by 19, in theory there are 361 possible first moves (19^2), followed by 360 possible second moves for each of those 361 first moves, followed by 359 possible third moves for each of the 360 possible second moves, and so on. Therefore the total number of moves can be calculated as 361 x 360 x 359… x 1, or, more simply, 361! (i.e., 361 factorial).” Continue Reading →
Recently I received a dual-technique FileMaker 12 demo from Eden Morris. Here’s what he had to say about technique #1:
In the Relationships Graph I show the use of colored labels to indicate where record creation, cascade deletes, and sorted relationships are enabled. Looking at the graph it easily shows that I can create customers from a company table but that they don’t cascade delete, you can create invoices and invoice lines from a project and the invoices and their lines cascade delete, and that the invoice lines are sorted.
It’s an elegant way to display this info (but for some reason, it makes me crave jelly beans).
FileMaker has always been an incredible tool for generating reports and for many years, I used to have a report screen with banks of buttons to generate all of the individual reports. In the last few years however, I’ve added a global field, to the footer of each module data entry screen, with a pop-up menu, listing all of the different report options for the module in question. To that global field, I’ve added an onObjectModify script trigger which sets the name of the report as the parameter that generates the report they want. Simple, very effective and it has the added advantage of giving the user all the reports that are relevant to where they are at the time.
Recently I had a project where some of the reports that were required were restricted to the different privilege sets and I was looking for a quick (and easy) way to accomplish this. I started thinking about whether I could use a related value list to solve the problem and came up with, what I think is, an elegant and very simple solution.
The first thing I did was create a Reports table with just two fields, Group (for the privilege set) and Report (for the name of the report itself).
- Kenneth on FM 12 ExecuteSQL: Dynamic Parameters, pt 1
- Kevin Frank on FM 12 ExecuteSQL: Robust Coding, part 1
- Ben on FM 12 ExecuteSQL: Robust Coding, part 1
- Benjamin on Exporting Data to iCal, Outlook, Google Calendar, etc.
- Benjamin on Exporting Data to iCal, Outlook, Google Calendar, etc.
- June 2013
- May 2013
- April 2013
- March 2013
- February 2013
- January 2013
- December 2012
- November 2012
- October 2012
- September 2012
- August 2012
- July 2012
- June 2012
- May 2012
- April 2012
- March 2012
- February 2012
- January 2012
- December 2011
- November 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- March 2011
- February 2011
- January 2011
- December 2010
- November 2010