×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

FIND WITHIN TEXT STRING "LIKE"

FIND WITHIN TEXT STRING "LIKE"

FIND WITHIN TEXT STRING "LIKE"

(OP)
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)

RE: FIND WITHIN TEXT STRING "LIKE"

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 [code]....your code...multiple lines...[/code] to have a code section like this:

CODE

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

RE: FIND WITHIN TEXT STRING "LIKE"

(OP)
this is my setfilter code.

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() 

RE: FIND WITHIN TEXT STRING "LIKE"

OK, it's easy to adjust this:

CODE -->

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

RE: FIND WITHIN TEXT STRING "LIKE"

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

So:

CODE

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 UPPER(&tcFieldName)?

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: FIND WITHIN TEXT STRING "LIKE"

Quote:

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

RE: FIND WITHIN TEXT STRING "LIKE"

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:

CODE

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:

CODE

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

RE: FIND WITHIN TEXT STRING "LIKE"

(OP)
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.

RE: FIND WITHIN TEXT STRING "LIKE"

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

RE: FIND WITHIN TEXT STRING "LIKE"

(OP)
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!

RE: FIND WITHIN TEXT STRING "LIKE"

Spysab,

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

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

RE: FIND WITHIN TEXT STRING "LIKE"

Quote (spysab)

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:

CODE

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close