Update 22 Jan 2013: Demo file and screen shots have been revised to fix bugs identified by Matt Ayres and David Schwartz (see comments at the end of article).
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, it turns out that Ugo’s demo suffers from the same shortcoming that caused me to write part 2 in this series: it doesn’t sort negative numbers correctly. For that matter it doesn’t sort currency values correctly either, but these are minor blemishes on a brilliant demo that the author no doubt could have easily remedied had he so chosen.
How much work would it be to get my demo to sort using a single sorter field? The answer turns out to be, not much work at all. “Method B” in my previous posting used substitution with numeric values, but if want to have just a single sorter field, I’m going to have to extend the substitution approach to text values as well. Or borrow code from someone who already has.
As I wrote back in April…
Ugo’s stroke of brilliance was to realize that you can fake a descending sort, when the actual sort order is ascending, if you invert the values behind the scenes… I particularly like the way he transforms text characters, taking advantage of these FileMaker behaviors:
a) Substitute is case-sensitive
b) Sorts are case insensitive…
So, having first cast all the text as upper-case, he then transforms A to z, B to y, C to x, and so on through to Z. If he hadn’t done this song-and-dance with case transformations, he would have encountered substitution collisions halfway through the alphabet, and the result would have been a big mess.
So, if I lift the highlighted portion from Ugo’s sorter calc…
…and insert it into a revised version of my sorter calc…
UPDATE 9 May 2021: I recently identified an “edge case” problem, which you can avoid by changing the f declaration in the above calculation to
For more information see Bypassing Scientific Notation.
…and revise my “Sort Records” step to reference a single field…
All will be right with world, and maybe, just maybe, I can finally lay this topic to rest.
For this technique to work, we need to know in advance what the maximum number of decimal places will be, and then multiply by a large enough multiple of ten to eliminate the decimals entirely. In this example the multiplier is 100, because I am only anticipating a maximum of two decimal places (to accommodate US currency). But if your maximum number of decimal places is, say, four, multiplying by 10000 will fix the problem, e.g.,
Right (
"0000000000000000" & 10000 * GetAsNumber ( ( GetField ( $$fieldName ) ) ) ; 16
)
And in the interest of fully disclosing all potential problems (including those unlikely to occur in the real world): a limitation of the above code is that it will only accommodate numbers as large as 999,999,999,999.9999 — beyond that the number of leading zeros, and the second argument of the Right() function will need to be adjusted accordingly.
If you either don’t know how many decimal places your numbers will go to, or simply don’t want the hassle of worrying about this, I recommend you play it safe and use Method C from part 2 (or some variation thereof) instead.
Very nice!
The sorting is not working for me on text fields where I have numbers at the start of the field.. consider a field that is product types with values:
1.0 Bowl Sink
1.5 Bowl Sink
Tap
Drainer
2 Litre Pan. etc etc
The sort does not work as expected for the above records due to the number at the start!
Hi Matt,
Are you seeing anything different than what you get if you do a standard “Sort Ascending” on the field?
In other words, is the problem with the technique in the article, or the way FileMaker sorts text fields?
Regards,
Kevin
Hey Kevin, yeah if you use the above demo (v3) and enter some numbers at the start of the values in the first column eg: 1Zombo, 2 Tout, 2 sutch, then try sorting on that column you definitely do not get expected results, indeed, you do not get the same results as if you use filemaker native sort commands on the field!
You’re absolutely right, Matt.
Thanks for bringing this to my attention; not sure how I could have missed that.
That’s okay… I presume it’s something to do with that sorter calculated field being formed incorrectly… Hope you have time to make a fix for it as it’s a bit beyond me!
Demo file and article have been revised. Please let me know if you detect any further discrepancies.
Nailed it :-)
DOH! Spoke too soon!
There is a problem now with Numbers. Consider the field “Account Balance”… populate this field with random values from 0 – 2000 eg:
222
235
455.2
458
500
544
622
677
687
1250.3
236.35
2563.3
783.65
1003.45
1452.36
The sort is not behaving as expected for me.
(the original v3 seems to work fine so it’s what ever you did to get to Rev.1 that’s broken it!)
Matt
Thanks for your diligent testing. It should be completely resolved now.
Hey Kevin!
Throwng NULL values for fields into the mix doesn’t work out too well either I’m afraid…!
If you open up your last rev and delete the “last name” field for a few of the entries you will see what i mean! Sort does not work as expected.
Another good catch. Fixed now. Thanks again.
I don’t understand; the “Date of Birth” field doesn’t sort properly at all (FM 11 or 12). Is it supposed to?
Hi David,
Thanks for writing. You’re absolutely right. The problem was specific to date fields and has been fixed.
http://kevinfrank.com/fmh/dynamic-list-sorting-v3-rev5.zip
Regards,
Kevin
I think this deserves an award for “buggiest demo” of all time.
I must thank you for the files. I can honestly say i don’t understand the sorting script and how it reverses order, but i downloaded your files and will see if i can implement this solution blindly. I know this isn’t optimal but i don’t have your intellect i guess.
Hi Brian,
Thanks for commenting. I had to revise the demo a half-dozen times, and it’s based on code I borrowed from Ugo Di Luca, so I guess I don’t have my intellect either :)
The idea of always doing an ascending sort, but inverting the data when you want to fake a descending sort, is an extremely clever one. It makes me smile every time I think about it. The methods in part 2 are more straight forward incidentally.
Happy FileMaking,
Kevin
Hello Kevin,
The command set variable ($$fieldname; value:$sp) automatically calculates and fills in Calculated sort_asc and sort_desc fields (depending on the sort order selected) only in first record but not all records. Any idea what could be wrong? Thanks.
Hi Dave,
Just checking, did you make sure the two sort fields are unstored?
Regards,
Kevin
Both calculated fields are defined as :
‘Do not replace existing value of the field (is any) unchecked,
Under Validation:
For Validate data in this field has ‘only During data entry’ checked.
‘Allow user to override during Data entry’ is also checked.
On Storage Tab:
For Indexing ‘None’ is checked.
Thanks.
Hi Dave,
The field type needs to be calculation with storage type set to unstored. It sounds like you’ve currently got the fields with a text type populated via auto-enter calculation.
Take a close look at the field definitions in my demo file.
Regards,
Kevin
Hello Kevin,
I do not have option for ‘Do not store calculation results’ under Storage Tab for calculated fields.
Thanks
Make sure your calcs aren’t using global storage.
Hello Kevin,
You are absolutely right. I had defined the sort_asc and sort_desc fields as Text and then auto calculate for options instead of defining them as calculation and then creating ‘options’.
Thanks a lot for your blog and also for your reply. I had been trying to work this situation out for two days. It does help to slow down and check everything.
Appreciate it.
Hi Dave,
You’re welcome and you’re welcome :-)
Regards,
Kevin
Hey Kevin, I’ve been using this for years. Wanted to thank you. I hit a snag. I copied everything into a new DB, and it just wont set the spType variable, no matter what I do. The only way I can get it to work is to go into the sorter field and change the ‘a’ variable in the let statement to “Text”.
Even when I put the expression (hard-coding a field name) it returns nothing:
MiddleWords ( FieldType ( Get ( FileName ) ; Jobs::Ship_City ) ; 2 ; 1 )
Any guess as to what I am doing wrong?
Like I said, I used this many times in a multiple databases. I’ve put them side by side, and just can’t seem to find the obvious problem.
Thanks again
Steve
Hi Steve,
Based on your description, my guess is that the parameter attached to the column heading button is not configured properly… it should look like this:
GetFieldName ( to::field )
…where “to” is the table occurrence of your layout, and “field” is self-explanatory, e.g., GetFieldName ( salespeople::state ).
Regards,
Kevin
Two other thoughts:
1. Make sure the result type for the sorter calculation field is “text”, and that the storage type is unstored.
2. Check the “evaluate from” box at the top of the calculation definition for the sorter field. It needs to be the same as the TO the layout is based on.
Hey Kevin,
The problem was my file name had a period in it. And it’s right there in the instructions for Get(FileName):
“…Note If you use Get(FileName) in a function parameter that requires a filename and the filename contains a period, include the filename extension in the parameter. Otherwise, functions may interpret the period in the filename as the beginning of the filename extension, which can lead to unexpected results…”
Completely my fault.
Steve