Hi everybody,

I need to write a report that displays personnel information and gives the user the ability to search(case insensitive) based on first name, last name or both and shows all records if nothing is selected. I'm fairly new to WebFocus and I was wondering if there is any search feature available in WebFocus or do I have to code a routine to accomplish this goal. What is the best way to do this?
Any help is greatly appreciated.

The prompt for the user can take any input ThAt the user types in.

-SET &TESTVAR=UPCASE(&INVAR.LENGTH, '&INVAR.EVAL', 'A&invar.LENGTH');

WHERE test on the Upper case value

WHERE UPCASE(column length, COLUMN , 'Acolumn length') EQ '&TESTVAR.EVAL';

If you want all if there is no match then use a double request.

TABLE FILE X
PRINT x y z
WHERE UPCASE(column length, COLUMN , 'Acolumn length) EQ '&TESTVAR.EVAL';
BY whatever
ON TABLE HOLD
END
-RUN
-IF &LINES NE 0 GOTO GOTIT;
TABLE FILE X
PRINT x y z
BY whatever
ON TABLE HOLD
END
-RUN
-GOTIT
TABLE FILE HOLD
etc. etc.

You have to design the GUI portion to allow users to input what they want to search, then you pass that value to your TABLE query. In the table query, you put where statements, such as (assume your data is alpha. If numeric, you don't need single quote and don't need .EVAL either).

WHERE FIELDNAME CONTAINS '&VALUE.EVAL'
OR
WHERE FIELDNAME EQ '&VALUE.EVAL'

You can also use wild card with LIKE, depends on if you want to match all firstname or match part of firstname.

If you are using 'first name, last name or both' unless you use a case conversion test you will
never guarantee that the test matches the case of the stored data.

Numerics are always case insensitive.

Also bear in mind that ‘LIKE’ or ‘CONTAINS’ tests in WebFocus are DB column format dependant.
Use LIKE ‘%_’ as the wild card for relational DB’s that use varchar.
Use CONTAINS ‘$*’ for fixed length columns.

Any prefix to the wild card portion IS case sensitive.

ie LIKE 'XXX%_' will not find 'xxx%_' and vice versa.

When creating "Search" queries for our users, we've found that they prefer to break this up into components. We usually use a radio button control where they would select

1. Begins with, Contains, or Equals
2. Search in First Name, Last Name, Both

and a Text box control for the search string

Then we build the query using &vars that are passed.

As JG mentions, you have to be careful with case. We just convert to uppercase.

-SET &NAMSRCH=UPCASE(&NAMSRCH.LENGTH,&NAMSRCH,'A&NAMSRCH.LENGTH');
-SET &SNAM=IF &NAMSRCH GT ' ' THEN (&NAMSRCH || '%');

then we have the following WHERE section to decide how the query will work:

-IF &NAMSRCH EQ ' ' THEN GOTO SKIPNAMSRCH;
-IF &SEARCHTYPE NE 'Begins' GOTO SKIPBEGIN2;
WHERE (POL_NAM LIKE '&SNAM' );
-GOTO SKIPNAMSRCH
-SKIPBEGIN2
-IF &SEARCHTYPE NE 'Equals' GOTO SKIPEQUAL2;
WHERE (POL_NAM EQ '&NAMSRCH' );
-GOTO SKIPNAMSRCH
-SKIPEQUAL2
-IF &SEARCHTYPE NE 'Contains' GOTO SKIPNAMSRCH;
WHERE ( POL_NAM CONTAINS '&NAMSRCH' );
-SKIPNAMSRCH

(Our database is DB2)

Thank you all so much, I'll use the tips and see if I can make it work.

Thanks again,
Robab