Displaying records in a grid if value = x 1

[keepfoxing]

what i want is to display only the records being filtered with the value on the field of single and male.
how to do this using set filter?

thanks in advance

 

[Mike Lewis]

The same way you set any other filter.

[tt]SELECT TheTableInQuestion
SET FILTER TO Sex = "M" AND Status = "Single"[/tt]

If the grid is already visible at this point, you will then need to set focus to it to ensure that it reflects the filter:

[tt]THISFORM.TheGrid.SetFocus[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

There is no DynamicSkipRecord or something along that lines, I don't know what you expect.
Obviously you can also just query that part of the data, which you DO want to display, into a grid cursor.

Bye, Olaf.

Edit: When going the SET FILTER route, also add LOCATE right after SET FILTER, LOCATE puts the record pointer to the first record fulfilling the filter condition. The SET FILTER alone does not move the record pointer from its current position, even if it does not fulfill the filter condition. The SetFocus to the grid might also do the same as a LOCATE followed by several CONTINUES anyway, but I often see people use SET filter, as if it would be a LOCATE FOR or BROWSE FOR, it only sets a certain filter, it is applied in all future accessing of records from then on until another SET FILTER.

A simple demonstration how "weird" that acts (always depends on expectations vs truth):

USE labels
SET FILTER TO .F.
? EOF(), RECNO(), RECCOUNT()
LOCATE
? EOF(), RECNO(), RECCOUNT()

Unless your HOME()+"labels.dbf" has no records the first EOF() will display .F., even though SET FILTER TO .F. means no record is fulfilling the filter condition. LOCATE "activates" the filter and moves to EOF, you are at RECNO()=RECCOUNT()+1, therefore. In a similar way just setting a filter does not influence the grid display until you "rock" at it a bit.

Bye, Olaf.

 

[keepfoxing]

I'm sorry guys for the late response..

actually what i want is that to filter a table in a one field only..
i want to filter single and married.

I'm trying to do it like this:

set filter to status = "Married" and status = "Single"

but it wont work..
plz help

 

[vgulielmus]

Simply replace the word "and" with "or"
set filter to status = "Married" or status = "Single"

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

 

[keepfoxing]

it did filter but ignores the first condition..
i want to display only on the grid where status is equal to married and single..

 

[Olaf Doschke]

What do you mean by "ignores the first condition"?

OR is definitely correct, as no status can be "Married" AND "Single" at the same time and that's what you ask for each record, when using AND. When you say in natural language you want to display married and single people you actually mean people who (each) are either married OR single. Filters are always about a (each) row and in one row you can't have both at the same time. Your condition not only has to be true for each single record, but also for each married record.

Do you see people with a status other than "single" and "married" or do you only see people with status "single"?
If it's the latter, then check, if you have "married" at all in the unfiltered data.

You might also fight with an upper/lower case issue.

Or is the overall condition more complex? Then try to force the right logic with parenthesis of partial conditions. Not only is AND and OR very different, they also have different precedence and are not simply executed left to right. Like multiplication and addition ANDs are executed first, then OR. As recently taught to foxuser, too.

Bye, Olaf.

 

[keepfoxing]

here is the sample code:

set filter to othercondition = "something" and othercondition2 = "something2" and status = "Married" or status = "Single"

and the one im telling you that ignores the condition is the first 2 condition..
so it will be F,F,T,T
on the grid it shows all records with status of "Married" and "Single" and all records with othercondition = "something" and othercondition2 = "something2

 

[Olaf Doschke]

Well, then you need a pranethesis:

set filter to othercondition = "something" and othercondition2 = "something2" and (status = "Married" or status = "Single")

Otherwise you have ALL single people, even those not fulfilling the first conditions. Without paranethesies you're saying:

I want people who are condition1 AND conditon2 AND status="Married" and besides these it's also OK if status="Single", no matter what else.

Like saying you want a woman, who's honest and beautiful and faithful or rich.

Bye, Olaf.

 

[Olaf Doschke]

>so it will be F,F,T,T

Well, it could be F,F,F,T, but never F,F,T,T, as no data can be equal to two completely different strings.

Bye, Olaf.

 

[vgulielmus]

Now that's hilarious

Like saying you want a woman, who's honest and beautiful and faithful or rich.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

 

[keepfoxing]

it worked..thanks guys for helping!

 

[Olaf Doschke]

To not need OR and to not need to think deep down low level of what you need, you may also use INLIST() or the $ operator, eg

[tt]condition1 AND condition2 AND INLIST(Alltrim(status),"Married","Single")[/tt]
or
[tt]condition1 AND condition2 AND Alltrim(status) $ "Married,Single"[/tt]

Notice the subtle difference, INLIST compares the first to one or more additional parameters, $ only has one string it searches in, but you may put many status values in a comma separated list in that string.

A filter condition that only is using AND operations on the partial conditions is easier to maintain. If you have AND and OR you really need to think how you want the conditions to be combined and cannot only solve that by the positioning of the conditions. In general mechanically writing code is a bad idea. Don't simply get into habits and rules of thumb.

It's mostly ok to put parenthesis around conditions you OR, especially conditions about the same field, but it's also not a rule of thumb. You may also have filters, where you want one condition to overrule all the others.

In the other thread two conditions about an scode where ORed, the same rule applies to that, though there the OR was in the middle of a total of four conditions, these two ORed conditions have to be put into parenthesis.

Bye, Olaf.

 

[keepfoxing]

another thing since they are with the same title i 'll just put it here...

i have this table:

name |status|children|ageofchild
-------------------------------
doe |single|marry |11
doe |single|name2 |15
doe |single|name5 |18

john |single|name |13
john |single|name3 |15

fox |single|name4 |19


and what i want is to filter only name with all of his/her ageofchild >12

this is to show in my grid,
name
----
john
fox

since doe has ageofchild=11 (marry)

any idea? or solution

 

[Olaf Doschke]

You can't do this with a filter, since any filter expression looks at a single record only.
You need a query which groups data by name and has a condition on MAX(ageofchild)>12 for the group of childs of the same parent.

Bye, Olaf.

 

[Mike Lewis]

has a condition on MAX(ageofchild)>12

Olaf,

I might be wrong about this, but shouldn't that be MIN(ageofchild) > 12 - given that he wants people all of whose children are 12 or older?

Or am I suffering from my usual Friday afternoon syndrom?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

In other words:
[tt]
SELECT Name FROM TheTable GROUP BY Name HAVING MIN(AgeOfChild) > 12[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You're right, Mike. I was superficial this time, grasping any child with aged higher 12, not all.

Anyway, this is a condition on a group of data and not a single record. The condition people with status married or single is a condition on all data in a different way, it describes two groups being unioned, not an atribute of a group like the min child age is, but attributes of two groups of records, that unite. Such things can be asked about each record: Do you belong to the one or other group? But seeing the age of a single child you can't determine the min or max age of all other children.

This marks the superiority of sql queries. You would need an index on [tt]name+str(ageofchild)[/tt], open the table with alias "secondary" in that sort order and then set RELATION to main table name into secondary alias. Then each record would point to the record with the youngest child and a filter can be about secondary.ageofchild. But I wouldn't recommend going that route, this is not very elegant and barely self documenting why this relation is needed, you don't see right away this is about the minimum age of all children of a certain parent as MIN(ageofchild) clearly states.

Bye, Olaf.

 

[keepfoxing]

thankx for the response.
here is the output:

name
----
doe
john
fox

there is still doe's name there.

 

[vgulielmus]

I believe you've missed something.
Here is the proof of the solution and a second one, using a subquery.

CREATE CURSOR TheTable (name C(25),status C(10),children C(25),ageofchild I)

INSERT INTO TheTable VALUES ('doe','single','marry',11)
INSERT INTO TheTable VALUES ('doe','single','name2',15)
INSERT INTO TheTable VALUES ('doe','single','name5',18)

INSERT INTO TheTable VALUES ('john','single','name',13)
INSERT INTO TheTable VALUES ('john','single','name3',15)

INSERT INTO TheTable VALUES ('fox','single','name4',19)

* solution 1 aggregate
SELECT Name FROM TheTable GROUP BY Name HAVING MIN(AgeOfChild) > 12
* solution 2 subquery
SELECT distinct Name FROM TheTable WHERE name NOT in (SELECT name FROM TheTable WHERE AgeOfChild <= 12)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/