Skip to Content
author's profile photo Former Member
Former Member

Correctly using isnull()

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?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Apr 24, 2007 at 08:19 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 24, 2007 at 08:58 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 24, 2007 at 09:13 PM

    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))

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 24, 2007 at 09:27 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 24, 2007 at 09:40 PM

    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?

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.