FIND WITHIN TEXT STRING "LIKE" 1

[spysab]

Hi Guyz!

Need some help to maximize search filter of my program. Im new in foxpro. Just reading codes and hows it working.

i have the following

employee.dbf
form1
grid1
cboField
txtsearchText

in form1, grid1 combo textbox are there.

combo is for the fields of form1 (example: name, company, address) where to search from
textbox1 is the text to find.

i want to extract all records having the value of textbox whether its in the beg middle or end.
this my existing code which only find records base on the beginning word

LOCAL cField, ctext, nItem
** get the selected field item#

nItem = thisform.cboField.ListIndex
IF EMPTY(thisform.aflds[nItem,2]) then
cfield = thisform.aflds[nItem,1]
ELSE
cfield = thisform.aflds[nItem,2]
ENDIF

** get value to search for
ctext = thisform.txtsearchText.Value

** call the filter routine (method)
thisform.setfilter(cField, cText)

 

[Olaf Doschke]

This code can stay as is.
You need to llok into the setfilter method and change that.

Post that code here and please put it between [ignore]

....your code...multiple lines...

[/ignore] to have a code section like this:

LOCAL cField, ctext, nItem
** get the selected field item#

nItem = thisform.cboField.ListIndex
IF EMPTY(thisform.aflds[nItem,2]) then
cfield = thisform.aflds[nItem,1]
ELSE
cfield = thisform.aflds[nItem,2]
ENDIF

** get value to search for
ctext = thisform.txtsearchText.Value

** call the filter routine (method) 
thisform.setfilter(cField, cText) && to get LIKE filtering this routine has to change

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[spysab]

this is my setfilter code.

LPARAMETERS tcFieldName, tcSearchText

SET FILTER TO  && clear any previous filters

&& use macro expansion to implement search filter
SET FILTER TO ALLTRIM(UPPER(&tcFieldName)) = ALLTRIM(UPPER("&tcSearchText"))

GO TOP
thisform.Refresh()

 

[Olaf Doschke]

OK, it's easy to adjust this:

LPARAMETERS tcFieldName, tcSearchText

SET FILTER TO  && clear any previous filters

&& use macro expansion to implement search filter
SET FILTER TO ALLTRIM(UPPER("&tcSearchText")) $ UPPER(&tcFieldName)

GO TOP
thisform.Refresh()

Mainly swapping out the operator = with $, but also switching the order, what you look for has to come first, "a" $ "abc" is true "abc" $ "a" is not true. Read the $ operator as "included in". What also has become unimportant is trimming the field value, therefore no ALLTRIM() anymore.

Another solution would be using AT and looking for it to be >0, AT also allows case insensitive searching without applying UPPER on both sides. Both things are not optimizable with any index, but should work out with good enough performance as long as the number of records is small enough.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

One more optimization you can also apply to the original code: Instead of using the Expression [tt]ALLTRIM(UPPER("&tcSearchText"))[/tt] you can first set tcSearchText to the trimmed upper version of itself and then just use [tt]"&tcSearchText"[/tt]

So:

LPARAMETERS tcFieldName, tcSearchText

tcSearchText = ALLTRIM(UPPER(tcSearchText))

&& use macro expansion to implement search filter
SET FILTER TO "&tcSearchText" $ UPPER(&tcFieldName)

GO TOP
thisform.Refresh()

This way the filter expression won't need to redo ALLTRIM(UPPER("&tcSearchText")) in every evaluation.

Just a quiz question (for anyone): Why isn't the same thing applicable to [tt]UPPER(&tcFieldName)[/tt]?

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Why isn't the same thing applicable to UPPER(&tcFieldName)?

Presumably becaues tcFieldName contains the name of a field, not its value?

For example, if you wanted to find the string "Olaf" in the Customer field, applying the UPPER() function to tcFieldName would simply mean that you were searching in CUSTOMER, which is no different from searching in Customer or customer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Exacly, Mike.

both customer and CUSTOMER will be the field name and while VFP isn't case sensitive in names, using lower or upper case names doesn't make it read the values of the fields lower or upper case, or in other words, since you want to search within the data turned to upper case, you have to do that "at runtime", each time the expression is evaluated, you can't just get this done once at "compile time".

More generally looking at an example where the trimming and turning to upper case is necessary perhaps illustrates that better:

Just make the assumption tcFieldName is "customer" and tcSearchText is " olaf ", as Mike did (kind of).

The original SET FILTER would then do this:

SET FILTER TO ALLTRIM(UPPER(customer)) = ALLTRIM(UPPER(" olaf "))

And just looking at the righthand side, no matter how many records this is done to, ALLTRIM(UPPER(" olaf ")) always stays the same, it is "OLAF", therefore it pays to do this once in advance. ALLTRIM(UPPER(customer)) will change per record, therefore you can't cut corners here. I already explained why the change to an in-string search makes ALLTRIM optional. but to make that clear in the new filter condition:

SET FILTER TO "OLAF" $ UPPER(customer)

This will be applied to all data and so if you have data about customers Mike, Olaf, and more that will find it, using just one specific customer will either be true ar false overall.

And at this time, this specific search can be sped up, if you have an index on the whole expression "OLAF"$customer, but it couldn't be used when the search text becomes "MIKE". You can't have indexes for all possible search terms. Upper(field)=value is optimizable with an index on upper(field), no further exact expression, as indexes can be used for "starts with" comparisons.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[spysab]

Olaf Doschke!!

Thank you! works perfectly.

one more thing please.
on that same code and form, what adjustment should be done to update the form every time you type any changes on textSearchtext? so you dont need a FIND button to get the records.

that would be the last thing i need.

 

[Olaf Doschke]

You'll put the code of the FIND button Click event to the InteractiveChange event of txtsearchText.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[spysab]

Olaf Doschke!

thanks again. a very simple solution to a very simple problem that seems hard for a beginner like me.
Having this, i would be able to create multiple types of forms and data extract applying same principle.

2 thumbs up!

 

[Mike Lewis]

Spysab,

Could I suggest one very small improvement to your solution. Move the line that says [tt]thisform.refresh[/tt] from your Setfilter method to the main calling routine. Place it at the very end of that routine, that is, immediately after [tt]thisform.setfilter(cField, cText)[/tt].

The reason is that the Setfilter method is not aware of the form or any of the controls on it. It should be a generic method for setting that particular type of filter. The main routine, similarly, does not "know" about how the filter is set. Its main concern is getting the parameters from the user. By moving the code in the way I suggest, you separate the functionality of the the Setfilter routine from the user interface of the main code.

This won't make an ounce of difference to the functionality or performance of the code. It is just done for neatness, and to keep things in the right place.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

able to create multiple types of forms and data extract applying same principle.

In principle,

you'll stumble on a few things:
1. $ or AT() only is applicable to character type fields (char/varchar/memo)
2. You'll always have 1 field in a table (or it isn't a table, of course), so this "exception handling" always works:

nItem = thisform.cboField.ListIndex
IF EMPTY(thisform.aflds[nItem,2]) then
cfield = thisform.aflds[nItem,1]
ELSE
cfield = thisform.aflds[nItem,2]
ENDIF

Corner cases:
a)Combobox unused may lead to ListIndex being 0 and you can't address array element 0.
b)Because of point 1): If the field type is not working for this type of textual search you have to think of something else.

VFP also has a very general filter form example as the _filterdialog class. You can look into it with

Modify class  _filterdialog of Home()+"ffc\_table"

This overall has the general idea to put together a query condition for a SQL query. Finally executing SQL and not just a SET FILTER.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name