| Author |
Message |
Doolhoff
Newbie
Joined: 14 Nov 2016
Online Status: Offline
Posts: 22
|
   Topic: Pull a single word from a string through formula?
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.
Edited by Doolhoff - 16 Jan 2018 at 11:39am
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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()
|
|
IP Logged |
|
Doolhoff
Newbie
Joined: 14 Nov 2016
Online Status: Offline
Posts: 22
|
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.
|
|
IP Logged |
|
Doolhoff
Newbie
Joined: 14 Nov 2016
Online Status: Offline
Posts: 22
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
Doolhoff
Newbie
Joined: 14 Nov 2016
Online Status: Offline
Posts: 22
|
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?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
Doolhoff
Newbie
Joined: 14 Nov 2016
Online Status: Offline
Posts: 22
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 19 Jan 2018 at 7:38am |
|
glad you got it sorted out
|
|
IP Logged |
|
|
|
