Solved

Formula in Crystal - numeric fields along with string fields

Posted on 2013-01-29
14
380 Views
Last Modified: 2013-02-03
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
Comment
Question by:Joe Brown
  • 9
  • 4
14 Comments
 
LVL 100

Expert Comment

by:mlmcc
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

by:Joe Brown
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

by:Joe Brown
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
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 
LVL 100

Expert Comment

by:mlmcc
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

by:Joe Brown
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

by:Joe Brown
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

by:mlmcc
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

by:Joe Brown
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 34

Expert Comment

by:James0628
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

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

Author Comment

by:Joe Brown
ID: 38848919
I had forgotten that I that I had already tried this after adding the "then" that was missing and I had figured out that the parenthesis weremissing.

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

by:Joe Brown
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

by:
mlmcc earned 500 total points
ID: 38849220
Is  {Out_FNData.Out_FNData_IFTResult} a string already?
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

by:Joe Brown
ID: 38849739
Perfection!!!!!!!!!!!!!!!!
Thanks so much mlmcc
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question