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.
Clever thinking!
I am a bit concerned about the performance with a lot of records, but then again, sorting a lot of child records is never fun, even when FM does it itself.
It will definitely be usefull for me, thx again.
Hey there, Mr. Tom!
I’ve tried both of the other techniques and have found the “dynamic-portal-sorting” method to be too slow over a network, and the “hidden tab element/hidden-sorted-portals” technique is a lot of work (compounded when you want to add another sort field).
Thank you so very much for sharing this new technique and the demo file! Very clever! I look forward to implementing the “Fitch Portal Sorting Technique™” immediately! :D
P.S. Thanks also for the new (to me) term, “technical debt”. I will be having less of that now. ;)
We know there’s more than one way to skin a calc in FM.
My personal approach. I’ve made this into a custom calc so that I can determine the index value for a selected item in any given list of separated values like a value list.
Let
(
[
items = $$gIDs ;
items = If ( Right (items ; 1 ) “¶” ; items & “¶” ; items )
];
ValueCount (
Left (
items ;
Position ( items ; ID ; 1; 1 ) + Length ( ID )
)
)
)
As with the sorting burden, the following is not recommended for very large record sets.
Replace your current portalSort calc with the following to determine each record’s post-sort position:
valPos ( FoundList ( Child::ID ; 1 ); Child::ID )
The following custom functions will allow you to remove the ID-building loop entirely.
FoundList ( field; start )
// start should always be set to 1
GetNthRecord ( field ; start ) &
Case( start < Get ( FoundCount );
"¶" & FoundList ( field; start + 1 )
) &
Case( start = Get ( FoundCount ); "¶")
valPos ( array ; value )
PatternCount ( Left ( "¶" & array; Position ( "¶" & array; "¶" & value & "¶"; 1; 1)); "¶")
Example: valPos ( FoundList ( Child::ID ; 1 ); Child::ID )
@rmw and @Audrey this method is not going to be the fastest, but IMO is acceptable on related sets of a reasonable size (<1,000). YMMV. Personally I don't think a portal is a good choice for presenting thousands, or even hundreds, of records.
@Rob and @Heather, I would encourage anyone to modify the calc in a way that seems most clear. I've seen a number of variations. In fact, I was going to include a "flattened" version of my calc in my original post but edited it out for simplicity. Here it is:
ValueCount ( Left ( ¶ & $$gIDs & ¶ ; Position ( ¶ & $$gIDs & ¶ ; ¶ & ID ; 1; 1 ) ) )Darren Terry and Chis Manton both pointed me to Sander Selover’s custom function, which strikes me as very clean and readable:
Let ( [v = ¶ & _listOfValues & ¶ ;
p = Position ( v ; ¶ & _value & ¶ ; 1 ; 1 )
] ;
ValueCount ( Left ( v ; p ) )
)
(Notice I’m not using quotation marks around the paragraph symbols, it’s not required and it’s more readable, I think, without them.)
I would not expect such variations to impact the performance, and whether you put the calc into a custom function is again, really just a matter of taste and/or your organization’s coding conventions.
@Heather I really appreciate your taking the time to post the FoundList custom function, but I have to tell you it does not perform well at all on large set of records. It was really noticeable on anything over a few hundred child records.
But again, for smaller related sets it’s not going to make a perceptible difference, so if you prefer to put your logic in a custom function there’s no reason not to. My preference these days is to put my logic into scripts, all else being equal.
For best performance in my example file:
To prevent the list from being rebuilt by each record, the Found List custom function should be used to set the current list into a global that will be referenced instead; valPos ( Child::_gList; Child::ID ).
I don’t recall the details but thought I saw concern in someone’s post about using this technique in multi-user environments. This is not a problem if the portalSort calc is unstored and dependent on a global [or other unstored result like FoundList( )].
@Heather, stashing the ID list in a global field or variable is the essence of this technique. That’s what I tested using your Found List function, and the results were in line with Jason Young’s excellent “What’s Faster” presentation from last year’s DevCon: loop w/freeze outperforms GetNth in building an ID list by a whopping margin.
Thanks for pointing out that this technique is multi-user friendly, I should have mentioned that in the article.
Works awesome and thank you!
When I implemented it on one of our solutions, I was getting some screen flicker. Tweaking the GTRR to open in a new window at -1000 from the top and then closing that window when I was done eliminated it completely.
Thanks Again!
I have used this technique in my solutions since I read this great article. Yesterday, as I was working on it in my new solution, suddenly something dawn on me.
Instead of using an un-stored calculation in the sort field, I created a regular sort field. Then in the script, after performed the record sort, I used the Replace Field Content to populate my sort field with custom value starting with 1 and increment by 1. In the portal, I just sort this regular sort field
This way, there is no need to use the custom function and there is no un-stored calc. I have tested this many times and it seems to work and it is fast too.
I am not sure whether this technique is good because I am new to FileMaker. Can someone tell me ?
Thanks for writing. I’m glad you brought that up, and the answer is: that may be acceptable for a single-user solution, but it’s not multi-user friendly. What happens when two or more people try to sort some of the same records? Think about it.
Another potential problem is the speed: if there are a lot of records I think a Replace might end up being slower than an unstored calc.
Lastly, you are changing the modification timestamp of the records with each Sort, which may be undesirable.
Hi,
2 or 3 years ago Mikhael Edoshin described almost the same sort technique. His system has one difference: the numbers in the sort field are not consecutive numbers. They do not have to be a consecutive series (1, 2, 3, 4, 5, etc), as the numbers also sort correctly if the series contains gaps, like 1, 12, 34, 72, 135, etc. Knowing this, the only thing that has to be calculated is the position of the ID in the list: Position ( ¶ & $$gIDs & ¶ ; ¶ & ID & ¶ ; 1; 1 ). This calculation is simpler, so it should be faster too. And it works, I can assure you.
Hi, Martin, thank you for your feedback. Let me first say that I’m a fan of Mr. Edoshin, and his old blog has some great information, still valuable even though most of it is over 5 years old now. (For those that don’t know, you’re missing out! See “Bits and Pieces” in my link list. It used to be right at the top, and has only moved down because it’s no longer actively updated.)
You know, when I first came up with this idea, I thought, “somebody must have thought of this before,” but I googled as hard as I could and nothing came up. I also spoke to a lot of developers at the last FileMaker DevCon, and this was a new technique to all. I’m happy to credit Mikhael with the idea, I just wasn’t able to find any reference. Was it on a FileMaker mailing list or forum or something like that?
You are correct about the numbers not needing to be sequential. I mentioned this in point #4 at the end of the article. Same calc, just formatted slightly differently. It’s interesting though, I don’t recall any appreciable speed difference. (My understanding is that FileMaker’s text-parsing engine is very fast. Is it possible the number of digits generated by the calc could balance the scales somewhat? I don’t know.)
Great technique. Just wondering… how would you have the ascending/descending option toggle automatically? It seems to just go one way unless you hold shift key to do a custom sort. I like the functionality of having this toggle with additional clicks. Any thoughts?
Thanks, Stephen. I purposely made my example file simple to demonstrate the concept. There are plenty of existing sorting examples that could be easily incorporated here. The basic idea is that you store a global variable as part of your sort script and then branch your script logic accordingly.
If you haven’t done so already, take a look at Matt Petrowsky’s sample file in the followup article.
hi guys, thanks for the sample. successfully implement this into my database. does this work on IWP? tried it and it stucked on the related layout. and never goes back
AWESOME!!!! thanksss
Thanks guys, lifesaver, this worked very well for me. The sorting is way faster now, compared to other methods that I saw by others (e.g. calculation text fields used as sort criteria, which was way too slow). So far it is handling 1000+ records without significant delay.
Hi Tom,
A belated thanks for your article. I have found a couple good applications for your technique recently, when I needed the sort criteria to live in the parent rather than the child.
Regards,
Kevin
Great way to work around the limitations of portals!
I’m sure someone has already mentioned, but with object hiding now, putting the same code into the “hide object when” box, works a treat as well. This also means you can use alternative row colours without seeing white text on the grey rows.
Hi and thank you for sharing all of this.
I tried Matt Petrowsky’s process. The sorting of the columns works fine (ascending and descending); however, I am not able to perform any queries in the portal anymore. If I remove the global “Refresh” field from the relationship, the queries work but not the sorting.
What did I do wrong?
You did nothing wrong. A relationship using a global is meaningless in Find mode.
Now that we have FileMaker 14 and the Refresh Portal script step, you might find the global Refresh trick unnecessary.
I have just implemented this – For some reason it did not work with a Global variable but worked when I used a field and I am not 100% sure if it is because I am using data separation – anyhow it worked a treat when I added a global field to each table and I would like to say thank you vary much for sharing this technique.
Data separation is probably the issue. Global variables are file-specific. So you’d need to set the variable in the data file, which could be done by passing the IDs as a script parameter for example.
Hey Tom,
Nice posting; thanks for summing it all up. I was just looking for this kind of solution recently and it fit the bill very nicely. I had a list view layout that could be sorted in various ways, and a portal based on a different context (but the same base table) that I also wanted to keep sorted in the manner the main layout was. This solution worked very well.
Hey Tom; another year goes by and this technique is still being useful. I just used it again in a solution: there’s a main list view layout that can be sorted many different ways, and I wanted a portal in a pop-over to match whatever the main list view sort order was in.
However, I was running into one issue this time around: the portal wouldn’t always refresh to match the new sort order. So I could have the pop-over open and it would show a sort A-B-C which matches the main layout. Then I re-sort the layout (which can only be done by closing the pop-over – so the portal isn’t visible); the new order is B-C-A. Upon opening the pop-over again, the portal is still showing the order A-B-C.
I thought that since the portal wasn’t visible opening the pop-over would cause it to re-render – apparently not. My structure includes the ‘ListOf’ summary field, plus a global field (type=text) that receives a copy of the ListOf summary field whenever the main Sort script is run (which itself is triggered in a drop down of sorting options). The g-field is updating correctly whenever the sort is changed. If I put in a “refresh portal” script step in the trigger script that runs when the pop-over opens, the portal updates correctly. But…always hate to put in extra steps that could conceivably incur heavy processing loads.
The basic outline of steps:
close pop-over that holds the portal
re-sort main layout
g-field is updated (values are correct)
open pop-over that shows the portal
[result -> portal is sorted incorrectly]
refresh portal
[result -> portal sorts correctly]
Hi Tom,
I have just implemented this, but am having a strange issue; I have to run the script twice for it to work. The first time I run it, the IDs are collected but not sorted; they only sort after I run it a second time.
It’s great to hear people are still getting some use out of this. I still thing it’s the best method we have. Although I really wish FileMaker would give us native dynamic portal sorting already!
Justin, thanks for the info. Sorry for not responding sooner.
It’s hard to say what your issue is, Mark. Read through the comments here and you’ll see what’s worked for others: using a global field vs. a global variable, refresh steps, commits, etc. Also try some of the other sample files from the follow-up post — maybe some variation will work better for you. Cheers.