Trouble creating a text formula based on null fields

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Answered
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],"")
Photo of Jordan

Jordan

  • 1,574 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
The Isnull is documented not to work on text fields 

https://login.quickbase.com/db/6ewwzuuj?a=dr&r=n&rl=j6


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

Trim([my text field])=""
Photo of Jordan

Jordan

  • 1,574 Points 1k badge 2x thumb
This was an excellent solution, thank you!
Photo of Jordan

Jordan

  • 1,574 Points 1k badge 2x thumb
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
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)
Photo of Katherine Phung

Katherine Phung

  • 248 Points 100 badge 2x thumb
Thank you!
Photo of Jordan

Jordan

  • 1,574 Points 1k badge 2x thumb
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 ???
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
Does it resolve when you save?
Photo of Jordan

Jordan

  • 1,574 Points 1k badge 2x thumb
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!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
Thx, I never knew that behavior with requiredness.  I learned something!