Editor’s note: for additional thoughts on this subject see Fast Summaries Revisited and Fast Summaries Re-revisited.
Have you ever wished you could pull the values from a summary report, and use them in a script or a calculation? Back in the FileMaker 6 days, Mikhail Edoshin introduced a technique to do just that.
He called the technique Fast Summaries, and I came to employ it extensively in my own solutions. Continue reading “Fast Summaries”
25 Feb 2012: Custom function syntax corrected to fix a minor bug.
There are various FileMaker functions that we can use to extract one or more characters from a text string (or any data string for that matter), provided we supply the proper numeric coordinates. For example,
Middle ( "Literature" ; 4 ; 3 ) = "era"
or
GetValue ( "Winter¶Spring¶Summer¶Fall" ; 3 ) = "Summer"
The key is knowing the numeric address of the data we wish to extract. In some cases we know it in advance (e.g., the first item of a return-delimited list); in other cases we calculate it on the fly. Continue reading “ValuePosition: The Function FileMaker Forgot”
Not long after FileMaker 7 was released, I happened to mention to one of my colleagues (Ilyse Kazar, in case you were wondering), “Now that auto-enter calcs have gotten so much more powerful, there’s really never a reason to use a lookup ever again, is there?”
“Actually,” she replied, “there is one thing that you can do with a lookup that you can’t do with an auto-enter calc…” and proceeded to tell me what it was. I promptly built a primitive prototype of today’s demo file, to see for myself, and she was indeed correct.
At first blush, it appears that every feature of the venerable FileMaker lookup option for fields can be replicated via auto-enter calculation — until you attempt to populate repeating fields that is.
If you ever need to set or clear a whole bunch of reps at once, you will appreciate how easy a lookup can make this operation. Continue reading “Lookups and Repeaters”
7 June 2015: This technique has been substantially revised to work with FileMaker 14; see FM 14: Separation Aggregation Aggravation revisited.
FileMaker developers take it for granted that calculated sub-total and total fields will update automatically when portal rows are created, edited or deleted. This becomes problematic when one a) uses the separation model, and b) either intentionally or unintentionally holds the parent and related child records open (uncommitted) while editing, but still expects to see these aggregates update in real time.
Today’s demo file is called aggregates-and-separation, and for demonstration purposes it has interface elements in both the Data file and the Interface file. Normally of course, in a separated solution, you wouldn’t have interface elements in the Data file (since that would defeat the purpose of separation). At the left, we are looking at a newly created parent record in the Data file, with a couple newly created portal rows, and the three aggregate calc fields below the portal are tracking the changes in real time. Continue reading “Separation Aggregation Aggravation”
When you consider how easily most common reporting tasks are accomplished in FileMaker Pro, the lack of a built in, clear cut method to count unique values within a given found set seems a bit surprising. But where there’s a will, there’s a way, and in part 1 we looked at a method that required the found set be sorted. Well I have some good news: today’s demo (Count Unique – Three Variations, 6.4 Mb) has no sorting requirement.
Here we have a flat file (single table) of contact information. For any given found set, the primary key (ID) will of course be unique, but each of the other fields will potentially contain duplicates, and we want to be able to quickly count the unique values without having to first sort the found set.
The basic approach can be summarized in a few words: Continue reading “Unique Records Revisited, part 2”
A while back I posted a technique to identify and count unique records. I recently discovered that while the technique is 100% reliable in terms of identifying unique records, under certain circumstances it will fail to count them correctly, due to what I believe is a bug in the way summary fields work. You can easily reproduce the bug by following the instructions in this demo file: broken summary field
Fortunately there are various ways to skin this particular cat, and today we’re going to look at one of them (demo file: broken summary field workaround).
The method for indentifying unique records hasn’t changed. Continue reading “Unique Records Revisited, part 1”
Back in 2009, David Graham released a demo called Spotlight Filter that caused a minor revolution in the FM development world: a) it neatly solved a problem that many developers routinely face, and b) it was an early and convincing implementation of FileMaker script triggers.
The problem it solved was, “How can I provide my users with a lightning-quick, keyboard-driven popup-window to pick from a set of values, for example, to add a customer or a product to an invoice?” Continue reading “Popup Pickers”
Recently I asked Jason DeLooze about something that was puzzling me. As usual I found his comments illuminating, and with his permission, am sharing them here.
KF: Why does the following evaluate as true (i.e., return a 1)?
GetAsNumber("") < 0
JDL: There actually is method in this madness. FileMaker numbers, dates, times, and timestamps have a duality nature. Remember that FMP stores these data types as text strings and interprets their value in the proper “space” when needed; that is, in their “text” form when the comparison operator is evaluated in “text space”; as a number value when the comparison operator is evaluated in “number space”, etc.
Continue reading “Space Is The Place: a conversation with Jason DeLooze”
Yesterday we looked at counting all possible combinations of four modifier keys: Alt, Control, Shift and Window (abbreviated A,C,S,W). Today, we’re going to look at a related problem: how many different ways can we combine two of those four keys?
Of course, when your total “set size” is only four, it’s not that hard to identify them (AC, AS, AW, CS, CW, & SW) and then count them (6). But why go to all that trouble when a modified version of Pascal’s Triangle can not only do the work for you, but answer the question for any set size, and any number of choices within that set.
Let’s start with the original and slice off the sections in red:
This gives us a simplified triangle, which we can use to answer the question I posed above: Continue reading “Combinations and Pascal’s Triangle, part 2”
Recently I needed to calculate all the possible combinations for these modifier keys:
Alt, Control, Shift, Window (abbreviated as A,C,S,W)
…ranging from none of them to all four, and every combination in between. I didn’t want to miss any of them, so calculating the total number of possible combinations seemed like a good idea. (As you may have noticed, this was on a Windows computer, and no, I didn’t forget Caps Lock — it wasn’t relevant in this case.)
As it happens, there is a very simple formula, 2^x, where x represents the total number of objects, for counting all possible combinations (including none).
So, the answer was 2^4, or 16, as follows: A, AC, AS, AW, ACS, ACW, ASW, ACSW, C, CS, CW, CSW, S, SW, W and none. Continue reading “Combinations and Pascal’s Triangle, part 1”
