“If you haven’t made one, it’s tough to imagine just how difficult it is. Every detail requires deliberation. There are many details. I will spare you an enumeration. But believe me when I say, if you think about them all before you start, you will never start. The rabbit hole is deep. The truth of any craft.”
It’s fast and convenient to have the FileMaker help file bookmarked, e.g. for quick access to the functions reference and script step reference.
In FileMaker versions 7 – 12, you would find the help file here:
This has changed in FileMaker 13. The help file is now found here:
The examples above are for the default location of FileMaker Advanced. FileMaker Pro would be slightly different.
To get to an application’s Contents, control-click or right-click it and choose “Show Package Contents.”
In the Help folder, open the index.html file in your browser, then bookmark it, or navigate to your reference section of choice and bookmark that.
An old trick but still a good one.
Hello to all my friends and colleagues in Miami this week! I’m sorry I can’t attend DevCon this year, but I look forward to hearing all the latest and greatest FileMaker news. As a consolation prize, the weather in Portland is beautiful and OSCON is here, right down the street from my office. I’m not attending the official sessions, but I plan to spend some time hanging out with the Big Data guys and healthcare IT folks. Should be interesting.
Sometimes we need to format numbers in a block of text, to send an email for example. We want comma separators:
1,000 — not 1000.
There is a built-in function, though, that is all you need if you just want to separate the digits with commas: NumToJText
NumToJText ( 1234567 ; 1 ; 0 ) gives us 1,234,567. Nice!
I got some great responses to my last article, and I’d like to share a few of them with you (with permission from the developers).
First up, from Chris Manton of BabelFix: rather than gathering the child IDs with a script, he set up a separate sorted relationship for each sort order. This lets you use a simple List function to instantly grab the list of IDs.
Great idea, but it turns out, testing with thousands of related records, that the slow part is not gathering IDs, but the portal’s Sort that introduces the bigger delay. (And remember, the field we’re sorting on is an unstored calculation.) Yes, the List function is king of speed, but you won’t notice the difference in portal sorting unless your related record set is in the hundreds or thousands.
Chris, undaunted, figured out a way to take the portal’s Sort out of the picture! He used the virtual list technique that Bruce Robertson developed. The portal displays the “virtual” records, which are already sorted, so the portal doesn’t need to do it.
If you sort thousands of records in portals, this is a killer technique. It’s really two techniques: 1. gathering the IDs using sorted relationships and the List function; and 2. a non-sorted portal that uses virtual records. Part 1 is useful, but does require an extra relationship in your graph for each sort order. It’s part 2 that’s the real breakthrough in terms of speed for large record sets.
Our next example is from Bruce Robertson of Concise Design, who pioneered the use of virtual lists, and naturally that’s the meat of his implementation. What’s interesting is that his approach is kind of the inverse of Chris’s: he uses sorted relationships, but on the virtual records. He also demonstrates a really clever way of grabbing the data by evaluating a script parameter.
Last but not least is a file modified by Matt Petrowsky of FilemakerMagazine.com. What you notice immediately is how great it looks. Subtle shading in the portal header, reverse shading on the header’s separator lines, and neat little sort direction arrows. Even the font sizes are just so.
Behind the scenes, he kept essentially the same simple relationship graph, with one important change: an additional “refresh” criteria in the relationship. You can read more about how that works at FileMaker Weetbicks. Essentially, it handles refreshing the portal without the screen flashing that comes with a refresh script step.
Matt also adds ascending/descending sorts, and uses some interesting custom functions. He uses one for the portalSort field. He uses another with GetNthRecord to create the child ID list. (As I mentioned in the comments of the previous article, GetNthRecord is not a problem for small sets of related records.)
Ultimately there is no single “right” way to sort a portal in FileMaker. The technique you choose depends on where it will be applied. For now I’ll be sticking with my original approach, it doesn’t require any changes to my graph (although I’m going to play around with the relationship-based refresh) and the setup is straightforward enough that other developers I work with should be able to make use of it without much trouble.
Many thanks to Chris, Bruce, and Matt, and everyone who gave feedback on Portal Sorting that Doesn’t Suck. Have fun digging into these files!
I am working on a FileMaker project that uses a “home” layout featuring a portal. The portal’s “parent” is a global text field containing a list of ID numbers. It’s related to an ID field in the child table. Nothing too unusual.
Then I started looking at sorting the portal. One nice thing about a list or table view is that it’s easy to give your users the ability to change the sort order on the fly. A portal’s sort order, though, is “baked in.” There’s really only one way to sort a portal: via the fields you specify in the portal’s Sort options in layout mode.
What if you want to click a column heading to sort? If you’ve used other dynamic portal-sorting techniques, you know they can be somewhat cumbersome. My thought was: I already have a list of child records (which I loop through in a script to populate my global text field). Why don’t I just sort these records before I loop through them? My ID list will then be in the order that I want.
Not so fast — the order of the IDs in the global field is ignored by the relationship. The records still display in an unsorted portal in the order they were created. Likewise, the global ID list can’t be used by the portal’s sort options.
I decided to try an experiment: what I needed was a field in the child table that calculates its position in the list of IDs. Then I could just sort the portal by that number.
Now, FileMaker lets us count the values in a list, filter values, and determine the position of a character. What it doesn’t provide is a function to tell you on what line in a list a certain value appears. That is what this calculation does:
Let( [ idList = ¶ & $$gIDs & ¶ ; // substitute your global var or field id = ¶ & ID & ¶ ; // substitute your ID field end = Position ( idList ; id ; 1; 1 ) ; cutList = Left ( idList ; end ) ; result = ValueCount ( cutList ) ]; result )
For example, let’s say our ID is “XYZ,” and it’s 4th in a list of 10 items. We first chop off all the text that follows XYZ (items 5-10). This leaves us with items 1-4 (“cutList” above), and XYZ becomes the last value in the list. Now its position in the list is the same as the number of values in the list: 4.
All that’s left to do is sort the portal by this field. It works!
To recap, you will need:
- A script that gathers the child IDs into a global variable or global text field, and does a sort.
- A calculated field in your child table that tells you where its ID is in the global list of IDs.
- A portal that is sorted by the calc field.
The great thing about this technique is that all the logic and control is script-driven, which makes it easy to set up and to make changes down the road. You will not be incurring technical debt.
This technique can be extended in all kinds of fun ways with conditional formatting, script triggers, etc. You can even allow your users to do a custom sort. Yes, a custom-sorted portal! See the example file for a basic implementation.
I would say that this technique lends itself best to an interface that is driven by a single-record/global ID list. I.e., we gather IDs as in step 1 above, not just to sort, but also to determine which records are displayed, using a relationship from a global ID field to the ID field in the child records. Otherwise, you’d need to trigger the sort as you navigate from record to record, and possibly do additional housekeeping to keep track of each record’s sort order. Multiple windows might also present challenges.
A few last points to keep in mind:
1. The calculation above should be set to Unstored.
2. If you use a global field (rather than a variable) to store the IDs, make sure its type is Text — a number field can’t store a list.
3. It seems, based on my limited testing, that when the calculated sort field is driven by a global field, the sort script does not require a window refresh step. When the calc references a global variable, it does require a refresh.
4. My friend and fellow FileMaker developer Matt Navarre pointed out that we don’t necessarily need to know the value number of the ID in the list — although you may want it for other purposes — it’s sufficient to simply know the order, which can be accomplished with the position function alone. Instead of a neat 1, 2, 3, 4, the result will be something like 1, 6, 11, 16, but gives us the same sort order:
Let ( [ idList = ¶ & $$gIDs & ¶ ; // substitute your global var or field id = ¶ & ID & ¶ ; // substitute your ID field result = Position ( idList ; id ; 1; 1 ) ]; result )
Download the sample file.
(Edit: 5/25/11 -added Freeze Window script step)
I look forward to your comments.
If you found this interesting, be sure to read the followup article.