Ignoring case while sorting

[liltbrockie]

HI there.. I am trying to adopt the portal sorting solution found elsewhere on this site. It uses this kind of SQL statement:

 

 

ExecuteSQL (

"SELECT "Address ID"
 FROM "Addresses!Contact ID"
Where "Contact ID" = ?
ORDER BY " & $field & Case ( $$sort_desc; " DESC" )

; "" ; "" ;Suppliers::Supplier)

 

 

And it fails if what you are sorting by uses a mix of upper and lower case characters... how can I ignore the case in this situation?

 

 

Matt

[Alex Taylor]

If you want to ignore case, you could create a stored calc in your table that uses Lower() to convert the field all to lowercase, then do your sort on that.

[liltbrockie]

That's a considerable amount of work for my database!

[Charity]

Ok. You have to make them the same somehow either both upper or both lower and then do it.  So maybe use Let to do that first.

 

HTH

[Priyabrata]

Hi,

 

Now try this query, it would work and ignore the case sensitivity of the fields.

 

ExecuteSQL (

"SELECT "Address ID"
 FROM "Addresses!Contact ID"
Where "Contact ID" = ?
ORDER BY " & Lower($field) & Case ( $$sort_desc; " DESC" )

; "" ; "" ;Suppliers::Supplier)

 

Regards

Priyabrata Sahoo (Priya)

Mindfire Solutions India

[liltbrockie]

Hi,

 

Now try this query, it would work and ignore the case sensitivity of the fields.

 

ExecuteSQL (

"SELECT "Address ID"

 FROM "Addresses!Contact ID"

Where "Contact ID" = ?

ORDER BY " & Lower($field) & Case ( $$sort_desc; " DESC" )

; "" ; "" ;Suppliers::Supplier)

 

Regards

Priyabrata Sahoo (Priya)

Mindfire Solutions India

 

Nope did not work

[comment]

Nope did not work

 

Of course not: Lower($field) works on the contents of the $field variable - IOW, it outputs your field name in lower case.

 

 

Try instead:

 

...

ORDER BY LOWER (" & $field & ")" & Case ( $$sort_desc; " DESC" ) ;

...

[jbante]

I updated the Virtual Sort module liltbrockie was referring to so it does a case-insensitive sort on text fields by adding LOWER to the SQL query as described in this thread.

[liltbrockie]

Nice

[comment]

Actually, it's not nice at all: it's a horrible hack that doesn't begin to address the issue. I don't see why SQL order shouldn't follow the same rules that Filemaker's native sort follows - i.e. take into account the language selected for indexing and sorting in the field's definition. That should be the default sort order - and one should be able to override it by adding a COLLATE clause to the query.

[liltbrockie]

Right.. just to add to this for anyone else using it.... that this

 

ORDER BY LOWER (" & $field & ")" & Case ( $$sort_desc; " DESC" ) ;

 

Fails on Number fields so what I had to do was let the script find out the field type first then choose the SQL to execute accordingly.

[comment]

Fails on Number fields

 

And, I presume, on Date, Time and Timestamp fields as well. I was so busy ranting about the disregard of charset, I didn't even consider this. It doesn't look like SQL (or rather Filemaker's implementation thereof) is the holy grail of dynamic sorting after all.

[liltbrockie]

Shame really...portal sorting by header clicks should have been implemented at what... Version 6? 7 versions later nope still nothing :(

[jbante]

Shame really...portal sorting by header clicks should have been implemented at what... Version 6? 7 versions later nope still nothing

 

We can gripe all we want about the functionality we should have had for years that we still don't have, or we can implement the functionality for ourselves. I get warmer, fuzzier feelings doing the latter, and so there's Virtual Sort.

[comment]

portal sorting by header clicks should have been implemented at what... Version 6?

 

True, and that's why I cannot work out a temper about it (portal sorting) anymore. SQL sorting, OTOH, is a brand new disappointment for me... I was playing with some ideas about implementing SQL in various ways - many of those are now forced off the table.

 

There is another aspect of this that's worth noticing: if SQL sort does not respect the language selected for the field's sorting and indexing, then it cannot use the field's index - can it? Which means it's "unstored" in terms of performance.

[comment]

or we can implement the functionality for ourselves.

 

Yes, we can. However, these implementations are either very costly or not good enough (or both, if you're lucky...).

 

Specifically, a sort order that ignores the language requirements is not good enough for me - and I could probably find examples where "ORDER BY LOWER" will produce a result that is different even from the default English sort order.

[comment]

I could probably find examples where "ORDER BY LOWER" will produce a result that is different even from the default English sort order.

 

Well that didn't take long, thanks to Wikipedia:

 

Filemaker's native sort, using English as the default language:

1. rock

2. rôle

3. rose

 

Filemaker's SQL sort:

1. rock

2. rose

3. rôle

[jbante]

English being a language where diacritical marks are limited to foreign loan words and words of the exceptionally erudite — does anyone else get a kick out of reading "coördinate" in The New Yorker as much as I do? — I can tolerate that minor inconsistency for the interim. I can't speak for users of other languages where diacritical marks are more important. Have you submitted a bug report/feature request for this yet?

[comment]

English being a language where diacritical marks are limited to foreign loan words and words of the exceptionally erudite — does anyone else get a kick out of reading "coördinate" in The New Yorker as much as I do? — I can tolerate that minor inconsistency for the interim.

First, that was only an example. I didn't say the problem was limited to words with diacritical marks. The fact is that I don't know the exact scope of the differences.

Next, diacritical marks are NOT "limited to foreign loan words and words of the exceptionally erudite". They are also used in names of people. People that are often sensitive to how their names are spelled and even sorted. Especially when they are misspelled and sorted out of order.

Moreover, I am just a lowly consultant and it's not my place to "tolerate inconsistencies" on behalf of my clients. I am supposed to provide solutions that will fit their data - not my idea of what their data should be. As far as I am concerned, they have every right to be as "exceptionally erudite" as they choose to be. Not to mention that some of them choose to work in all kinds of esoteric languages - like Spanish or German - or even languages that use a non-Latin alphabet such as Greek, Russian or Arabic. Go figure - people can be weird that way...

Finally, but most importantly: I cannot afford having two sort orders for the same field. It's very tempting to use SQL for all kinds of auxiliary stuff like ad-hoc value lists, "virtual" tables and so on - but when you get back to the real data and you land on the wrong record because you thought that "rôle" comes after "rose"...

[liltbrockie]

I agree with comment. The lack of consistency is unforgivable. It should work one way or the other not a mixture of both