Skip to Content

Crystal Reports formula calculation of one field based on another field's value

Hi I am trying to achieve the following:

1. If the temperature = 58, then return "<1" , if temperature = 64 then "<2".

2. The complexity is that the above temperature can be different and is a Table.field value.

3. I tried the following:

I created a Formula testTemp: if ({RESULT.NAME} = "Test Temperature (°C)") THEN left({RESULT.ENTRY},2)

4. Then i used this in another formula (specValue) as follows:

If ({@testTemp} = "58") THEN "1.00 max" else if ({@testTemp} = "64") THEN "2.00 max" else ""

5. This value of this specValue should be returned in the highlighted field on the right in the image (against Jnr3.2).

HOWEVER that doesnt seem to work.

How am i supposed to set this up.

Regards

Geeta

jnr32a.png (16.4 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on 6 days ago

    Both of your formulas look ok with the limited information you've given, but I'm not familiar with your data.

    Since you're using two formulas, here's how I would debug this to determine which formula has a problem:

    1. Take the specVal formula off the report.

    2. Put the testTemp formula in the place where you had specVal.

    3. See what the value of testTemp is.

    I suspect that you're not getting the value there that you're expecting. So, you need to work with that until testTemp is returning the correct values.

    Once testTemp is working right, take it off the report and add specVal back. If it doesn't work right at that point, you need to work with that and the specific values you're getting from testTemp.

    Also, if {RESULT.ENTRY} is a numeric data type instead of a string, you don't have to get the left of the value. Instead, you'll just use your specValue formula and change it to this:

    If ({@testTemp} = 58) THEN "1.00 max" else 
    if ({@testTemp} = 64) THEN "2.00 max" else " "

    If I've misunderstood what you're trying to do, please provide more specific information about how it "doesn't work".

    -Dell

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on 6 days ago

    Hi Dell,

    These are my formula fields in the subreport

    The specValue formula contains the following (have copied the relevant bit):

    if ({RESULT.ANALYSIS} = "TEST1" and {RESULT.NAME} = "Jnr3.2" ) THEN

    SELECT {@prdtGrade}

    case "FB":

    If ({@testTemp} = "58") THEN "1.00 max" - THIS IS NOT RETURNED

    else if ({@testTemp} = "64") THEN "2.00 max" - THIS IS NOT RETURNED

    else "1.5 max" - since i have got this here this is returned since the above 2 conditions are not satisfied. The below should have returned 1.00 max since the Temperature was 58 degrees.


    --

    In the specValue formula above i am trying to get the value of the prdtGrade as a separate formula:

    if ({RESULT.ANALYSIS} = "TEST1" and {SAMPLE.PRODUCT} = "BIT" )

    THEN {SAMPLE.PRODUCT_GRADE}

    ---

    In the specValue formula above i am trying to get the value of the testTemp as a separate formula:

    if ({RESULT.NAME} = "Test Temperature (°C)") THEN left({RESULT.ENTRY},2)

    I am hoping that the prdtGrade and testTemp will return the values which can be used in the Select case in the specValue formula. Hope this makes it a bit clearer.

    --

    I tried adding the testTemp formula on to the report. It returns the 64 against the Test Temperature. All else are blank. Could this be the reason why? I am running round in circles here. Please help.jnr32c.png

    Geeta


    jnr32b.png (7.3 kB)
    jnr32c.png (15.0 kB)
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.