How to Create a search filter grid?

[OldtTimerDon]

I would like to create a grid similar that performs like a data query in Microsoft Office 2010.

Visualize an Office spreadsheet with the following columns: firstname, lastname, city, state.

In office, I can set a filter on each individual column. So, for example, I set state="AZ", I will see only those record that pertain to people who live in "AZ."
If I then set the firstname filter to "Don", I will then further restrict the records to AZ residents whose firstname begins with "Don." (Don, Donald, Donato, etc.) Now, I don't remember Don's lastname but if I now set the city filter to "Sun City", I will then see only the "Don(s)" who live in Sun City. This has been a feature of Excel for a number of years. Naturally, lastnames are displayed when each filter is added.

Any ideas about how I might implement this in VFP9?

Yup, the Senile Senior is trying to expand his knowledge...

Don
Sun City, AZ

 

[Olaf Doschke]

There is nothing similar implemented in the grid, nor the grid headers. The most similar feature of VFP is the SET FILTER TO command, perhaps first take a look at the help chapter of it.

An idea for the UI part would be to add textboxes outside and above the grid columns to be able to enter filter expressions. Then you'd have to do a routine to put together a filter expression from the inputs to those textboxes, I wouldn't use a static expression involving Text1.Value, Text2.Value etc. but eg allow entering ">2005" in a textbox above the "release year" column to filter for releaseyear>2005 and with a value "2005" filter for releaseyear=2005. So there would be some logic involved taking the filter values entered and compute a FOR condition for the SET FILTER TO comamnd.

Some of the controls for the filter might also be a combobox with all available values, if there are only few different values overall, that would make these columns quite similar to excel filter headers, but I wouldn't put this above all columns, otherwise you also wouldn't be able to filter for names beginning with "DON".

As you mention it as a feature you want, I will dive into this little detail about string comparison in VFP:

You perhaps know or remember how VFP compares strings in a "begins with" fashion, also see SET EXACT. By default, actually SET FILTER TO firstname="Don" will find Donald, Donato etc, too. If you SET EXACT ON you'd have to pad the filter value to field length, because eg with a CHAR(20) field SET FILTER TO firstname="Don" will not even find records with firstname being Don, as CHAR(20) stores that as "Don " with all the trailing spaces and EXACT ON would also need these spaces in the value you compare with. You can mix inexact and exact string comparison by using == for exact comparisons, so I would leave SET EXACT OFF, which is the default.

I actually did this last year in an inventory application for overview of a few hundred inventory items, but in general SET FILTER is something I avoid and rather put together a WHERE condition for a query. It's okay to SET FILTER on smaller amounts of data. One downside is, that a FOR condition offers less options han SQL WHERE, eg LIKE, BETWEEN, IN (list), IS NULL are SQL options. You can use the LIKE(), BETWEEN(), INLIST(), ISNULL() functions instead, but what you can't do in FOR conditions is subqueries. Also in SQL you can filter by INNER JOIN with a cursor of a list of values, which is more elegant and performant than INLIST or IN. Another less important downside of a FILTER is, it doesn't change the RECCOUNT() and that means the filtering does not reflect in the grids scrollbar. Some users have found it a bit irritating, but that's how it is.

Bye, Olaf.

 

[Koen Piller]

Hi,

Paul Gibson has just recently published a class doing exactly that, please read his postings in Foxite - Thread 381171 Message 381271. There is even an example published by Tony with message 381324.

Hope this helps you as well.

Regards,

Jockey(2)

 

[OldtTimerDon]

Jockey2,
Thanks for the the tip but I am having trouble getting to the message. I am not a member of Foxite but I visited the site and tried a search by thread which failed. I then tired the message id which also failed. Can you forward the actual URL?

The Senile Senior
Don
Sun City, AZ

 

[mjcmkrsr]

Hi OldTimerDon,

I have a couple of textboxes, a grid and a command button on the form.
In the form's command button click method put something like the following code.
You have of course to adapt it to your needs and your data

Local lcFilter
lcFilter = ""

lcFilter = IIf !Empty(Alltrim(This.TextBox1.Value), "AT(Alltrim(This.TextBox1.Value),YourTable.Field1) # 0 and", "")
lcFilter = IIf !Empty(Alltrim(This.TextBox2.Value), "AT(Alltrim(This.TextBox2.Value),YourTable.Field2) # 0 and", "")
...
lcFilter = IIf !Empty(Alltrim(This.TextBox_n.Value), "AT(Alltrim(This.TextBox_n.Value),YourTable.Field_n) # 0", "")

Select YourField(s) from YourTable(s) Where &lcFilter into cursor YourCursor

This works on one but also on multiple tables

hth

MK

 

[mjcmkrsr]

Please replace "This" with "ThisForm" in my previous answer
Sorry for the typo
MK

 

[mjcmkrsr]

It should read

Local lcFilter
lcFilter = ""

lcFilter = lcFilter + IIf(!Empty(Alltrim(This.TextBox1.Value)), "ATC(Alltrim(This.TextBox1.Value),YourTable.Field1) # 0 and ", "")
lcFilter = lcFilter + IIf(!Empty(Alltrim(This.TextBox2.Value)), "ATC(Alltrim(This.TextBox2.Value),YourTable.Field2) # 0 and ", "")
...
lcFilter = lcFilter + IIf(!Empty(Alltrim(This.TextBox_n.Value)), "ATC(Alltrim(This.TextBox_n.Value),YourTable.Field_n) # 0", "")

Select YourField(s) from YourTable(s) Where &lcFilter into cursor YourCursor

hth

MarK

 

[Koen Piller]

Hi OldTimerDon,

I agree the search facility on Foxite is not the best utility I have seen, the topic I am referring to is a question by Tony Vignone with heading ´Tables Indexes - Complex (2/58)`.
Hope this facilitates you to find the coding.

Regards,

Jockey