Set filter on PRICE value.

[tempuser123]

Hello,

I have a grid on a form and I'm using the Set Filter To in Interactive Change of a text field. It works quite well for any character value (Product name) and numeric value (product number 1-1000), but it does not work on numeric values with decimal point (product price 10.99).

I've tried

SET EXACT ON 
set filter to alltrim(thisform.text12.value)=substr(ALLTRIM(STR(pprice)),1,len(alltrim(thisform.text12.value)))
go top
thisform.refresh

If I type 10 the grid shows all records that have 10 as the price (10.00, 10.55, 10.34, ...)but I'm looking for 10.55, so If I type 10.55 I get nothing.

Thank you for your help.

 

[Koen Piller]

Hi,

must better is to execute a

select * from myTable where myField > 10 into cursor curGrid

and than make this cursor the controlsource of your grid.

Also my advise FWIW avoid the ThisForm.Refresh()


Rgds,

Jockey(2)

 

[Mike Lewis]

You could make thisform.text12.value numeric (by initialising to zero). Then, your filter condition would be:

SET FILTER TO BETWEEN(THISFORM.Text12.Value, pprice, pprice + 1)

That way, you get rid of the messy conversions and substring handling.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

The problmatic part in the original code is STR(), STR has a parameter for decimals, if you don't use it, it will convert numbers to strings without the decimals, that's all. Use TRANSFORM instead, or simpler, let the textbox be numeric, as Mike says. If you only want exact matches then, the condition is simply pprice = thisform.text12.value

I'm not sure THISFORM is a good choice in expressions for FILTER conditions overall. The FILTER is set to a workarea, it does not have the context of the form. It works, I think, but activating another form could cause problems, I think.

I just tested: Indeed you get a problem with filter conditions containing THISFORM, if you change focus from the running form to a browse window. I get an error "Object is not contained in a form" then.

The thing with filter expressions is, they are NOT evaluated at the time you set the filter, they are set as the expression itself, which is continuously evaluated each time a grid or browse does access the workarea. And if that is done outside of the context of the form it breaks.

The advantage is, you can set the filter once and changing the control value changes what you see, a filter is "live", as is a relation etc. he disadvantage is, you depend on the context and your filter isn't stable. I tend to do something along the lines of

lcCondition = "pprice = "+TRANSFORM(THISFORM.Text12.Value)
SET FILTER TO &lcCondition

Now you have set the filter to the current value of the text12 textbox, changing the textbox needs a new SET FILTER, but that's what you do anyway, if I understand you correctly.
Makro substitution is causing the expression to be substituted at that moement and so, if the text12 value is 10.55 you SET FILTER TO pprice=10.55. Changing the focus does not break this in any way.

It's also a nice thing to SET FILTER TO pprice = gnPrice and use a public variable gnPrice and bind the text12 textbox to that, then you only need to set this once, but it has the disadvantage of a public variable.

Bye, Olaf.

 

[tempuser123]

Thank you all for your help, and for the code examples.

After a few tests, it worked and the grid behaves as I wanted it.
Here's the code I used, as you suggested, with TRANSFORM:

set filter to alltrim(thisform.text12.value)=substr(ALLTRIM(transform(pprice,'@R9999999.99')),1,len(alltrim(thisform.text12.value)))

I'm going to test your alternatives as well and see the differences, I believe it will come in handy for future projects

Again, thank you so much.
Andrei.

 

[Olaf Doschke]

You could use the plain TANSFORM(), it'll use as many decimal places as you need.

But as you want to compare the numeric value, don't trasnform the price to a string, let the user enter a number as type number, simply preset the textbox value as 0, then the user can only enter numeric values.

Bye, Olaf.

 

[Dan Freeman]

Everyone here has hinted around the reality:

DO NOT USE SET FILTER

Ever.

Don't do it.

There is a better way absolutely every time.

I'm in a position right now where someone 20 years ago used SET FILTER to get themselves out of a bind (or because they didn't know a better way), and it worked. So they did it again. And again. For 15 years. Add a couple of other things she did because she also didn't understand dates or loops -- for 15 years -- and there's quite a mountain of code that's really fragile and breaks often.

They look at me and ask "what's wrong?"

Please don't use online resources to find out how a command works. Use them to find out the best way to do something. Someone fifteen years in the future will thank you.

(Whew! Got that off my chest! Sorry.)

 

[Olaf Doschke]

You're so right, Dan.

I had the urge to mention SQL-SELECT WHERE, but then you start a change process that does not end at how to do queries instead of SET FILTER, but alos on why grid goes blank, how you can update a DBF from queried data. Why views are good and why they are bad, etc. and I'm not in the mood to make this lengthy educations anymore. VFP is a dead end in itself, I think we don't need to talk about 15 years from now, even if there are more than 15 year old VFP8,7,6,..... and VFP DOS applications still used, it's not development but usage.

I showed a downside of filters - not being in the form context you think they are. You have to know filters are not applied just once, the filter condition is reapplied to every record read from a workarea. Even if it's rushmore optimizable it is optimized and used again and again in contrast to rushmore optimization of queries FILTER has less benefits than WHERE optimization.

