Set Filter Not Working with Character Data

[stanlyn]

Hi,

This should just plain work, but it doesn't and I've spent a lot of time on it...

I've included a screenshot (a picture is worth a thousand words) that shows ALL the details with the debugger on and watching values and you see the code. This is simple stuff that I've used many many times before.

With option #2 in the option group checked and with an empty search textbox the qty returned should be 0 instead of the 435 that it says. If I check the last option (all) it returns the same 435 records. This is insane!

Any ideas?

Thanks,
Stanley

 

[Mike Lewis]

I suspect that you have SET EXACT OFF, in which case what you are seeing is what I would expect.

Try doing SET EXACT ON.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Just a notice on your line "GO TOP". You indentation looks, as if you intend to make this work always, but it'll only run in the second case anyway. This is only needed, when you want to go top after case2 count is done. Since you have the go top before the do case, any case starts with record pointer at top anyway.

More important, a count to lnCount will only count the filtered records, either you set filter to something and COUNT TO lnCOUNT, or you only COUNT FOR something TO lnCount, you don't need to repeat yourself.

Mike's suspect is true, this is shown in your debugger watch window. So yes, either you SET EXACT ON, or make use of == where you want exact string matching, eg [tt]alltrim(party2_name) == lcFindValue[/tt]

Bye, Olaf.

 

[Mike Lewis]

The other thing I would do in cases is like this is to wrap both sides of the condition with an UPPER() - especially where user-entered data is involved. That doesn't apply in this particular example, but it's a good rule to keep in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Hi Mike,

I suspect that you have SET EXACT OFF, in which case what you are seeing is what I would expect.

Try doing SET EXACT ON.

Yes Mike, set exact is set to off as shown in the watch list. Setting it to on will not allow the incremental filtered results. For example the table has a party1 names Amanda, Amber and Amy shown in the grid, attached screenshot, (just to prove these names are in there).

If I start typing "A", I'd expect to see all 3 names, and when I type "Amb" I expect to only see names that starts with "Amb". Therefore setting exact to on prevents that from happening. The 2nd screenshot shows that if both sides are uppercased and set exact is on, then no matches are found, and you can easily see that is not the case here.

So, any other ideas?

Thanks,
Stanley

 

[stanlyn]

Hi Olaf,

Just a notice on your line "GO TOP". You indentation looks, as if you intend to make this work always,

No Olaf, I am only setting up the 2nd item for testing. The "go top" was only put there for testing the "move the pointer" as described in another tek-tips article. Its indentation was intentional for it to stand out after testing has completed. The article that mentioned "moving the pointer for set filters is"

http://www.tek-tips.com/viewthread.cfm?qid=1102724

a count to lnCount will only count the filtered records

I did not know that... Thanks!

Mike's suspect is true, this is shown in your debugger watch window. So yes, either you SET EXACT ON, or make use of == where you want exact string matching, eg alltrim(party2_name) == lcFindValue

See my message to Mike regarding while this approach will not work for the customer.

Thanks,
Stanley

 

[stanlyn]

Hi,

What is more disturbing is this little block of code is reporting its values incorrectly. Take a look at what lcFindValue's actual value is. It is 1000 as shown in the txtFind textbox and the balloon tip. I then test for txtFind's value being "" and the debugger thinks it is and steps into the test. It should have tested false and stepped over it. Notice the "next line to run" pointer.

There has to be an explanation! Truly disturbing...
Stanley

 

[Olaf Doschke]

>moving the pointer for set filters

If you want to have that effect, it MUST go right after SET FILTER, not after COUNT. You also rather do LOCATE, LOCAT$E also was the given command by Jim. A COUNT is moving record pointer, too, it's no COUNT REST or COUNT current row. The only thing a GO TOP or LOCATE (right after SET FILTER) ensures is the record pointer is on a record fulfilling the filter. Setting the filter alone, while you're on a record not fulfilling the filter is not moving, that's true, but you don't need to be afraid you include the current record as count, count starts from to taking into account the filter, that's not your problem.

Bye, Olaf.

 

[Olaf Doschke]

[tt]If lcFindValue = ""[/tt]

Yoiu still dont understand string comaparison with EXACT OFF. And we've told you it's your problem alreads. Every string is equal to "", test with == here, if you want to test for emptyness,or use EMPTY(), which also is true for all whitespace strings.

Bye, Olaf.

 

[Olaf Doschke]

>See my message to Mike regarding while this approach will not work for the customer.

Stanlyn, please take your time and read fully. I said ...OR use ==

You don't need to SET EXACT ON, you can only use == where you want exact matches, and that's even what's better, as it allows both ways of string comparisons.

Besides that, you can also do an incremental search with EXACT ON, when using LIKE("input*",field) as filter expression. It's a matter of taste, but you need to understand the effect of and ways of string comparisons, it's a very basic thing you should know already.

Bye, Olaf.

Put in another way: Assumption 1: You just recently changed SET EXACT OFF. Then you likely have lot of code not working anymore, you'll have lots of work going through any IF var="" or other comparisons to make them work as when SET EXACT was ON. If you need "starting with" (inexact) string comparison at some places only, eg in filters, then you may stay with EXACT ON and use LIKE() with FOR clauses or LIKE operator with SQL queries. It's a huge step to change a general setting, that influences all code, you better have something like that right from the beginning. I prefer exact off, which is the default anyway and makes sense for sql to work as expected, but then use == in places I need exact matching.

 

[stanlyn]

Hi Olaf,

I don't use set exact on. It was used so as Mike suggested it and I wanted to show you all in the debugger its effects.

Very rarely do I use exact=on. As you said the default is off and I use it that way too for the same reasons as you. And when I need exact=on I generally use ==, also as you say. That said, it is my understanding that field1 == "value" is the same as set exact on - field1 = "value" - set exact off. Correct????

If lcFindValue = ""

Which is better for this test, squeezing the spaces out with the allt or not...
1. if lcFindValue = ""
or
2. if lcFindValue == ""
or
3. if allt(lcFindValue) = ""
or
4. if allt(lcFindValue) == ""
or
5. if allt(lcFindValue) = ""
or
6. if empty(lcFindValue)
or
7. if empty(allt(lcFindValue))

To me it looks like 4 and 7 would yield same results. So what would you use?

You still don't understand string comparison with EXACT OFF

Obliviously I don't... however I now remember an in-depth discussion we had here concerning it string comparisons. A revisit is in order...

which also is true for all whitespace strings.

Please define a whitespace string and how it differs...

Thanks for the "moving the pointer" immediately after the filter discussion, as I've always got my count by adding the filter expression into the count command, thereby as you said, duplicating it (un-necessarily).

Thanks,
Stanley

 

[Olaf Doschke]

Whitespace strings: Any strings containing spaces and tabs (whitespace) are also empty: ? EMPTY(" ") is .t.

Whether you use If lcFindstring=="" or ALLTRIM(lcFindstring)=="" is up to you, just don't expect lcFindString="" to react to empty strings only. With exact off (and you say you always have it off) it always is true, any string, no matter if empty or not begins with nothing. Exact off matching is "begins with" matching. ? "A" = "", what do you get? Sucgh an IF can never work as you exepct it, so my guess was, you were working with EXACT ON to have such code working. When you say you use == where needed, then why didn't you in that case? You're contradicting yourself quite a bit here.

Bye, Olaf.