• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

Selection Formatting Question

Hey all....another question

I am using selection formatting to evaluate a field value and then apply color based upon the field value.  My formual is listed below....

Select CurrentFieldValue
    case is >= 1    :crRed
    case is > .75       :Color (244,134,11)
    case 0              :crNoColor
    case is <= .50      :crLime
    case is <= .75      :crYellow
    default  

I have a new value 'NB' which is text....How can I incorporate this into my code above...

I have tried

Select CurrentFieldValue
    case = 'NB'      :crRed
    case is >= 1    :crRed
    case is > .75       :Color (244,134,11)
    case 0              :crNoColor
    case is <= .50      :crLime
    case is <= .75      :crYellow
    default


That didnt work...I am pretty sure its because its text and not a number....Any push in the right direction would be helpful.

Thanks,
bob  
0
vmrfadmin
Asked:
vmrfadmin
  • 6
  • 5
  • 2
1 Solution
 
bdreed35Commented:
What is the data type of this field?  It must be text, but then that will not explain how the formula worked before since what you have had to have been numeric data.
0
 
vmrfadminAuthor Commented:
The date type in this field was all numberic.....now it can be numeric or text.......

0
 
bdreed35Commented:
There can only be one data type, so it must be text now.
Try changing it to this:

if CurrentFieldValue = "NB" then
    crRed
else
Select val(CurrentFieldValue)
    case is >= 1    :crRed
    case is > .75       :Color (244,134,11)
    case 0              :crNoColor
    case is <= .75      :crYellow
    case is <= .50      :crLime
    default                 :crNoColor
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
vmrfadminAuthor Commented:
I tried it out......Crystal gives an error message that a

"A number is required here"..........it referes to the "NB"
0
 
bdreed35Commented:
If it gives you that error than the field must be numeric and it can't contain the value "NB"
Try highlighting the field in the Fields list, right click it and choose Browse Data.
This will give you the data type and the first 500 unique values in the field.
Post the data type and what you found out about the data.
0
 
vmrfadminAuthor Commented:
The field is a formula, and this is the formula....


If  {Budget Amount Computer Services}= 0 and {Computer Services Expenses} = 0 then
    '0'
else
(
  IF {Budget Amount Computer Services}= 0 then
     'NB'
  else
     cstr(({Computer Services Expenses}/{Budget Amount Computer Services}) * 100)
)

The formula will return a numeric value or a text value.......

Does that make sense
0
 
ladylinetCommented:
Well, problem looks like lays in this part:

  case = 'NB'      :crRed
  case is >= 1    :crRed

 First case is string, second is number.  Change second one to string to:

 case = 'NB'      :crRed
 case is >= CInt('1')    :crRed

Lady Linet
0
 
bdreed35Commented:
It makes sense but it will actually only return text data like "NB", "0", or the calculation converted to text using the CStr function.

I am not sure why the last code I gave you is not working.  Perhaps we should simplify it for the time being to try and get it working.

Try this for now:

if CurrentFieldValue = "NB" then
    crRed
else
    crNoColor

Let me know if that works
0
 
vmrfadminAuthor Commented:
I  tried the simplified version....

if CurrentFieldValue = "NB" then
    crRed
else
    crNoColor

And it still complains about "A number is required here" .....referring to the NB.

The field that I am applying this formual to can either be a number or string field........

In the example above...I dont understand why Crystal cares if the field value is a number or string.....just evaluate based upon my formula......

bob
0
 
vmrfadminAuthor Commented:
ladylinet.....

I tried your code and have the same problem........It doesnt like that "NB" is a string........instead of a number.

Keep getting the "A number is required here"

bob
0
 
bdreed35Commented:
Bob:
The data may look like it is either numeric or text but the formula can only return one data type.  Because you put quotes around the 0 and used the cstr function, you have converted the numeric data to text.

Are you sure you have this in the font color for the formula and not in the font color for another field?
If you use the formula name instead of CurrentFieldValue do you get the same error:

if {@formula_name} = "NB" then
    crRed
else
Select val({@formula_name})
    case is >= 1    :crRed
    case is > .75       :Color (244,134,11)
    case 0              :crNoColor
    case is <= .75      :crYellow
    case is <= .50      :crLime
    default                 :crNoColor
0
 
ladylinetCommented:
Hi, Crystal cares if field is string or number, very much so. How can Crystal knows how to compare values like NB and 0? It can if they are both strings.

 It can't be both, so you should choose one, string will be better because you can use both 'NB' and '0'.

 Covert all cases into strings:

    case = 'NB'      :crRed
    case is >=  '1'    :crRed
    case is > '0.75'       :Color (244,134,11)
    case '0'              :crNoColor
    case is <= '.75'      :crYellow
    case is <= '.50'      :crLime
    default                 :crNoColor

Lady Linet
0
 
vmrfadminAuthor Commented:
bdreed35.....You got it.  

I had selected all the formulas at once and inserted the code CurrentFieldValue..........

When I went thru and specified the specific formula and removed the currentfieldvalue it works like a charm.  The downside...I have to setup about 40 of these that specify the formula instead of setting it up once for all of them.

But, considering I have already spent way too much time on this....Its time well spent.

Thanks everyone for helping me.  I really appreciate it!!!!  Some days are harder than others......

Today was a hard one  :)

Thanks.
Bob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now