Cool solution: Test field for list of word embedded anywhere...

[MVisconte]

We need to test several fields (four) against a user-entered list of words. All fields will be tested for any of the terms embedded anywhere in them.

i.e., "UPS":
"User reports UPS failure at site."

i.e., "Power":
"Power supply failed"

The user enters their list of words into a parameter array. They can enter as many as they want.

Originally we used a formula we found on the net which takes the parameter list and converts it item by item into a wild-card-buffered list:

List: "UPS", "A/C", "POWER", "HEAT"

converts to "*UPS*", "*A/C*", "*POWER*", "*HEAT*"

which allows us to do something like:

AND
(
field1 like @KeyArray OR
field2 like @KeyArray OR
field3 like @KeyArray OR
field4 like @KeyArray
)

which USED to be translated (via Crystal) into

AND
(
{Field1} like "*UPS*" OR
{Field1} like "*A/C*" OR
{Field1} like "*POWER*" OR
{Field2} like "*UPS*" OR
{Field2} like "*A/C*" OR
{Field2} like "*POWER*" OR
... etc.


When we made a design change in the report, the WHERE quit parsing and the effect was to pull all x-hundred thousand records locally to extract the 20-50 records we actually wanted.

I thought the problem was the formula, so I played w/ built-in functions and came up with this:

split("*" & join({?Keywords}, "*, *") & "*", ", ")

It looks convoluted, but the result is pretty cool:

Array: "UPS", "A/C", "Power" becomes
STRING: "UPS*, *A/C*, *Power" which becomes
STRING: "*UPS*, *A/C*, *Power*" which becomes
ARRAY: "*UPS*", "*A/C*", "*Power*"

It is used like this:

{Field1} like
split("*" & join({?Keywords}, "*, *") & "*", ", ") OR
{Field2} like
split("*" & join({?Keywords}, "*, *") & "*", ", ") OR ...

Whew. That's what allows the fields to be tested for any of the words embedded anywhere in the field.

A user formula can be used to house the
split("*" & join({?Keywords}, "*, *") & "*", ", ")
which results in a much easier to view:

(
{Field1} like {@KeyArray} OR
{Field2} like {@KeyArray} OR
{Field3} like {@KeyArray} OR
{Field4} like {@KeyArray}
)


Cool, eh?


--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[synapsevampire]

I always like any tricks that get Crystal to pass the &%$#(#@ SQL to the database, and your use of the join and split functions are good stuff.

However you should note that different versions of Crystal handle SQL pass through differently, so please remember to post your version.

BTW, there are some standard SPs used in SQL Server and Oracle to do this sort of thing which should prove a bit faster, adn might be tweaked to return what you need. In those cases you don't have to bring in a table, you bring in the SP and then pass it the database or table of interest to search for.

-k