x
Solved

# Formula in Crystal - numeric fields along with string fields

Posted on 2013-01-29
Medium Priority
396 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
• 9
• 4

LVL 101

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 101

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 101

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 101

Accepted Solution

mlmcc earned 2000 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.

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.