Easy Sorting of List Views, part 3

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…

1-22-2013 5-26-02 PM

…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.

29 thoughts on “Easy Sorting of List Views, part 3

  1. Matt

    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!

    Reply
    1. Kevin Frank Post author

      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

      Reply
  2. Matt

    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!

    Reply
    1. Kevin Frank Post author

      You’re absolutely right, Matt.

      Thanks for bringing this to my attention; not sure how I could have missed that.

      Reply
  3. Matt ayres

    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!

    Reply
    1. Kevin Frank Post author

      Demo file and article have been revised. Please let me know if you detect any further discrepancies.

      Reply
  4. Matt

    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

    Reply
  5. Matt Ayres

    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.

    Reply
  6. David Schwartz

    I don’t understand; the “Date of Birth” field doesn’t sort properly at all (FM 11 or 12). Is it supposed to?

    Reply
  7. Brian

    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.

    Reply
    1. Kevin Frank Post author

      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

      Reply
  8. D. dave

    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.

    Reply
    1. Kevin Frank Post author

      Hi Dave,

      Just checking, did you make sure the two sort fields are unstored?

      Regards,
      Kevin

      Reply
  9. D. dave

    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.

    Reply
    1. Kevin Frank Post author

      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

      Reply
    2. D. dave

      Hello Kevin,
      I do not have option for ‘Do not store calculation results’ under Storage Tab for calculated fields.
      Thanks

      Reply
  10. D. dave

    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.

    Reply
  11. Steve

    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

    Reply
    1. Kevin Frank Post author

      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

      Reply
    2. Steve

      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

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s