Formatting
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14667
Printed Date: 02 May 2024 at 5:49am
Topic: Formatting
Posted By: AndyLeates
Subject: Formatting
Date Posted: 13 Oct 2011 at 4:08am
|
I have a formula to change the background colour of a field if it is blank if {UserDetail.DirectLine}="" or isnull({UserDetail.DirectLine}) then crRed else crWhite However, it is changing the background colour to Red on fields that contain a phone number, not the blank ones. What am I doing wrong? Thanks Andy
|
Replies:
Posted By: DBlank
Date Posted: 13 Oct 2011 at 4:27am
|
not exactly sure as your code is almost accurate...
a couple of things on to keep in mind though.
1. You cannot change background colros on NULL fields as there is nothing to change. To get around this you can make a formula field to insert "" for NUlls and then change the background of the formula field.
2. Whenever you are checking NULL always use it as the first condtion
if isnull({UserDetail.DirectLine}) or {UserDetail.DirectLine}=""
then crRed else crWhite |
Posted By: AndyLeates
Date Posted: 13 Oct 2011 at 4:44am
|
Thanks, I have switched round as suggested, also tried removing the ifNull part, leaving just if {UserDetail.DirectLine}="" then crRed else crWhite No change though, still the wrong way round. Andy
|
Posted By: DBlank
Date Posted: 13 Oct 2011 at 5:14am
|
Since you have to deal with NULLs I would try a formula field first. //PhoneReplacement
If isnull({UserDetail.DirectLine}) or trim({UserDetail.DirectLine})="" then "" else {UserDetail.DirectLine}
place this next to your original field on the canvas and make sure it is leaving phone numbers when they exist and leaving an empty string everywhere else.
If it is not working look cloasely at the items that are not working. Are there " - - " items instead of ""? or something similar.
if it is OK
right clcik on the formula field
select formay field
border tab
backgroundcolor formula
if currentfieldvalue="" then crRed else crNocolor
|
Posted By: AndyLeates
Date Posted: 13 Oct 2011 at 5:46am
|
If isnull({UserDetail.DirectLine}) or trim({UserDetail.DirectLine})="" then "" else {UserDetail.DirectLine}This worked fine, showed a number where there was one before, blanks where blanks if currentfieldvalue="" then crRed else crNocolor Blank fields still not coloured red!!
|
Posted By: DBlank
Date Posted: 13 Oct 2011 at 5:50am
|
You have to use this formula on the formula field not on the original "Directline" field...NULL records do not exist so they have no background and therefore cannot be colored. if you used tyhis on the formual field what exactly are you getting back form this (sample)?
|
Posted By: AndyLeates
Date Posted: 13 Oct 2011 at 5:56am
|
Yes, the formula is on the newly created field, not the original. None of the fields are being coloured, whether they contain text or not. Changing the ="" to <>"" colours the background Red where there is text, does nothing to the blank fields. |
Posted By: DBlank
Date Posted: 13 Oct 2011 at 6:08am
|
add another formula for testing isnull(@PhoneReplacement)
place this on the canvas
it should always be FALSE.
Is it FALSE on the blank rows?
|
Posted By: AndyLeates
Date Posted: 13 Oct 2011 at 6:12am
| Yes, it is returning FALSE on all rows, including the blank ones |
Posted By: DBlank
Date Posted: 13 Oct 2011 at 6:22am
|
So just to be sure here, if you right clcik on the @PhoneReplacement field
select format field
select border tab (not font tab)
click on the x-2 button to the right of the 'Background' (not border)
enter your formula here
if currentfieldvalue="" then crRed else crNocolor
save the the changes...
It still does not work?
Change your test formula to
@PhoneReplacement=""
This should be TRUE for you blanks and FALSE on everything else.
|
Posted By: AndyLeates
Date Posted: 13 Oct 2011 at 11:34am
|
I have double checked everything! Changed test formula as you described, and as you say, all blank rows now report TRUE, everything else FALSE
|
Posted By: DBlank
Date Posted: 13 Oct 2011 at 11:39am
|
try and change your color formula
if currentfieldvalue="" then color(255,0,0) else color(0,255,64)
what do you get?
|
Posted By: AndyLeates
Date Posted: 13 Oct 2011 at 11:43am
| Fields with a number are green, without remain blank / white |
Posted By: DBlank
Date Posted: 13 Oct 2011 at 11:47am
|
wow. I am stumped. It is acting as if the field is NULL...????
Try adding a bottom border single line to the field?
Does it appear for all records or only the ones that <> ""?
also try an inverted color attempt:
if NOT(currentfieldvalue="") then CrNocolor else CrRed
|
Posted By: AndyLeates
Date Posted: 13 Oct 2011 at 11:59am
|
Bottom border NOT present when the field is blank Inverted formula shows all rows with no colour. You've been more than helpful. Go do some real work! Andy
|
Posted By: DBlank
Date Posted: 13 Oct 2011 at 12:04pm
Waiting for my ride  ...
try this
change your @PhoneReplacement formula to :
If isnull({UserDetail.DirectLine}) or trim({UserDetail.DirectLine})="" then "No Phone" else {UserDetail.DirectLine}
In your color formula
if currentfieldvalue="No Phone" then crRed else crNocolor
|
Posted By: AndyLeates
Date Posted: 13 Oct 2011 at 12:09pm
|
Now that's what I call a workaround! I like your style.... Works perfectly, good enough for me. Thanks for that, hope your ride turns up soon. Andy
|
Posted By: DBlank
Date Posted: 13 Oct 2011 at 12:15pm
|
I still don't know why it was doing that...
if you want to get rid of the work around text make the text color conditional too
if currentfieldvalue="No Phone" then crRed else crBlack
This way the red 'No phone' ink color should disppear in the red background and mimic what you wanted in the first place and actual phone numbers will be black (assuming that is the color you want).
|
Posted By: AndyLeates
Date Posted: 13 Oct 2011 at 12:17pm
|
Hah, I just did that! Not so dumb after all... ;-) Thanks again, nearly midnight here, so time to hit the hay.
|