Search Function

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.

Posts: 35 | Location: Tallahassee,Florida | Registered: March 16, 2007

<JG>

posted

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.

Lusheng

Platinum Member

posted

Hide Post

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.

Posts: 118 | Location: Omaha, NE | Registered: June 12, 2003

Ignored post by Lusheng posted

Show Post

<JG>

posted

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.

Darin Lee

Virtuoso

posted

Hide Post

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)

Regards,

Darin

In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat

Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007

Ignored post by Darin Lee posted

Show Post

Robab

Silver Member

posted

Hide Post

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

Thanks again,
Robab

WebFocus 7.1
Developer Studio

Posts: 35 | Location: Tallahassee,Florida | Registered: March 16, 2007

Ignored post by Robab posted

Show Post