Incorrect searching in ESS tables

July 15, 200916 yr

We search ESS tables all the time - many from difference sources (SQL Server, Oracle or MySQL). But one particular external ESS source just threw us for a loop!

We were searching this ESS table (SQL Server) for the word [color:blue]transfer and it produced no results but we found out that [color:blue]TRANSFER existed in the field. Oh dear, how many other critical searches have failed for us on this table? Storage Options in FileMaker says ‘English.’ In talking to the SQL DB Admin for this table, I was told “it uses Latin1_General_Binary - Where Upper C is not the same as lower c, so filter must be extract.”

SURPRISE!! It is probable that others using FM and searching in ESS fields believe they are searching using standard FM rules. It makes sense that FM is searching (creating a SQL query) to that table and it must follow the collation of that field. It is my understanding that character set will determine data being entered and collation will determine sorting, case sensitivity and searching. For some reason, I thought that FileMaker would apply its OWN rules to an ESS field just as it does to data entered within it! I was wrong!

In FM Help under ODBC Data Sources Trouble Shooting, it says,

"Find results

[color:blue]For languages other than English, find results for ODBC tables might differ from FileMaker find results on the same data. For example, Asian text in an external table might be collated differently and give different find results than the same text in a FileMaker table. "

... but it says 'for languages other than English' and we're English.

What do I expect FileMaker to do about it? Well ideally, it would somehow make all ESS collations meet our standard search/sort understandings (translate it for us) but I realize that might be difficult. So I would ask that FM make us aware of it by putting the character set and collation information from each field right within the options for the field alongside the validation information. And I would also think that the Storage Options listing it as ‘English’ should be grayed out, just as data type and validations are grayed out. And add more information to FM Help about it.