| Author |
Message |
AndyLeates
Newbie
Joined: 05 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 23
|
   Topic: Formatting
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
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
|
|
IP Logged |
|
AndyLeates
Newbie
Joined: 05 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 23
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
|
|
IP Logged |
|
AndyLeates
Newbie
Joined: 05 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 23
|
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!!
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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)?
|
|
IP Logged |
|
AndyLeates
Newbie
Joined: 05 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 23
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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?
|
|
IP Logged |
|
AndyLeates
Newbie
Joined: 05 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 23
|
Posted: 13 Oct 2011 at 6:12am |
|
Yes, it is returning FALSE on all rows, including the blank ones
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
|
|
