Comparing result to limit value or limit override value 1

[dkellam]

In Crystal Reports XI, I am trying to compare sampling results {TESTRAW.RESULT} from table TESTRAW to applicable limit. The database setup is where a automated limit is populated for for each parameter - {LIMITS.LIMIT}, if there is a change the limit is stored in {LIMITS.LIMIT_OVERRIDE}, leaving the {LIMITS.LIMIT} field blank.

Is there a formula to compare {TESTRAW.RESULT} to {LIMITS.LIMIT} - if it exsists and if it is blank automatically compare {TESTRAW.RESULT} to {LIMITS.LIMIT_OVERRIDE}?

Thanks in advance for any input!!

 

[lbass]

You don't say what the comparison is exactly, but try something like this, if you mean to, for example, subtract the result from the limit:

if isnull({Limits.Limit}) then
{Limits.Limit_Override} - {testraw.result} else
{Limits.Limit} - {testraw.result}

-LB

 

[dkellam]

In continuing to review data, I find that there could or could not be a {LIMITS.LIMIT} set in the limits table when there is a {LIMITS.LIMIT_OVERRIDE} stored. Therefore, I guess I am trying to find if the {TESTRAW.RESULT} is greater than the {LIMITS.LIMIT_OVERRIDE} first, and if it is not present (null or zero), then resort to comparing to {LIMITS.LIMIT}.

Thanks!

 

[lbass]

Do you mean the reverse of what you have just said?

-LB

 

[dkellam]

Yes, again because I found that the {LIMITS.LIMIT} can be stored even when there is a {LIMITS.LIMIT_OVERRIDE} stored.

 

[lbass]

I'm still confused. Let me rephrase this--which field takes precedence? Please show some sample data that illustrates the issue and then indicate what should be compared.

-LB

 

[dkellam]

As you see in the right column there is a limit (limits.limit_override), and the left is the automatic populated limit (limits.limit). If there is a value in the left column, I want to compare the test result to the limits.limit_override, if not default comparison to the right column limits.limit.

Hope this helps, thanks for your patience


LIMIT_SET POLL_ LIMIT LIMIT LIMIT_BA LOCAL
_DATES_S CODE _OVER SIS_L_S _LIMITS
RIDE
3,419.00 00720 5.05 2.50 1.00 N
3,419.00 01034 25.05 10.05 1.00 N
3,419.00 01051 10.05 5.05 1.00 N

 

[lbass]

Just reverse the formula.

if isnull({Limits.Limit_Override}) then
{Limits.Limit} - {testraw.result} else
{Limits.Limit_Override} - {testraw.result}

-LB

 

[lbass]

Sorry, you wanted to also test for zero:

if isnull({Limits.Limit_Override}) or
{Limits.Limit_Override} = 0 then
{Limits.Limit} - {testraw.result} else
{Limits.Limit_Override} - {testraw.result

-LB

 

[dkellam]

Thanks so much for your help.
I have the format in working order, however, I would like to conditional format the testraw.result. The condition being if {TESTRAW.RESULT}>{@Limit} then crred else crblack. Where do I put this formula?

 

[lbass]

Right click on the field->border->color->background->x+2 and enter it there--if you want the background to change. If you want the font to change, go to the font tab->color.

-LB

 

[dkellam]

Will CRXI not allow me to compare one field to another? When comparing {TESTRAW.RESULT} to {@Limit}, I get the error message "The formula result must me a number".

 

[lbass]

What is the formula content and where are you putting it?

-LB