Simple Formula 2

[Juice05]

I must be losing my mind because this is a very simple procedure, but it is kicking my butt. I have a report that gets it's data from a Stored Procedure. One of the fields within the SP is:

FirstName + ' ' + LastName

This field is called Employee. So I am trying to format the field so that if it is blank then it should display "No Employee" but for some reason I am struggling with this.

I right-click on the field (Within the report) and go to "Format Field". Then I choose "X-2" beside the "Display String". My formula looks like this:

If Trim({Reason;1.Employee}) = "" Then
    "No Employee"
Else
    {Reason;1.Employee}

This doesn't work, the blank records are still returned. Shouldn't they read "No Employee"? Also I tried IsNull and get the same results.

 

[Turkbear]

Hi,
Instead of attempting to use the x-2 option, create the formula as a separate object..Place the formula in the report instead of the actual field..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Madawc]

If you get back blanks from a formula with If ... Else, then you are reading a null.

Having come to Crystal from mainframe languages, I got a 'cultural shock' when encountering null. It means 'no data': Mainframe languages mostly treat this as the same as zero.

It is actually a finer shade of meaning, the difference between 'Yes, we have no bananas' and 'I don't know how many bananas we have, it could be some, it could be zero'. In Crystal, the entry is 0 or null and can be tested for.

Note that Crystal assumes that anything with a null means that the field should not display.

Try

if isnull({Reason;1.Employee})
or Trim({Reason;1.Employee}) = "" Then
    "No Employee"
Else
    {Reason;1.Employee}

Or you could split it to identify the nulls.

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

 

[Juice05]

Madawc - I tried the IsNull function with no success. The field still returns blank.

Turkbear - I tried adding a seperate formula, drug in on the form and it results the same results.

Maybe I should mention that the field I am working with is within a group. I have a group of clients and there are many employees for each client.

 

[Juice05]

I spoke too soon.

Turkbear - The seperate formula worked. I was checking for "" not Null.

Madawc - You were right on the null issue.

I still don't understand why it has to be a seperate formula.

Thanks for the help.

 

[synapsevampire]

Your initial formula would be fine if you select File->Report Options->Convert null value to default.

-k

 

[Madawc]

Formula fields can be made very complex, if you like. But since one formula field can call another, I prefer to split them.

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