[This article assumes that the reader is familiar with part 1 and part 2 of this series.]
The other day, I ended part 2 of this series by looking at the y-axes of a variable-array-based chart, and commenting, “Note that all 52 variables for each of the 12 data series are individually enumerated. I’m thinking that there is a smarter way to go about this, but that is code for another day.”
Well, now I’m thinking that “another day” has arrived.
Here’s a tip I picked up from Steven Blackwell the other night on Friday Night FileMaker Chat. FileMaker 11 doesn’t provide an obvious way to prevent a user from interacting with a chart in browse mode (e.g., copying, clicking on, dragging, or mousing over to view tooltips).
Last week we looked at building a FileMaker 11 native chart based on data that has been parsed into a field-based array.
I happen to like field-based arrays, for reasons I’ll get to in a minute, but is a field-based array really necessary to generate the above chart? Continue reading “Array Charting, part 2”
28 Dec 2020: for further thoughts on this subject see Set Variable By Name Revisited.
6 Apr 2016: A “SetVarByName” custom function can make things a whole lot easier. For details, see Set Variable by Name.
31 Oct 2014: This was originally embedded inside another article, but I now realize it should have been a standalone post, so I am belatedly taking corrective action and making it so.
Although FileMaker provides a “Set Field By Name” script step, it does not provide a direct method to dynamically declare the name of a variable.
Note that I said there isn’t a “direct” method to accomplish this… but it can be done indirectly.
Creating Variables on the Fly
There’s something cool and slightly miraculous about being able to dynamically name and populate variables at runtime. In the code below we don’t care about $x at all — it’s just a convenient way to access the calculation engine so we can invoke the Evaluate and Let functions.
We’ll come back to that in a minute, but first let’s consider a much simpler example. If we know the name of the variable we want to declare, but want to specify the repetition dynamically, it can be accomplished in a very straight-forward manner, like so:
This gives us a variable named $array_01[x] where “x” represents whatever number is currently in the $rep variable.
The following also achieves the same result, but normally we wouldn’t bother since it’s more work with no additional benefit. But it does show that a variable can be defined via Let, with the rep specified dynamically — in this example, via a variable, $rep, but Get(RecordNumber) or any calculated expression would work.
However, the technique breaks down if we try to dynamically specify the name of the variable itself. In the above example, we’re telling FileMaker to define a variable named $array_01 with whatever repetition number happens to be sitting in the $rep variable.
But we want to declare a variable named $array_xx where “xx” could be anything from “00” to “12” and is based on the value sitting in the $column variable (padded with a leading zero where appropriate), and to pull that off we need to bring out the heavy artillery, i.e., Evaluate.
It makes my head hurt, but it actually works.
In recent postings we’ve looked at…
While these three techniques may not appear to have much in common, all of them are used as building blocks in today’s demo, line-chart-from-field-array. Our data set is a table of web site visits, by week and by state, beginning in March 2006 and running through June 2011.
Wouldn’t it be nice to to use FileMaker 11’s built-in charting capabilities to produce a line chart showing weekly visits per year? Continue reading “Array Charting, part 1”
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”
