Discussions

 View Only
  • 1.  Nested IF formula

    Posted 06-04-2018 18:11
    Hi everyone,

    I would like to automate a Status column based on 9 other columns. Most of these other columns are dates but there are two checkbox columns (Reassigned to Drops and New Build).

    This is what I have so far... I'm not getting an error, but I can tell from the results, only the lines underlined (first two) are actually reading and returning values.

    If([Permits]="Yes","Permit Pending",
    If([Reassigned to Drops],"1",[Reassigned to Drops],
    If([New Build],"1",[New Build],
    If(not IsNull([Date MDU Splicing Completed]),"Splicing Complete",
    If([Access Issues]="Not Resolvable","ROE Impasse",
    If(not IsNull([Date MDU Temp]),"Temp",
    If(not IsNull([Date MDU Ready to Splice]),"Ready to Splice",
    If(not IsNull([Date MDU In Construction]),"In Construction",
    If(not IsNull([Walkout/Survey Date]),"Walkout/Survey","Other")))))))))

    Suggestions? 

    Thank you


  • 2.  RE: Nested IF formula

    Posted 06-04-2018 19:22
    I have two main comments

    The first is that you do not need to nest in QuickBase.  It has esier syntax than Excel

    Just list your conditions vertically (for readability) and Quick Base will evaluate them in sequence and take the result of the first one that is true.

    The next issue is that you  this is a text formula, so you need to rethink where you have a result which is a checkbox boolean true result.  That does not make sense as a text result and will give you a syntax error.

    If(
    [Permits]="Yes","Permit Pending",
    [Reassigned to Drops] = true, "say something here",
    [New Build] = true, "say something here",
    not IsNull([Date MDU Splicing Completed]),"Splicing Complete",
    [Access Issues]="Not Resolvable","ROE Impasse",
    not IsNull([Date MDU Temp]),"Temp",
    not IsNull([Date MDU Ready to Splice]),"Ready to Splice",
    not IsNull([Date MDU In Construction]),"In Construction",
    not IsNull([Walkout/Survey Date]),"Walkout/Survey","Other")

    Be sure to consider the sequence as the first true result will be the result.


  • 3.  RE: Nested IF formula

    Posted 06-06-2018 15:10
    Excellent, that's easy to follow. Thank you, this worked!