Demo file: boxed-summary-groups.zip
Recently I needed to produce a summary report with variable height rows and each group enclosed in a rectangular box. To make the challenge more interesting, there were multiple fields in the body part, and it was not known in advance for any given record which would be tallest. At any rate, the approach I decided to go with took a bit of trial and error to get working and I figured I’d share the process here in case anyone else finds it useful.
Demo Files
Demo #2 is a variation on demo #1, and uses the contents of a $$variable as the basis for the value list.
Recently I had a need to use a dynamically-generated ascending sequence of numbers as the basis for a value list, and in a multi-user-safe manner. By dynamically-generated, I mean that the list of numbers would be calculated at runtime and would not be known in advance. And by multi-user safe, I mean the methodology would need to allow multiple users to simultaneously and independently populate the value list with their own sets of numbers.
There are a number of ways a developer might go about solving this problem, and my initial plan was to…
a. Populate a global text field with the list of numbers
b. Base the value list on that global field using a technique I picked up from John Ahn at DevCon 2012 in Miami Beach (as described in Magic Value Lists) and which I subsequently explored in detail in Custom Field-Based Value Lists.
The basic idea is to set up a relationship where the “primary” predicate is unstored. In this case I went with a self-join from my Settings table to itself like so…
…though I could have instead used a global field on the left (primary) side… the important things being that the left-hand predicate is not stored and the right hand predicate is stored, and of course that the relationship is valid (in this case we have an equijoin operator and each field = 1).
[Note: a Cartesian join relationship would work here as well.]
Today I want to talk about a potential problem lurking in a small percentage of FileMaker solutions, and discuss various options for mitigating it.
As you may know, there are three ways to display a new window…
…and it’s this last option that can, depending on circumstances, be problematical.
I say “depending on circumstances” because typically it’s okay for a user to spawn a new window, which is to say that doing so doesn’t pose any challenges beyond those the developer already faces when building a multi-user system, which presumably the developer has considered and has intelligently dealt with.
16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.
This is a follow up to last week’s part 6, with three refinements.
16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.
29 Mar 2017: this file (Excel Exports, v6d) fixes a formatting bug when currency values < 1000 are not preceded by a “$”. Explanation forthcoming in next week’s “part 7” article.
Recently I was asked to implement user-friendly Excel exports on a WAN-based solution, utilizing some of the techniques explored earlier in this series (part 1, part 1.1, part 2, part 3, part 4 and part 5 — all from five years ago). There was just one problem: while performance was great locally, and okay on a LAN, it was decidedly less-than-satisfactory across a WAN.
Well today we’re going to look at three demos showcasing a brilliant, outside-the-box technique I picked up from Beverly Voth, which is not only significantly faster on a WAN (4x or 5x faster), but also remedies some other shortcomings associated with my earlier approaches.
Demo files:
Editor’s note: Today I’m pleased to present a guest article and accompanying demo file written by Joel Englander on using ExecuteSQL + a global multi-line key as an alternative to FileMaker’s built-in portal filtering mechanism, with dynamic portal sorting included as an added bonus.
Demo file: fm-sqlportal-filter-and-dynsort.zip (75 Mb compressed, 230 Mb uncompressed)
28 Dec 2020: for further thoughts on this subject see Set Variable By Name Revisited.
29 July 2017: custom function updated to simplify the clearing of existing vars.
16 April 2017: custom function has been updated to auto-interpret dates (rather than treating them as sequential division problems)… e.g., to recognize “4/16/2017” as a date, as opposed to “4 ÷16 ÷ 2017”.
Recently, in the midst of various reporting and charting projects, I’ve found myself wishing for an easy way to “dynamically instantiate” one or more variables… in other words, set them by name, with the name determined programmatically, as opposed to being hard coded.
This issue has come up from time to time on this blog over the last five years or so, most notably in Dynamic Variable Instantiation, where I observed that…
…and then proposed an unwieldy mass of gobbledygook as a workaround, e.g.,
Actually, the preceding isn’t too bad for occasional use, especially if the value you’re passing to the variable is as simple as the GetSummary string shown above. Continue reading “Set Variable by Name”
If you produce reports or other FileMaker output in PDF format, here’s a little trick that can be a real time saver in terms of development.
Imagine this: a client receives a nightly report via email in PDF format, and asks you to make sure it will fit on a single page in landscape orientation. The report always contains three groups of 12 records, with each group preceded by a leading subsummary, a latticework of horizontal and vertical gridlines, etc.
It is a busy, crowded report, but with patience and fine tuning, you manage to get everything to fit on a single page. Just. Barely.
Now the client wants you to add a trailing sub-summary to each group, but of course still expects everything to fit on a single page. The report is already at 8pt type, with tight vertical margins, and you are not looking forward to a bunch of time-consuming heroic measures to accommodate this request, so instead you employ the following trick. Continue reading “PDF Trickery”
The need to isolate duplicates within an existing found set has often been a source of frustration for developers. A couple weeks ago we examined a technique by Ralph Learmont demonstrating that, contrary to popular belief and experience, the ! (find duplicates) operator can, under certain conditions, be coerced into isolating duplicate values within a found set.
Here’s Ralph’s original statement of the problem:
There’s a quirk in the way Filemaker deals with duplicate records. This makes it difficult to find duplicate records WITHIN a found set of records. If you try to constrain the Find to the current set of records, you might discover extra spurious records appearing. These records have “partner-duplicates” outside of the found set. These unwanted spurious records will appear as single occurrences when you inspect a sorted column. Technically they are duplicates. It’s just that their partners lie “outside”, hidden in the omitted slab of records.
And why it’s a cause for concern:
I think it’s important to explain why finding duplicates in a found set is something that often appears to work… and it won’t be apparent there actually IS AN ISSUE unless one goes to the trouble of actually sorting results and checking for those “single-occurrences”.
While the technique was blazingly fast, it seemed to me there was something a bit voodoo-like about Ralph’s previous demo, as well as my attempt at simplification. Apparently Ralph wasn’t satisfied with it either, because he has provided a new demo (Find Duplicates in Found Set) showcasing a more straight-forward technique, and kindly agreed to share some of his thoughts about it as well. Continue reading “Successfully Find Duplicate Values Within A Set Of Records”
Today we’re going to look at a reporting challenge that comes up from time to time, and a technique that can be useful to have in your developer bag of tricks.
In the demo file, Non-Breaking-Groups, we have a simple personnel file containing employees spread across all 50 U.S. states, with between two and nine employees per state.
We have a requirement to produce a report showing personnel grouped by state, like so:
