Pull a single word from a string through formula?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22524
Printed Date: 25 Apr 2024 at 12:21pm
Topic: Pull a single word from a string through formula?
Posted By: Doolhoff
Subject: Pull a single word from a string through formula?
Date Posted: 16 Jan 2018 at 9:38am
I am attempting to create a new report, but I am having some great difficulty trying to get it to do what I need it to.
I am trying to get a single word to pull from a string. The I have tried to use the "like" function to no avail. I am trying to get the word "white" to pull from the string "5.75in Pinfed Rem White". I cannot figure this out. I have searched several forums and the help function in Crystal itself, again to no avail.
Please help.
|
Replies:
Posted By: DBlank
Date Posted: 17 Jan 2018 at 2:32am
When you say 'pull' do you mean you want display the existing string without that word in it?
try using REPLACE()
|
Posted By: Doolhoff
Date Posted: 17 Jan 2018 at 9:56am
|
When I say pull I am wanting to display the string with only that word. Sorry for the confusion. I should have used a different way to ask that.
|
Posted By: Doolhoff
Date Posted: 18 Jan 2018 at 8:25am
|
If what I am trying to do isn't possible, please let me know so I can make a new plan for my report.
|
Posted By: DBlank
Date Posted: 19 Jan 2018 at 2:28am
I don't see the need to actually pull the word. You just need to choose to display the word if it exists.
You can use an instr() or LIKE to that.
If instr(table.field,'white',1)>0 then 'white' else ''
the value of 1 means it is case-insensitive, if it should be case sensitive change it to 0.
|
Posted By: Doolhoff
Date Posted: 19 Jan 2018 at 7:21am
|
I've just tried this and it is working to a degree. Here is the exact way I have the formula: "If instr ({PM_Item1.ItemShortDesc},'white',1)>0 then 'White' else '0001'". I put the 0001 in just to make sure, visually, that the formula was working. Which brings me to my new issue, if the word I'm searching for isn't there I get nothing. Did I do something wrong?
|
Posted By: DBlank
Date Posted: 19 Jan 2018 at 7:28am
1. make sure you are not putting that formula in the select criteria, but rather ti should be a formula field in the field explorer then placed on the report canvas to display the result.
It will not deal with a NULL. hat would return nothing. In the formula editor set the formula to 'use default values for nulls' to fix that.
|
Posted By: Doolhoff
Date Posted: 19 Jan 2018 at 7:36am
|
Thank you DBlank! I am a little abashed to admit that I found the issue... and it wasn't the nulls. I found that I had an if then statement for '' to return ''. I'm sure that the null treatment probably didn't help though.
|
Posted By: DBlank
Date Posted: 19 Jan 2018 at 7:38am
|
glad you got it sorted out
|
|