Using "Contains" in a Formula

[harrietohs]

Crystal 8.5.

I have fields that are strings like:

CAPITAL CAMPAIGN; WILDLIFE CAMPAIGN
FERAL CAMPAIGN; CAPITAL CAMPAIGN

In other words, a list separated by semi colons.

Is there a formula I can use to find strings that contain certain text, regardless of where it is in the string? Something like {myfield.name}contains 'CAPITAL' ?

Is it any different, easier in Crystal XI?

Thanks,

Harriet
Ottawa Humane Society

 

[synapsevampire]

There are a couple of whays, but you don't state what you want to do as a result of finding them.

{table.field} like "your text"

or you can use:

instr({table.field},"your text") > 0

Which returns the starting location.

And you can use

split({table.field},";") to create an array of the elements separated by the ;

So yes, there are several ways, now post the intent.

If you only want rows returned to the report that match the text, use Report->Selection Formulas->Record:

{table.field} like "your text"

btw, all of these work with either version.

-k

 

[rosemaryl]

Dear harriet,

You have several options:

1. Use In function to check.

If 'Captial' in {Table.Field}
then 1
else 0

2. Use Instr (Instring) function:

if InStr({Table.Field},'Capital')
then 1
else 0

3. Use Like:

If {Table.Field} like '*Capital*'
then 1
else 0

Use Crystal's help feature to look each one up as there are overloads (arguments) for Instr and LIke that extend the functions.

The danger of all of the above is that they all find any occurance of the text in the string. So if you intended Capital Improvements and the string contains Capital Expense then it will still return true.

Also, you could get more complex if you, for instance, need to know what position in the string the phrase occurs.

If you intend to select records based on whether a field contains a particular string it is probably best to use a SQL Expression as that can pass to the database while a formula cannot.

regards,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[rosemaryl]

Dear Harriet,

A correction on my second example Instr ... I was copying and pasting and example 2 should read:

if InStr({Table.Field},'Capital') > 0
then 1
else 0

All my examples were to show an example usage when the intent was to return a number (1 for yes and 0 for no) so that you sum the number of records that contained the value.

regards,

ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[harrietohs]

My intent is to allow a user to enter the name of the fund they wish to see included on the report. I think InStr will do it.

My select formula is

InStr({Gf.Gf_Fund},"@Fund") > 0

The parameter @fund collects the Fund Name to be extracted from the string.

I still want to play with your other suggestions, such as using a array.

Thanks very much for the tips.

Harriet

 

[synapsevampire]

Instr probably won't pass the SQL to the database in CR 8.5 (check the Database->Show SQL Query for this to make sure that the criteria is processsed on the database).

I'd use:

{table.field} like "*"+{?MyParameter}+"*"

That generally passes in CR 8.5 and will return the proper results providing what is entered is tailored well enough.

-k

 

[rosemaryl]

Dear Harriet,

That statement in the Selection Criteria will not pass to the SQL Where Clause. The result will be poor performance. IF that is your only selection criteria then every record in the table will be returned and then Crystal will filter the records.

Are there a limited number of funds and a maximum that are ever in the field? The reason that I ask is that it may be better to create separate SQL Expressions that extract the fund name stored from each "position" in the string and then to state:

(
{%Fund1} in {?FUND}
or
{%Fund2} in {?FUND}
or
{%Fund3} in {?FUND}
...
)

Another option would be to base the report on a Stored Procedure or Command.

regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[harrietohs]

There is no definite limit to the funds in the string.

When I test using my above setup, I am indeed getting the results I desire - only those lines containing the fund I select are appearing.

Harriet

 

[synapsevampire]

Yeah, it will return the proper results, at issue is simply performance, if you're happy with it, you're done!

-k

 

[rosemaryl]

Dear Harriet,

I didn't mean to indicate that what you are doing is wrong. It is not wrong, it just isn't optimal.

I only wanted you to know the ramnifications ... as when the table grows to 300,000 records for example, what was once acceptable, may no longer be acceptable.

The reason report writers should be concerned about performance is that you might not know how many users will ever run the report at the same time ... I have been to many client sites who have reported database performance issues that turned out to be a few poorly written reports that were being run by many users and dragging down the database.

Best regards to you,

ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[harrietohs]

I wasn't insulted!!!! And I totally agree re performance issues.

I should have told you that the estimated max. potentital amount of records being dealt with for this partictular report will likely never be more than 1000 and there will be only one person running the report. The export is pre-filtered to send out only the split gift records (donation on one payment, but split among multiple funds) , which are a small minority of a larger donation database. Unfortunately, even though the export eliminates the dollar amounts for funds other than those I wish to include, it insists on exporting the string with all of the funds chosen,with each line.

Harriet