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

[gh_cr05]

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

[gh_cr05]

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

[DellSC]

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

[DellSC]

Are you trying to have the testTemp value show for all of the details? If that's the case, you'll need to use a variable. It would look like this:

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

I think this will resolve the issue for you.

-Dell

[gh_cr05]

Hi Dell

I updated the specValue Formula as follows: (pasting the relevant part here):

StringVar testTemp := "";

if ({RESULT.ANALYSIS} = "TEST1" and {RESULT.NAME} = "Test Temperature") THEN

testTemp := left({RESULT.ENTRY},2);

StringVar prdtGrade := ""; if ({RESULT.ANALYSIS} = "TEST1") THEN

prdtGrade := {SAMPLE.PRODUCT_GRADE};

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

SELECT prdtGrade

case "Flexi Bind":

If (testTemp = "58") THEN "1.00 max" else

if (testTemp = "64") THEN "2.00 max" else "1.5 max "

--------

Then to check if the variables: testTemp and prdtGrade return any values - i basically commented out the remaining parts of the formula one by one. These are the observations;

When i comment out everything except the formula for the variable testTemp

When i comment out everything except the formula for prdtGrade

I guess the above behaviour is because the prdtGrade is at the Sample level while the testTemp is at the Result level

Is there anything i can do for the specValue formula to check the testTemp value in the if then condition and return the correct value:

If (testTemp = "58") THEN "1.00 max" else

if (testTemp = "64") THEN "2.00 max" else "1.5 max "

Appreciate your help so far.

Geeta

[DellSC]

Sorry about that - I know what I did wrong in the formula I gave you. You can't set the variable to an empty string every time it runs. Instead, you have to initialize it on the first record in the report and then, if you're grouping data on the report, any time the group changes. So, it would now look like this:

StringVar value;
if OnFirstRecord or {RESULT.Group field} <> Previous({RESULT.Group field}) then
  value := "";
if({RESULT.NAME}="Test Temperature (°C)") THEN 
  value:=left({RESULT.ENTRY},2);
value

-Dell

[gh_cr05]

Hi Dell

That worked brilliantly. Thank you so much for your guidance.

Regards

Geeta