To ignore or to ignore, that is the....

[keepingbusy]

Hello guys

I am looking for some guidance on the following:

If I have two variables on a form, lets say msurname and mforename, the user can enter either or both to search. In this scenario, I would normally have a case statement that picks up which variable has been completed or not as the case may be:

*  If the msurname is EMPTY....

CASE EMPTY(msurname) AND NOT EMPTY(mforename)
  SELECT * FROM MYTABLE WHERE FORENAME=mforename ORDER BY ;
    SURNAME ETC ETC

or

*  If the mforename is EMPTY....

CASE NOT EMPTY(msurname) AND EMPTY(mforename)
  SELECT * FROM MYTABLE WHERE SURNAME=msurname ORDER BY ;
    SURNAME ETC ETC

*  If both are used....

CASE NOT EMPTY(msurname) AND NOT EMPTY(mforename)
  SELECT * FROM MYTABLE WHERE SURNAME=msurname AND FORENAME=mforename ;
    ORDER BY SURNAME ETC ETC

Ok, this is fairly straight forward if you only have one or two variables to contend with. I am sure it is possible to have multiple variables so the user can search on many fields within a table, but if you use this method, CASE statements are going to get very long and complex.

Is there a way or some SQL command that caters for this.

I am looking to use about ten searchable fields in a table with Version 9 of VFOX.

I would appreciate some guidance on this.

Thank you


Lee

 

[TamarGranor]

The way I handle this kind of request is to build up the WHERE clause one item at a time and then substitute it into the query. So instead of your code, I'd had something like:

cWhere = "WHERE .T."

IF NOT EMPTY(m.mSurname)
  cWhere = m.cWhere + " AND Surname = " + m.cSurname
ENDIF

IF NOT EMPTY(m.cForename)
  cWhere = m.cWhere + " AND Forename = " + m.cForename
ENDIF

SELECT * ;
  FROM MyTable &cWhere ;
  ORDER BY ...

This way, you have a simple IF for each item.

Tamar

 

[TamarGranor]

One additional point. My approach also means you have only one query, so that when you need to change the field list or the ORDER BY or something, you have to do it only once.

Tamar

 

[keepingbusy]

Hi Tamar

Thank you for the very quick response. So to understand this, what you are saying is for each variable I have, you add an IF ... ENDIF statement, build this up with all the variables available and then use the SQL command?

One thing I don't understand as I've not seen it before is:

" AND Surname = "

Could you (or someone) please explain this?

Many thanks

Lee

 

[Mike Lewis]

Lee,

You wouldn't see

" AND Surname = "

on its own. It would be something like:

" AND Surname = " + m.TargetSurname

Basically, what's Tamar is saying is that for each search term that is present (not empty), you concatenate it to the WHERE clause, which you end up macro-executing.

Hope that makes sense.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[keepingbusy]

Hi Mike

That makes sense now.

Thank you

Lee

 

[StewartUK]

Lee,

Assuming you are using textbox or something similar for the user to enter search criteria you don't need to use variables - you could instead put the textbox value directly in as you build up the WHERE clause.

Another thing you could consider is to loop through each object on the form and so build up the WHERE clause. You could then arrange it so that if you added more search boxes you wouldn't (hopefully) have to change the coding of the where clause.

Hope that helps,

Stewart

 

[keepingbusy]

Hi Stewart

Bit lost with your suggestion, could you give me some guidance.

By the way, I am using text boxes on a form.

Thank you

Lee

 

[Mike Lewis]

Lee,

I think what Stewart is saying is that, instead of copying the contents of the text boxes (the ones you use to enter the search terms) into memory variables, you just reference the contents of the text boxes directly.

In other words, instead of this:

lcSurname = ALLTRIM(thisform.txtSurname.Value)
....
... WHERE Surname = m.lcSurname

you simply do this:

... WHERE Surname = ALLTRIM(thisform.txtSurname.Value)

At least, that's what I understood.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[keepingbusy]

Thank you Mike.

Lee

 

[StewartUK]

Hi Lee,

Sorry about that, I see Mike partly clarified what I meant, although I was thinking of still building up a string for macro execution, so instead of

" AND Surname = " + m.TargetSurname

you would have

" AND Surname = [" + THISFORM.txtSurname.Value + "]" && note the extra quotes

...when building up the macro string, I'm sure you need to put quotes around the values you are inserting otherwise you get something like

...AND Surname = Smith AND Forename = John

which VFP wil think means a variable called Smith.


As for my 2nd paragraph, I was talking about something like this (where you would put the field name in say the Tag property of the textbox):

FOR EACH obj IN THISFORM.Objects
  IF obj.BaseClass = [Textbox] AND NOT EMPTY(obj.Value)
    cWhere = cWhere + " AND " + obj.Tag + " = [" + obj.Value + "]"
  ENDIF
ENDFOR

...but maybe that's getting too fancy.

I hope that helps,

Stewart

 

[keepingbusy]

Hi Stewart

Thank you for posting the example, very helpful indeed.

Regards

Lee

Windows Vista
Visual FoxPro Versions 6 & 9

http://www.customsoftware.co.uk