Forum Discussion

AnnettaColeman's avatar
Qrew Cadet
3 years ago

How to write an IF Statement to look for NOT null

In our app we converted 400 projects that had historical SPONSORS as a text field. 
  • Approximately half of the projects are closed so we plan to keep a Sponsor-Historical (text field) for closed projects.
  • Approximately half of the projects are active so for these we will invite the Sponsors to become QuickBase users.   As they become users we will assign the project to a Sponsor-User (user) field.   Some Sponsors choose to not become users the project needs to retain the Sponsor-Historical (text) field.
  • In our project header we want to display the Sponsor Name cleanly with a formula text field that chooses the UserToName version of the Sponsor-User field if it exists, otherwise  choose the Sponsor-Historical field if it exists, otherwise display a null value for the displayed version.   
Note: We were struggling with the If Statement so we chose to create a separate formula text called SPONSOR-UserToName field to confirm that the UserToName conversion was working properly, which it is.

PROBLEM: We continue to struggle with the conditional statement to do the following:
  • If Sponsor UsertoName field exists, use it in the display value field otherwise
  • If Sponsor Historical field exists, use it in the display value field otherwise
  • Display Value field is null 
What we have so far works only if the first line is true, always ignores the 2nd line and if the first line is not true produces a null value.




Of interest if we swap the 1st and 2nd lines, which ever line is 1st works if not null, but 2nd line never fires.   How can we resolve this If statement?  

Thanks in advance for your help.

Annetta Coleman

3 Replies

  • Well Duh....   It works just fine if I use <> Null instead of IsNull = False.    Amazing what clarity can be discovered via a Power Nap.  Sorry to waste community time.

    Annetta Coleman
    • MarkShnier__You's avatar
      Icon for Qrew Legend rankQrew Legend
      I just a tip. You can't use IsNull to test for a Text field type being blank. I use this

      IF(Trim([My Text field]) = "", ............)

      Mark Shnier (YQC)
    • MikeTamoush's avatar
      Qrew Commander
      To add on to Mark's tip:

      If you ARE testing a numerical field simply use:

      isnull([Field]) to test if blank
      not isnull([Field]) to test if not blank (versus isnull <>null....)

      Mike Tamoush