# Trouble creating a text formula based on null fields

• 0
• Question
• Updated 12 months ago
I have 3 fields, for simplicity we will call them [A], [B] and [C].  They are all proxy fields, and part of a conditional drop down chain, so you need to fill in [A] to fill in [B] to fill in [C]. I would like to have a text formula to show the field that is the farthest part in the chain. So if [A] and [B] are filled in it shows [B], and if they are all filled in it would show [C], that kind of thing. The goal is in view mode to not see the 3 drop down menus and instead see this formula field. Currently I am using the following with no luck, what am I doing wrong?

If (
IsNull([C]) and IsNull([B]) and not IsNull([A]),[A],
IsNull([C]) and not IsNull([B]) and not IsNull([A]),[B],
not IsNull([C]) and not IsNull([B]) and not IsNull([A]),[C],"")
• 1,644 Points

Posted 2 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 65,402 Points
The Isnull is documented not to work on text fields

Try this test instead for a blank text field to test against empty quotes.

Trim([my text field])=""
• 1,644 Points
This was an excellent solution, thank you!
• 1,644 Points
Just to piggy back off this a bet as I would like to that this one step farther. I have this formula now that works excellently:

If(
Trim([Type3 - Third Type])=""  and Trim([Type2 - Second Type])="" and Trim([Type1 - First Type])<>"",[Type1 - First Type],
Trim([Type3 - Third Type])="" and Trim([Type2 - Second Type])<>"" and Trim([Type1 - First Type])<>"",[Type2 - Second Type],
Trim([Type3 - Third Type])<>"" and Trim([Type2 - Second Type])<>"" and Trim([Type1 - First Type])<>"",[Type3 - Third Type],"")
Though I only want it go through this formula if another field is blank and if it is not blank display its value. So I have been trying a nested if statement and it does not seem to be working, here is  what I am going with:
If (Trim([Type of Claim or Project])="",If(

Trim([Type3 - Third Type])=""  and Trim([Type2 - Second Type])="" and Trim([Type1 - First Type])<>"",[Type1 - First Type],
Trim([Type3 - Third Type])="" and Trim([Type2 - Second Type])<>"" and Trim([Type1 - First Type])<>"",[Type2 - Second Type],
Trim([Type3 - Third Type])<>"" and Trim([Type2 - Second Type])<>"" and Trim([Type1 - First Type])<>"",[Type3 - Third Type],""),[Type of Claim or Project])
Is it a structuring issue or is there a better solution?

QuickBaseCoach App Dev./Training, Champion

• 65,402 Points
I hate nested IFs.  Too hard to debug especially if you have to revisit is 2 months to 2 weeks later.

Conversely I LOVE formula variables. http://help.quickbase.com/user-assistance/formula_variables.html

I would do this.

var text TestTypes =
If(
Trim([Type3 - Third Type])=""  and Trim([Type2 - Second Type])="" and Trim([Type1 - First Type])<>"",[Type1 - First Type],
Trim([Type3 - Third Type])="" and Trim([Type2 - Second Type])<>"" and Trim([Type1 - First Type])<>"",[Type2 - Second Type],
Trim([Type3 - Third Type])<>"" and Trim([Type2 - Second Type])<>"" and Trim([Type1 - First Type])<>"",[Type3 - Third Type]);

IF(Trim([Type of Claim or Project])<>"", \$TestTypes)
• 248 Points
Thank you!
• 1,644 Points
This is a much more elegant approach, I did some tweaking as I would like it so that when the Type of Claim or Project field is blank then it goes through the type1, type2, type3, statement. If there is something in the Type of Claim or Project field then I would like it to be displayed. What I am working with now is:

var text TestTypes =
If(
Trim([Type3 - Third Type])=""  and Trim([Type2 - Second Type])="" and Trim([Type1 - First Type])<>"",[Type1 - First Type],
Trim([Type3 - Third Type])="" and Trim([Type2 - Second Type])<>"" and Trim([Type1 - First Type])<>"",[Type2 - Second Type],
Trim([Type3 - Third Type])<>"" and Trim([Type2 - Second Type])<>"" and Trim([Type1 - First Type])<>"",[Type3 - Third Type]);
If(Trim([Type of Claim or Project])="", \$TestTypes,[Type of Claim or Project])
If something is selected from the drop down in the Type of Claim or Project field it is filling in, though if I leave it blank and try to select a Type 1 the calculation field does not resolve and just reads ???

QuickBaseCoach App Dev./Training, Champion

• 65,402 Points
Does it resolve when you save?
• 1,644 Points
As it turns out, since the worker or project name is a required field is that was breaking it. When I may that field unrequited it then worked. Which is fine since the whole point of this is to replace that field. Thank you for the help!

QuickBaseCoach App Dev./Training, Champion

• 65,402 Points
Thx, I never knew that behavior with requiredness.  I learned something!