Crystal Reports Formula based on Text in a field

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Formula based on Text in a field

Author	Message
bremen Groupie Joined: 28 May 2013 Online Status: Offline Posts: 93	Topic: Formula based on Text in a field Posted: 03 Jul 2013 at 5:52am
	I am trying to create a formula that can display certain characters based on text in a field. It is not gaurnteed if the field will have anything in it, and if it does it might not be in the same spot every time as this field is free form text. So if {table.field} contains "Blue" anywhere in it, then display as "BL"
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 03 Jul 2013 at 6:03am
	if instr({table.field},"Blue")>0 then "BL" //note this formula is currently case sensitive
	IP Logged

bremen Groupie Joined: 28 May 2013 Online Status: Offline Posts: 93	Posted: 03 Jul 2013 at 6:41am
	So i put that in and it worked for searching for the characters in the string. Thank you However, the rest of my formula isnt working so well. if instr({tabe1.field1}, "Blue")>0 then "BL" else {table2.field2} I is not pulling in the data from tabe2.field2
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 03 Jul 2013 at 6:45am
	did you join table 1 and table 2 together? if you place the fields from table 1 and table 2 onto the detail section what do you get? Edited by DBlank - 03 Jul 2013 at 6:45am
	IP Logged

bremen Groupie Joined: 28 May 2013 Online Status: Offline Posts: 93	Posted: 03 Jul 2013 at 6:48am
	Yes the tables are joined. When I drop the fields into the report on thier own i see the data as i should.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 03 Jul 2013 at 6:56am
	by chance are most of table 1 options NULL?
	IP Logged

bremen Groupie Joined: 28 May 2013 Online Status: Offline Posts: 93	Posted: 03 Jul 2013 at 7:04am
	Id say its about 50/50 where table1 is null
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 03 Jul 2013 at 7:10am
	in the formula editor switch the option to the 'use default values for nulls'
	IP Logged

bremen Groupie Joined: 28 May 2013 Online Status: Offline Posts: 93	Posted: 03 Jul 2013 at 7:14am
	that did the trick. Thank you.
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum