Keyword searches in description fields

[redbunny78]

Crystal 10 and Oracle

I would like to build a report that shows all tickets owned by a certain assignment group. Unfortunately that info is only found in a description field. How can I do a keyword search on that description field to find all tickets that contain '1-' or 'SR'?

Thanks so much!

 

[lbass]

Try a record selection formula like:

{table.field} like "*1-*" or
{table.field} like "*SR*"

-LB

 

[redbunny78]

Every time I try to save this as a record selection formula it does not seem to save. I don't get any errors but then when I go back to see the record selection it isn't there?

Any ideas?

Thanks!

 

[lbass]

Are you sure you are placing this in the right area? Don't use the report expert. Instead just go to Report->selection formula->record and enter it there.

-LB

 

[beltmanjr]

PS for oracle (and perhaps others) the wildcard is %.
However, since the answer came from lbass I would not discount * as wildcard

 

[redbunny78]

Yes! That worked. Unfortunately it brings back alot of junk but I guess that it so be expected from some keyword searches.

Thanks again!

 

[lbass]

If you are writing the selection formula in the CR GUI, you would use "*"; if in a command, you would use "%".

What kind of junk are you getting? Please provide samples. You might be able to narrow the search by adding spaces around the letters selected--if that's the way they appear in the field, for example.

-LB

 

[redbunny78]

Well, I am looking for results such as:

SR 1-98087019
SR 1-98358183
1-98465813

which I am getting but I am also getting things like 1-800 numbers, ticket numbers, etc.

I just verified that it will always contain 1-9 followed by 7 numbers.

Any suggestions?

 

[lbass]

You could try:

stringvar array x := split({table.field}," ");
numbervar i := 0;
numbervar j := ubound(x);
stringvar y;
for i := 1 to j do(
if x like "1-9*" and
len(x) = 10 then
y := x
);
y in {table.field}

This will be slow though as it won't pass to the SQL.

-LB

 

[smcnulty2000]

Consider trying this in record selection:

({table.field} like "*1-9???????*")

In addition to your other record selection criteria.

The question mark is a single character replacement.

It should also be able to send this to Oracle, so you get your speed back.

 

[redbunny78]

Thanks! That helped alot. How can I only show those ticket numbers on the report instead of all the other description info in that field?

 

[lbass]

You could use this formula in the detail section:

stringvar array x := split({table.field}," ");
numbervar i := 0;
numbervar j := ubound(x);
stringvar y;
for i := 1 to j do(
if x like "1-9*" and
len(x) = 10 then
y := x
);
y

-LB

 

[redbunny78]

Yes! That works. Thanks so much for all the help.