?
Solved

Formula in Crystal - numeric fields along with string fields

Posted on 2013-01-29
14
Medium Priority
?
387 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
[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
  • Learn & ask questions
  • 9
  • 4
14 Comments
 
LVL 101

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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 101

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 101

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 35

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 101

Accepted Solution

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

718 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