How to display text instead of a numberic field? 2

[NewToThis2]

CRV10 - I have a {HRHISTORY.FLD_NBR} field this is numeric. In my report I do not want the numeric to print but instead I want to replace it with text. When I try to use this formula, I get the message "A Number is required here" and the "14" is hi-lighted. I would appreciate some guidance.

if {HRHISTORY.FLD_NBR}="14" then "Process Level"
else if {HRHISTORY.FLD_NBR}="15" then "Department"
else if {HRHISTORY.FLD_NBR}="18" then "Supervisor"
else if {HRHISTORY.FLD_NBR}="19" then "Job Code"

Thanks!

 

[beanbrain]

You don't need quotation marks around numbers when comparing those numbers to a numeric field. Try this instead.

if {HRHISTORY.FLD_NBR}= 14 then "Process Level"
  else if {HRHISTORY.FLD_NBR}= 15 then "Department"
  else if {HRHISTORY.FLD_NBR}= 18 then "Supervisor"
  else if {HRHISTORY.FLD_NBR}= 19 then "Job Code"

Note that the "14" etc is now 14 without quotation marks.

 

[NewToThis2]

Excellent - worked like a charm. Thanks so much for your expert guidance! I have one more question. What if I need to put a hundred of these if statements in this formula, is there any way to shorten the formula or use "OR" instead of an if statement for each one? Thanks again!

 

[kskid]

Ideally, you would have a code table that you could join tol make the translation but sort of that

select {HRHISTORY.FLD_NBR}
  Case 14:
    "Process Level"
  Case 15:
    "Department"
  Case 18: 
    "Supervisor"
  Case 19:
    "Job Code"

.... and so forth

  Default:
    "Other"

-LW

 

[NewToThis2]

That worked. Thanks so much for your expertise!

 

[kskid]

With that many codes, it seems strange that the DBA didn't have a code table for FLD_NBR

FIELD_CODE

Code Code_Description
14 Process Level
15 Department
18 Supervisor
19 Job Code

Then all you have to do is Click on Database->Visual Linking. On a clear space on the visual linking screen, right click->Add Table and add the FIELD_CODE table and then link {HRHISTORY.FLD_NBR} to {FIELD_CODE.CODE}. In your report you just have to substitute {FIELD_CODE.CODE_DESCRIPTION} for {HRHISTORY.FLD_NBR} and bingo, instant conversion.

Anyway, glad to help

-LW

 

[NewToThis2]

Thanks for your suggestion. The application was not built in house. The table is a history table where each numeric value means something different. I'm very interested in your suggestion but I could not find "Visual Linking" in Crystal V10. When I click on Database, that is not a choice.

 

[beanbrain]

In Crystal 10 you'll find "visual linking" under;

"Database" > "Database Expert." When the database dialog box appears look for a tab called "Links." In the "Links" tab you'll find the visual linking expert.

When you locate a join between tables right click on the join for options.