Discussions

Expand all | Collapse all

Trouble creating a text formula based on null fields

  • 1.  Trouble creating a text formula based on null fields

    Posted 08-23-2017 14:14
    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],"")


  • 2.  RE: Trouble creating a text formula based on null fields

    Posted 08-23-2017 14:35
    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])=


  • 3.  RE: Trouble creating a text formula based on null fields

    Posted 08-23-2017 16:03
    This was an excellent solution, thank you!


  • 4.  RE: Trouble creating a text formula based on null fields

    Posted 08-23-2017 16:51
    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?


  • 5.  RE: Trouble creating a text formula based on null fields

    Posted 08-23-2017 17:04
    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)


  • 6.  RE: Trouble creating a text formula based on null fields

    Posted 05-03-2018 23:03
    Thank you!


  • 7.  RE: Trouble creating a text formula based on null fields

    Posted 08-23-2017 17:50
    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 ???


  • 8.  RE: Trouble creating a text formula based on null fields

    Posted 08-23-2017 18:07
    Does it resolve when you save?


  • 9.  RE: Trouble creating a text formula based on null fields

    Posted 08-23-2017 19:47
    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!


  • 10.  RE: Trouble creating a text formula based on null fields

    Posted 08-23-2017 19:48
    Thx, I never knew that behavior with requiredness.  I learned something!