[Roesing]

I am trying to run a formula that will look at an amount funded field if it is greater than 0 look at the outright gift if that is greater then 0 put a 0 in the field with the formula then look at the booked pledge and if that is greater then 0 put a 0 in the formula field if it doesn't meet any of these requirment it will put the amount funded. It does not work. I have pasted one of many formulas that I tryed. I never get an error but it never puts the amount funded in the field.

IF {MG_Turf Field Report.CnPrProp_1_01_Amount_Funded}>0 then
IF {MG_Turf Field Report.CnGf_1_01_Amount}>0 then 0 else
IF{MG_Turf Field Report.CnGf_2_01_Amount}>0 then 0 else {MG_Turf Field Report.CnPrProp_1_01_Amount_Funded}

[DrewAllen]

If the Amount Funded is Null, then your formula will return Null. You need to check whether the amount funded is null first.

Drew

[Roesing]

Some are and some aren't. We only want to show the amount funding if there is no outright gift or booked pledge

[DrewAllen]

This is why you need to check for null values. Crystal stops evaluating as soon as it encounters an unhandled null value and returns null. If the amount funded is null, it doesn't even look at the other amounts, it just aborts evaluation and returns a null.

Drew

[Roesing]

so are you saying that if the outright gift or the booked pledge are null it wont even continue and look back at the amount funded. So I will need to write a formula saying if outright gift is null put 0.00 and the same with book pledge so that there are numbers in the fields?

[DrewAllen]

Pretty much, but it might not even get that far.

Your first expression is Amount Funded > 0. If the amount funded is null, it aborts the expression.

If the amount funded is not null and it's greater than zero, then it will go to the next expression. If the gift1 amount is null, it will again abort the expression and return null.

The only way that your current formula will return the amount funded, is if all three values are not null and the two gift amounts are equal to zero.

(Technically, if the amount funded is null, the formula will also return null and in a sense will be the same as the amount funded, but not because it's returning the value in amount funded which happens to be null. It's returning null, because it cannot evaluate the expression.)

Drew

[Melissa Siobhan]

LOL Drew. You are too funny. To those of us just catching on to crystal it took me aa while to read this response but I finally get it.

Can the report be programmed to have a use a default for null as $0? would that make the expression above work properly?

If not, Roesing, can you maybe explain what you want in more plain english for me because it sounds like you need to completely reformulate this expression (and deal with nulls)

[Roesing]

So how would I write the formula in the amount funded field. I know in acces I can us nz but I don't think that applies to crystal.
Just to let you know I went to the File options put down menu and file report and checked the box convert null field value to default and it still doesn't work.

[Roesing]

Sorry wrong window got it. Once I switched the report options to default it worked. Why doesn't the book make that a bit clearer? Thanks everyone. I am sure I will be back with more questions in the future.

[DrewAllen]

Quote:

Originally Posted by Melissa Siobhan

LOL Drew. You are too funny. To those of us just catching on to crystal it took me aa while to read this response but I finally get it.

Can the report be programmed to have a use a default for null as $0? would that make the expression above work properly?

If not, Roesing, can you maybe explain what you want in more plain english for me because it sounds like you need to completely reformulate this expression (and deal with nulls)

It's so much easier to explain this with pictures (flowcharts).

You can go into the Report Options and set the "Convert Null Values to Default". It sounds like Roesing has done this.

In Crystal XI, you can set this option just for the one formula. I actually prefer keeping the null values in most cases, particularly when dealing with amounts. Sometimes it helps to distinguish between a null value and a $0 value.

In Crystal 8.5, you can use IF...THEN...ELSE and IsNull() and either store the result in a variable or create a sub-formula which you can then use in your main formula.

Drew