1

I have a Database field which I'm trying to use in Crystal Reports formula. This record in the database stores both numeric and text values.

Sample values: 75, 85, 55, None, Pending

I'm trying to use this in a Crystal Report formula and the report should display a particular value based on this field.

Example: 

{Database Field Value}>=85 THEN "Exceeds Expectations"
{Database Field Value}>=50 AND <=84 THEN "Met Expectations"
{Database Field Value}<49 THEN "Needs Improvement"
{Database Field Value}="none" THEN "Survey Not Taken"
{Database Field Value}="Pending" THEN "Survey Not Completed"

I'm trying to use this formula, but its not showing in the report correctly. 

IF {Database Field Value} >= TOTEXT(85) THEN "Exceeds Expectations"
ELSE IF {Database Field Value} >= TOTEXT(50) AND {Database Field Value} <= TOTEXT(84) THEN "Met Expectations"
ELSE IF {Database Field Value} < TOTEXT(49) THEN "Needs Improvement"
ELSE IF {Database Field Value}="None" THEN "Survey Not Taken"
ELSE IF {Database Field Value}="Pending" THEN "Survey Not Completed"
ELSE ""

Can someone please help?

MatSnow
  • 7,357
  • 3
  • 19
  • 31
Karu3103
  • 81
  • 8
  • Does the database Field contain Text Value? if Yes then pls convert the field ToNumber. Why you are converting the Number into ToText()? Text cannot be compared using>= or<=, Hope you understand.. – gm arif Aug 07 '20 at 23:38

1 Answers1

1

There are multiple issues in your formula

  1. You can't compare text directly with numbers
  2. You can't write {Database Field Value}>=50 AND <=84, you always have to specify the value to compare: {Database Field Value}>=50 AND {Database Field Value} <=84
  3. If a text is converted with ToText() it is still a text and isn't treated as a number

So you have to check if the field contains a number first by using the NumericText function.
If it contains a number, then convert it with ToNumber and then compare it to the corresponding number.

The following formula should give you the desired result:

If NumericText({Database Field Value}) Then
    If ToNumber({Database Field Value})>=85 Then
        "Exceeds Expectations"
    Else If ToNumber({Database Field Value})>=50 And ToNumber({Database Field Value})<=84 Then
        "Met Expectations"
    Else If ToNumber({Database Field Value})<49 Then
        "Needs Improvement"
Else If {Database Field Value}="none" Then
    "Survey Not Taken"
Else If{Database Field Value}="Pending" Then
    "Survey Not Completed"

P.S.: The number 49 will not meet any of the conditions...maybe this is intended.

MatSnow
  • 7,357
  • 3
  • 19
  • 31
  • Hi @MatSnow, thanks for the above. The formula seems to be working fine for the numeric values, but when checking for "none" or "Pending" it is displaying nothing in the report. Any reason what might be happening? – Karu3103 May 13 '20 at 13:48
  • @Karu3103 Maybe one or more spaces before or after "none" / "Pending". Something like " none" or "Pending "? – MatSnow May 13 '20 at 13:58