Param. Field passing to Select Expert; Adv'd; - HOW?

[Drakhan]

Here is my scenario, I have a parameter field which requests a user ID, which is proper name, like 'Smith'. The report contains 5 "key" fields which could contain this proper name.

The goal is to generate a report which uses the parameter field to query against all of the key fields and then display the results. Below is an example:

In the SQL database:

table1.key1
table1.key2
table1.key3
table1.key4
table1.key5

The data contained in each key field is a name, could be anything J. Smith, Smith, Smith J. and these values could appear in more than one field.

The report looks like this:

?Name - parameter field

The 'Select Expert' formula, is written like this:

{table1.key1} in {?Name} or
{table1.key2} in {?Name} or
{table1.key3} in {?Name} or
{table1.key4} in {?Name} or
{table1.key5} in {?Name}

Nothing happens.

What I am attempting to accomplish is how do I get the {?Name} parameter field to query *each* {table1.keyX} fields to see if the name exists (in any form)?

The Select Expert does not have a "contains" value...is there any way to simulate or write a formula that will make Crystal view the data as "contains", rather than "begins with", "equal to", etc.? Thanks in advance for your help!

 

[synapsevampire]

The Select Expert is just a means to simplify writing record selection formulas, you can write more elaborate formulas by using Report->Edit Selection Formula->Record

What I believe you're trying to do is integrate wildcards into the search, which is best performed on the database rather than in Crystal, but here's a means to do it in Crystal:

{table1.key1} like uppercase({?Name})+"*" or
{table1.key2} like uppercase({?Name})+"*" or
{table1.key3} like uppercase({?Name})+"*" or
{table1.key4} like uppercase({?Name})+"*" or
{table1.key5} like uppercase({?Name})+"*"

The above will search for anything which starts with what was entered, and ends with anything.

{table1.key1} like "*"+uppercase({?Name})+"*" or
{table1.key2} like "*"+uppercase({?Name})+"*" or
{table1.key3} like "*"+uppercase({?Name})+"*" or
{table1.key4} like "*"+uppercase({?Name})+"*" or
{table1.key5} like "*"+uppercase({?Name})+"*"

The above will search for any part of the field containing
it.

There is also a ? which is used as a wildcard for a single character.

-k