Last time we looked at several summary reporting tricks, including a conditional subsummary (when an item’s Status is “Scheduled” it will have a value in the Substatus field — otherwise Substatus will be blank). The challenge was to generate a summary report showing Substatus only where appropriate, without seeing any annoying empty gray rows beneath Pending, Cancelled or Completed. And last week’s report worked fine… in preview mode.
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).
Ever since I posted part 2 of this series, I’ve been torn between, on the one hand, wanting to move on to other topics, and on the other, the realization that I wasn’t quite done with this one yet. So, here is what I expect will be my final posting, and final demo (dynamic list sorting, v3 rev5), on this subject.
Thus far, we’ve looked at various methods to facilitate dynamic list sorting (by “dynamic” I mean that the field to be sorted is determined programatically). Most of these methods use two fields — one of them uses four — and you can see them all in part 2.
But in the back of my mind has been the knowledge that Ugo Di Luca pulled this off with a single field back in 2004 (EasySort.fp7, shared by permission of the author, and previously discussed last April in an article entitled Portal Sorting, pt 3).
Well, I thought I’d said everything I had to say on this subject, but yesterday afternoon I noticed a glaring omission in part 1’s demo — what happens if the user manually unsorts the found set?
The sort indicator doesn’t disappear the way a good little sort indicator should. Fortunately this is easily remedied, thanks to the Get(SortState) function. Continue reading
Earlier this year, I posted a three-part series on Portal Sorting, and part 2 focused on dynamically sorting a portal when a column heading was clicked. Well, with just a few tweaks, this technique can be applied to dynamic sorting of found sets, and of course the most likely place to employ something like this would be on a list view.
I should note that on very large found sets, sorts using this technique can be noticeably slower than traditional “hard-coded” scripted sorts. (Performance is fine with normal found set sizes.) The benefit of using this technique, is that a new field can be added to a layout and sort-enabled in about 60 seconds without touching the script itself.
One of my favorite aspects of FileMaker Pro is reporting, and I appreciate it when a client poses a reporting challenge, because the phrase “we can’t do that” is not in my vocabulary (except when used as an example of a phrase not in my vocabulary). But recently I was given a reporting request that momentarily tempted me to utter those forbidden words.
The request: For a given date range, display and summarize prizes by category, and by type within that category, with each category starting on a fresh page… so far so good, right? The hierarchy is Prize Category → Prize Type, and FileMaker developers can crank out reports like that in their sleep.
But then came the twist: show different columns of information depending on category and type. And yes, the client wanted this all in a single report.
I mentioned this technique in passing a few months ago (Portal Sorting, part 2), but today I would like to examine it in greater detail. Have you ever had a text object or a field that you wanted to selectively change based on some logical condition? For example, say you have a check box, and want the label next to the check box to change depending on whether the box is checked or not, like so:
In the old (pre-FileMaker 9) days, you could have used auto-enter or calculated field trickery, but now, thanks to the modern miracle of Conditional Formatting, you can make this happen at the layout level, rather than having to pollute your table schema.
The other day we looked at static portal sorting, where the developer decides in advance how the portal will sort, and “hard codes” those settings into the portal. Sometimes, though, we want to provide users with an interface where they can dynamically sort a portal by clicking on column headings…
…and we’re going to look at a technique to accomplish this today. Continue reading
Demo file: 2010-11-21-count-unique.zip (requires FM 10 or later)
Yesterday we looked at a simple method to flag unique entries in a found set. This time, we’re going to look at an additional use for this technique, using the same data set and demo file as last time.
As you may recall, we have a simple table of sales data, and previously we produced a summary report sorted by salesperson, but reordered by total sales, so that the top performing salespeople appeared at the top of the report.