Correctly using isnull()

[Former Member]

Post Author: WAFFLE

CA Forum: Formula

I want to create a formula that checks to see if something is null and if it is, I want it to check another piece of information. If it does have information I want it to return the information.So what I was thinking was IF NOT isnull(field1) then field1 else IF NOT isnull(field2) then field2 else IF NOT isnull(field3) then field3 else " ";Is this thought process off or is this the best way to go about it?

[Former Member]

Post Author: WAFFLE

CA Forum: Formula

Ok, so I see that the logic is correct, however the formula is not. What would be the correct formula in that case if I need to check for null but I want isnull(field1) to return false or I have to run another isnull(field2) that should return false or I have to run a third isnull(field3) that if it returns true I can just create a " " and if it returns false I can just return field3?

[Former Member]

Post Author: Charliy

CA Forum: Formula

VERY IMPORTANT UNDOCUMENTED RULE

The test for Null must be the first test you do in a formula:

IF ISNULL({table.enddate}) OR {table.enddate} > ?{End Date} will work

IF {table.enddate} > ?{End Date OR ISNULL({table.enddate}) will not work - it won't give you an error, it just won't give you the right results

[Former Member]

Post Author: yangster

CA Forum: Formula

the confusion you are having is you are confusing the different languagesin sql you would write field1 is not nullin crystal you'd have to write itnot(isnull(field1))

[Former Member]

Post Author: WAFFLE

CA Forum: Formula

So I tried the IF not isnull(field1) then field1 else if isnull(field1) then "dance"; However, when I tried that, I still could only pull information once... the empty report from the Null value eventhough I turned on the null field. The reason I was trying it this way is ideally the field1 would hold information but if it doesn't I'd need to run another If to check the second field so basically it would look like...If not isnull(field1) then field1 else if isnull(field1) then if not isnull(field2) then field2 else if isnull(field2) then if not isnull (field3) then field3 else if isnull (field3) then " N/A"; and I think that is too much and there is an easier way to do it.

[Former Member]

Post Author: SKodidine

CA Forum: Formula

I always suggest that you get things working before you modify it to make it more efficient. Your logic is fine, so try it.