Solved

# Formula in Crystal - numeric fields along with string fields

Posted on 2013-01-29
382 Views
I need to take a string field and change it to a numeric field so I can round the decimal points in the Crystal report.

I have a formula already made for this field that includes some logic for this field. It appears that it gets cranky when I want to make some "if's" when it is a string and then change it to a numeric field within that same formula. See below. Is this not possible? What do I need to do to change the field to a numeric value in order to round the decimal point when this can also be a string field.

It is requiring that the "else" portion also be numeric even if I state, totextt in the formula.

if isnull ({Out_FNData.Out_FNData_IFTResult}) then {Out_FNData.Out_FNData_VendorResult}
else if {Out_FNData.Out_FNData_IFTResult} = "True" then "Pass"
else if {FN_ProductSpecs_Tests.FN_PS_TestID} in [174, 175] then tonumber ({Out_FNData.Out_FNData_IFTResult})
else {Out_FNData.Out_FNData_IFTResult}
0
Question by:Joe Brown
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 9
• 4

LVL 100

Expert Comment

ID: 38833790
A formula can only return 1 data type.

You are trying to return a number or a string (PASS)

You can use CStr or ToText to change the number to a string

CStr({NumericField},NumberofDecimals,"Thousands Separator")

Round to 3 decimals with no thousands separator

CStr({YourField},3,"")

or

CStr({NumericField},"FormatString")

CStr({YourField},"#####0.000")

mlmcc
0

Author Comment

ID: 38845818
I need to explain my problem a little bit better and I am sure you can help me with a formula that will work.

I have a field named IFTResult and it is a string field in SQL.
Sometimes this field has words but most of the time it is a numeric text.
Then I have another field named TestID and it is an integer field.

I have a crystal report where I have to report the field with the words when the Test ID is equal to 179, 116 or 195. I have to report the field as a number with one deciamal place when the Test ID is equal to 105, 106, 189, 190, 186, 200, 177, 161 and I have to report the field as an integer the rest of the time.

I have tried many differen combonations, even one where I make a formula field for each situation. It likes all my formulas but as soon as I put a formula on there that has a "not" value on to the report I get an error.
If I try this at any point:
if {FN_ProductSpecs_Tests.FN_PS_TestID} <> [179, 116, 195] then tonumber ({Out_FNData.Out_FNData_IFTResult})
It states that the string is non numeric.

It would be nice to have it report the 179, 116, 195 as a string - the 105, 106, 189, 190, 186, 200, 177, 161 as a number with one decimal and the rest as integers but I can't figure out how to do this with the test id being numeric and the iftresult being a string and I am just pissing it off :)
0

Author Comment

ID: 38845834
And fields that I need to result as an integer have to round the value.
That is why my goal was to turn all tests to a numeric field when they were not in the short list of tests. And turn them in to a number when they were in the longer list, then allow that field to be shown as is (0.3) then I was trying to make a third formula field that would take all other tests not noted above and turn them in to a numeric field also - then take that field and format it to round. I was going to layer them over each other and then supress when each was not valid.

I'm sure that is not best practice, but I am desperate to find a solution to this
0

LVL 100

Expert Comment

ID: 38845929
>>If I try this at any point:
if {FN_ProductSpecs_Tests.FN_PS_TestID} <> [179, 116, 195] then tonumber ({Out_FNData.Out_FNData_IFTResult})

That means on of the other values has non-numeric text in it.

if {FN_ProductSpecs_Tests.FN_PS_TestID} IN [179, 116, 195] THEN
{Out_FNData.Out_FNData_IFTResult}
Else If  {FN_ProductSpecs_Tests.FN_PS_TestID} IN [105, 106, 189, 190, 186, 200, 177, 161]
(
If IsNumeric({Out_FNData.Out_FNData_IFTResult} then
CStr({Out_FNData.Out_FNData_IFTResult},1)
Else
{Out_FNData.Out_FNData_IFTResult}
)
Else
(
If IsNumeric({Out_FNData.Out_FNData_IFTResult} then
CStr({Out_FNData.Out_FNData_IFTResult},0)
Else
{Out_FNData.Out_FNData_IFTResult}
)

mlmcc
0

Author Comment

ID: 38845961
I tried this and I get an error that the keyword then is missing
right at the parenthesis before
If IsNumeric({Out_FNData.Out_FNData_IFTResult} then
0

Author Comment

ID: 38845971
Also, will this part of the formula round the number or just omit the numbers to the right of the decimal point?

CStr({Out_FNData.Out_FNData_IFTResult},0)
0

LVL 100

Expert Comment

ID: 38845995
If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then

It will round and not just truncate

You could also use

CStr(Round({Out_FNData.Out_FNData_IFTResult},0) ,0)

mlmcc
0

Author Comment

ID: 38846140
I tried this and I get an error that the keyword then is missing
right at the parenthesis before
If IsNumeric({Out_FNData.Out_FNData_IFTResult} then
0

LVL 35

Expert Comment

ID: 38846551
mlmcc left the "Then" off of the third line.  It should be:

Else If  {FN_ProductSpecs_Tests.FN_PS_TestID} IN [105, 106, 189, 190, 186, 200, 177, 161] then

And, as already mentioned, the two lines with IsNumeric are missing a ")".  They should both be:

If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then

James
0

Author Comment

ID: 38848893
Thanks James - I will try that now.
0

Author Comment

ID: 38848919

I added these and get an error on the 5th line that "There are too many arguments have been given to this function."

It is highlighting this error on the number 1 in the following line:
CStr({Out_FNData.Out_FNData_IFTResult},1)
0

Author Comment

ID: 38848922
Here is the complete formula I currently have.

if {FN_ProductSpecs_Tests.FN_PS_TestID} IN [179, 116, 195] THEN
{Out_FNData.Out_FNData_IFTResult}
Else If  {FN_ProductSpecs_Tests.FN_PS_TestID} IN [105, 106, 189, 190, 186, 200, 177, 161]
then (
If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then
CStr({Out_FNData.Out_FNData_IFTResult},1)
Else
{Out_FNData.Out_FNData_IFTResult}
)
Else
(
If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then
CStr({Out_FNData.Out_FNData_IFTResult},0)
Else
{Out_FNData.Out_FNData_IFTResult}
)
0

LVL 100

Accepted Solution

mlmcc earned 500 total points
ID: 38849220
If so you have to convert it to a number then you can format it with CStr

if {FN_ProductSpecs_Tests.FN_PS_TestID} IN [179, 116, 195] THEN
{Out_FNData.Out_FNData_IFTResult}
Else If  {FN_ProductSpecs_Tests.FN_PS_TestID} IN [105, 106, 189, 190, 186, 200, 177, 161]
then (
If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then
CStr(Val({Out_FNData.Out_FNData_IFTResult}),1)
Else
{Out_FNData.Out_FNData_IFTResult}
)
Else
(
If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then
CStr(Val({Out_FNData.Out_FNData_IFTResult}),0)
Else
{Out_FNData.Out_FNData_IFTResult}
)

mlmcc
0

Author Comment

ID: 38849739
Perfection!!!!!!!!!!!!!!!!
Thanks so much mlmcc
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirementsâ€¦
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 biâ€¦
Introduction to Processes
Why Skyport?
###### Suggested Courses
Course of the Month4 days, 22 hours left to enroll