Crystal / If Then Else / Accpac / Optional Field

[rpmel]

Hi everyone!

Crystal XI help please?

I have a formula field (LEADTIME) that looks like this,

WhileReadingRecords; 
If {APVENO.OPTFIELD} = "LEADTIME" Then
ToNumber ({APVENO.VALUE}) Else
0

The idea being, have a look at the Vendor Record, for an Optional field called “LEADTIME” then, if it exists, return the value as a number, or, if it doesn’t exist use 0.

I get zero regardless of the value in the leadtime field. It seems to accept the formula, with no errors found, but ignores the IF/THEN, and just goes with ELSE?

Using the Record Selector to find only records with APVENO.OPTFIELD = leadtime won't work, as not all vendors have a leadtime specified - and for those, I want a zero displayed?

Does that make sense?

 

[jdemmi]

I trust that you don't mean that field may or may not exist but that it may or may not have a value of LEADTIME. Correct? (You state "have a look at the record for an optional field called LEADTIME". I think you mean have a look at the record to see if this column contains a value of LEADTIME. Correct?)

If so I suggest placing this optional field in the same section of the report you're placing this formula. The you can see why the evaluation is failing. Perhaps there is a space in the value (LEAD TIME) or perhaps it's not upper case (lead time). Once you see the value(s) you can then correct your formula.

-- Jason
"It's Just Ones and Zeros

 

[Madawc]

You don't need to say WhileReadingRecords;, but the formula should work if the data is what you think it is.

Add a display for {APVENO.OPTFIELD} and also for {APVENO.VALUE} - maybe the value is zero.

It's usually also good to test for nulls, IsNull, but that would not explain your problem.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I would check the case. If the case in the field can vary, then use:

if ucase({APVENO.OPTFIELD}) = "LEADTIME" Then
ToNumber ({APVENO.VALUE})

-LB

 

[rpmel]

I trust that you don't mean that field may or may not exist but that it may or may not have a value of LEADTIME.

Well, the field is an optional field, added to each supplier record manually - it doesn't actually exist by default until you add it in as an optional field.

Hmm, might actually need to refer this to our external Accpac dude for some help I think?

I would check the case. If the case in the field can vary, then use:

The value is set as Uppercase and selected by a drop down box? Oh well, I'll go away and see what else I find.

 

[lbass]

Change the formula to:

If isnull({APVENO.OPTFIELD}) or
ucase({APVENO.OPTFIELD}) <> "LEADTIME" Then
0 else
ToNumber ({APVENO.VALUE})

From what you say, the ucase() might be irrelevant, but it doesn't hurt.

-LB