Case Sensitive Query Item

Hi Everyone,

I am using a Query to search related files and then using the RAD 11 Compatible "Find" window to search the results further. The window uses HFilter to select items.

Can anyone explain why if you use "Starts with" on a text item, it is case sensitive and if you use "Contains" it is not case sensitive? The generated filters are:

"Starts with" -> <query.item> LIKE '<string>%'
"Contains" -> <query.item> LIKE '%<string>%'

How can I make all filters not case sensitive in all cases to avoid confusion. The original HF file field is not indexed.

Hi Jeff,

I don't use Hfilter much as I heavily use queries, but I am interested in this issue as one would assume there would be no difference. Just to verify, you are talking about the same items from the file behaving differently, correct? What are the settings on the file attributes for the items (in terms of case sensitivity, etc.)?

Regards,

-Marc

Hello Everybody...

The only reason I can see for such a difference would be that there is a key for the field checked as NOT case sensitive...

The hfilter would then use that key for 'start with' but of course couldn't for the contains...

So the start with would be case insensitive, and the contains would be case sensitive...

And that's the opposite of what you are describing :-(

So without seeing the code and analysis, I'm at a loss

Hope this help

Fabrice Harari
WinDev, WebDev, WinDev Mobile consulting

There is no key associated with the field and I am talking about the same item behaving differently.

I submitted this question to the hot line but have not heard back yet.

Will keep you posted if I find out anything.

Jeff

Hi Fabrice,

Here is the code generated by the WinDev 11 Find window for HFilter:

gsfKeyName=HFilter(gfsFile,gfsSelectionCondition)

where
gsfFile = "QRY_NmS_Contacts" which is a query containing file item "FirstName" which is defined in the analysis as "Not a key", string 20.

For "Starts with"
gfsSelectionCondition = "QRY_NmS_Contacts.FirstName LIKE 'jeff%'" and is case sensitive as no results are found unless 'j' is changed to 'J'.
gsfKeyName = "FirstName"

For "Contains"
gfsSelectionCondition = "QRY_NmS_Contacts.FirstName LIKE '%jeff%'" and returns the same results for 'j' and 'J'.
gsfKeyname = "ContactID" which is an automatic ID for the file the query is based on.

Very strange results. I hope you have some ideas. Thanks for taking the time.

Jeff

Hi Jeff...

Here is my guess... It's only a guess because you are filtering using a 'free hand' condition on the result of a query... Which means that you are in the case where WinDev does the work for you TWICE...

In the first case (start with), the key returned by the hfilter is:
gsfKeyName = "FirstName"

BUT you told me that FirstName is NOT a key in the file, which means that your original query probably sorted its result on FirstName, thus CREATING a temporary key in the memory result... And this key seems to be case sensitive...

When you do a hfilter with a startwith condition on firstname, it's used for optimization and therefore is case sensitive...

In the SECOND case, you are doing a CONTAIN, for which NO optimization is possible. The hfilter therefore just read all records of the query and compares (key returned ContactID)... In that case, only a comparison is done and clearly this one is case insensitive.

Of course, not knowing the insides of the QUERY+Hfilter codes, it's only an educated guess...

You will have this kind of result for as long as you will use queries and free hand conditions... Easier to write, but less strict on how the result are obtained and therefore with some surprises from time to time...

Best regards

Fabrice Harari
WinDev, WebDev, WinDev Mobile consulting

Hi Fabrice,

Thanks for the info. That sounded like a good explanation.

However, the query is not sorted by first name, but it does have a condition. I tried removing the condition and it is still case sensitive for 'starts with' and shows 'FirstName' as the key. So I have no idea why the memory file has a key for FirstName.

I have no sorts so I tried forcing a sort on a key field and still the memory file HFilter retturns FistName as the key.

Cheers,
Jeff