We don't know how much data is filtered and if it stays small. Some database tables stay small, some grow. You can find out about SET FILTER here and in other forums and there are enough discussions warning about it.

Bye, Olaf.

 

[Mike Lewis]

Dan,

Of course, you are right to avoid SET FILTER - and especially with regard to grids. And you're right to point it out to the questioner.

In cases like this, there's always a balance between giving the questioner some immediate help that will get him past his problem, and re-educating him towards adopting better practices in the long term. In this case, I chose to show him where his existing code was going wrong, and to suggest a quick improvement. But I agree that might not always be the best approach.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Freeman]

Hi Mike,

Perhaps the steaming pile I'm suddenly dealing with is causing it but I'm a little sensitive to helping people do the right thing these days. <shrug>

I always have been to an extent, and I do understand the quick fix that is often the goal with an online post, but sometimes it should also be pointed out that the quick fix to the wrong thing is still the wrong thing.

 

[Olaf Doschke]

Dan,

I'm in your boat. Not so long ago I was sometimes also asked why not to focus on the given problem and always extend. I also pointed out some things, eg in this case I find the lesson more important to compare data on it's natural type. Prices are numeric and you can configure a textbox to allow only numeric entry, that alone solves another problem of users entering anything else but a price.

There are many levels here, but the discussion is lost, if tempuser is what he names himself and does not return.

Also I am confident people learn from bad experiences better than from good advice.

http://www.youtube.com/watch?v=cdgQpa1pUUE

To take us even more off topic, would you go in a car like this?
Even if you're blind, would you trust a person more than a computer system?
I think people tend to forgive themselves easier than anybody else or any technology, so they do not only learn better from own mistakes, if they can only blame themselves it's easier to cope with a fault. But think about how muh more secure cars could be as a swarm knowing the nearby cars and their intention by communicating with each other? And now imagine still with this added security an accident happens. What would people think? The developers are to blame, the technology is to blame, and this would never have happened if people would have stayed in charge. People, who get angry, drunk, tired, depressive and run amok.

Back in regard to the system of tempuser, we don't know how good or bad FILTER will work. I have built an inventory system last year, soon to be used this year, that will store at most 1000 or perhaps 2000 records per inventory. Previous year data is separate, so you can easily work with SET FILTER on this low number of records and things still are fast and easy to use.

Bye, Olaf.

 

[tempuser123]

I do apologize for starting such a debate, and for submitting a dumb question, but I was is need of some help, and I believe the best approach to any problem is communication with your peers. That's why I always share mine with a forum, people tend to help each other, not all the time, but sometimes. For that I am grateful to you all, I know my problem seems small to some, but when I have to deal with people that tell me not to change a thing in the interface (because they love this "live" effect that Set filter to on a grid gives), I have to do my best and find solutions. In my case, the application is small, the table has max of 500 records per year, and I had no issue with grid and filters. However, I am going to try alternatives, for future projects, this "fix" is temporary, 'till I get an understanding of the whole problem, and of course, more important, get some free time. That's a promise.

 

[Olaf Doschke]

The simplest questions can cause debates here. Partly because there is not much to do here. It's not bad at all.
I hope you also got the help you needed anyway. You're welcome.

Bye, Olaf.

 

[Dan Freeman]

The only dumb question is the one you don't ask!

Ask a question any time. But there are some questions (most questions?) that actually have multiple answers and we tend to grind through all of them. And then there's the idea of an alternate approach being questionably better, and it would be professional malpractice for us not to point that out.

 

[Olaf Doschke]

I strongly agree with dan: Your question was not dumb.

Indeed my direct answer points to STR() and you could have looked up what it does. If you consider questions you can answer yourself as dumb, than that still is just the simple answer. You have been told to do things differently and you can decide in what direction(s) you want to go. That has given you much more and better insight than looking up STR() in the help.

By the way one thing that helps with such more complex expressions, to see why they not help and what part does not help, is putting partly terms of the expressions into variables and inspecting them in the debugger. In general, make use of the debugger:

LOCAL lcUserInput, lcDatabasePrice

lcUserInput = alltrim(thisform.text12.value)
lcDatabasePrice = substr(ALLTRIM(STR(pprice)),1,len(alltrim(thisform.text12.value)))

set step on

You can then see the values of the two variables in the locals window.
You see that the lcDatabasePRice will not have the decimals, and then you can investigate further, eg

LOCAL lcUserInput, lcDatabasePrice, lcSTRPrice, lnLenText12Value

lcUserInput = alltrim(thisform.text12.value)
lcSTRPrice = ALLTRIM(STR(pPrice))
lnLenText12Value = len(alltrim(thisform.text12.value))
lcDatabasePrice = substr(lcSTRPrice,1,lnLenText12Value )

set step on

And then you get on the right track by seeing STR does not convert as you thought it does. It's mostly wrong ideas of a function or command leading to a runtime error you don't expect.

Bye, Olaf.