Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I love the structure of the site. You start at the top, and drill down to what you want. Maybe I've been using Unix too long... :-) "
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > Reporting Solutions > Business Objects: Crystal Reports 1 Formulas Forum

I need to filter text from within a

thread767-1688482
Read More Threads Like This One
davismar (IS/IT--Management)
17 Jul 12 14:49
I need to filter text from within a field name called: tblServiceOrders.GeneralResolutions - this is a memo field
in Crystal Reports XI.

Within the text of the database, I need to set certain criteria where the text contains the following ONLY:

WS
Windstream
DID

Can someone tell me what the proper formula would be?
Also, if the return of the formula = true or false, how can I display only the records that meet this specific critera?

Thanks!

See attached report for the example
MCuthill (TechnicalUser)
17 Jul 12 16:46
DavisMar,

Not 100% for Crystal Reports XI (as I use 10), but the following should identify if all the strings exist in your field. You should then be able to filter the report on this formula field.

{@SubStringsExist_YN}

CODE

IF 
(
   InStr({tblServiceOrders.GeneralResolutions},"WS")>0 AND 
   InStr({tblServiceOrders.GeneralResolutions },"Windstream")>0 AND
   InStr({tblServiceOrders.GeneralResolutions },"DID")>0 
)
THEN "Y" 
ELSE "N"

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

MCuthill (TechnicalUser)
17 Jul 12 16:47
oops. Please remove the last space from the field name in the 2nd and 3rd "AND" clauses.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

jlopeman (IS/IT--Management)
17 Jul 12 17:16
The above solution will work as long as each record includes WS, Windstream, and DID in the memo. If its possible to have different permutations, i.e., includes WS and DID but not Windtream, then you will want to use "OR" statements instead of "AND"
davismar (IS/IT--Management)
17 Jul 12 17:43
Thanks for the responses.

Once more clarification -

If I want to use only WS and exclude results that contain WS - how would I need to change this formula?

For example - if WS is found within TWS, I want to exclude TWS and only pull records with WS only?

Thanks!
Madawc (Programmer)
18 Jul 12 7:11
Create a formula field saying

CODE --> @YourSelect

tblServiceOrders.GeneralResolutions = ["WS", "Windstream", "DID"]
Display it on unselected data, it should say True or False as expected.

Then add @YourSelect to your record selection - it is a boolian, no need to explicitly test the result.

(If you did want text within a larger string, you would say "IN"

yinyang Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP yinyang

MCuthill (TechnicalUser)
18 Jul 12 10:18
davismar,

Quote:

If I want to use only WS and exclude results that contain WS - how would I need to change this formula?

For example - if WS is found within TWS, I want to exclude TWS and only pull records with WS only?

For something like this (I presumed exclude TWS in addition to including the other three), you would change the formula to:
{@SubStringsExist_YN}

CODE

IF 
(
   InStr({tblServiceOrders.GeneralResolutions},"WS")>0 AND 
   InStr({tblServiceOrders.GeneralResolutions },"Windstream")>0 AND
   InStr({tblServiceOrders.GeneralResolutions },"DID")>0 AND
   InStr({tblServiceOrders.GeneralResolutions },"TWS")=0 AND
)
THEN "Y" 
ELSE "N"

InStr() returns a 0 if a substring does not exist within the full string (and a 1 when it does exist), I think the above is what you seek.

Another option would be that if WS is always preceeded and followed by a space, you could change the first InStr() statement to the following (instead of adding the TWS exclusion):

CODE

...InStr({tblServiceOrders.GeneralResolutions}," WS ")>0 AND...

Cheers! Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Business Objects: Crystal Reports 1 Formulas

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close