Discussions

 View Only
Expand all | Collapse all

Building Nested Formulas

  • 1.  Building Nested Formulas

    Posted 11-07-2022 13:37
    I am trying to assign a category ("On Time", "Past Due", "Exclude", and "Error") based on specific criteria (Installation Stage and dates). After which, I will create a pie chart showing the number of On Time Installations v. Past Due Installations.

    Exclude
    • Return "Exclude" if the Installation stage equals "Refused", "Cancelled", "Remove from Scope", "Pending", and "3rd Party Pending"
    Errors
    • Return "Error - No Target Date" if the Installation Stage equals "Installed" or "Planned" and the Target Date is blank
    On Time v. Past Due
    • If Installation Stage equals Planned
      • Return "On Time" if Today() is less than or equal to than Target Date
      • Return "Past Due" if Today() is greater than (Target Date + 3 days)
    • If Installation Stage equals Installed
      • Return "On Time" if Actual Install Date is less than or equal to Target Date
      • Return "Past Due" if Actual Install date is greater than Target Date + 3 days)


    ------------------------------
    Jennifer Hall
    ------------------------------


  • 2.  RE: Building Nested Formulas

    Posted 11-07-2022 15:09
    This is how I currently have my formulas set up. However, I am receiving the error "Expecting boolean but found text" on the highlighted portion.

    //Exclude from Report
    If([Installation Stage]="Refused", "N/A",
    [Installation Stage]="Remove from Scope", "N/A",
    [Installation Stage]="Cancelled", "N/A",
    [Installation Stage]="Pending", "N/A",
    [Installation Stage]="3rd Party Pending", "N/A",

    //Errors
    If([Installation Stage]="Planned" or [Installation Stage]="Installed" and [Target Installation Date]=null, "Error - No Target Date",

    //On Time v. Past Due
    If([Installation Stage]="Planned" and Today()<=[Target Installation Date], "On Time",
    If([Installation Stage]="Planned" and Today()>([Target Installation Date]+Days(3))=true, "Past Due"),
    If([Installation Stage]="Installed" and [Actual Installation Date]<=[Target Installation Date], "On Time",
    If([Installation Stage]="Installed" and [Actual Installation Date]>([Target Installation Date]+Days(3)), "Past Due")
    )
    )
    )
    )
    )

    ------------------------------
    Jennifer Hall
    ------------------------------



  • 3.  RE: Building Nested Formulas

    Posted 11-07-2022 16:31
    Jennifer,

    Is your Formula Field a Formula Check Box instead of Formula Text?



    ------------------------------
    Don Larson
    ------------------------------



  • 4.  RE: Building Nested Formulas

    Posted 11-07-2022 16:37
    The formula Field is Formula Text.

    ------------------------------
    Jennifer Hall
    ------------------------------



  • 5.  RE: Building Nested Formulas

    Posted 11-07-2022 16:39
    don't think this is valid...
    If([Installation Stage]="Planned" and Today()>([Target InstallationDate]+Days(3))=true"Past Due"),

    remove the "=true"

    ------------------------------
    Keith Jusas
    ------------------------------



  • 6.  RE: Building Nested Formulas

    Posted 11-07-2022 16:41
    Keith found it.

    ------------------------------
    Don Larson
    ------------------------------



  • 7.  RE: Building Nested Formulas

    Posted 11-07-2022 16:45
    I removed the true and I am still receiving the same error message.

    ------------------------------
    Jennifer Hall
    ------------------------------



  • 8.  RE: Building Nested Formulas

    Posted 11-07-2022 18:35
    Edited by Mike Tamoush 11-07-2022 18:38
    I can't see it, but something strikes me wrong with the parentheses. Seems as if too many at the end, meaning one in the wrong spot.

    Can you comment everything out and try just using one simple if statement for your ontime/past due (not nested).  If it works, it's something in the nesting and parentheses.

    For example, in your ontime/past due I don't think those each need an if statement, yes? They do not need to be 4 nested if's. I think if you can clean up the nesting it might help. Maybe make Each condition a variable bool, test each one, the write the nested if statement using the variables.

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 9.  RE: Building Nested Formulas

    Posted 11-07-2022 23:25
    Edited by Don Larson 11-07-2022 23:26
    Keith AND Mike were right but there is more.   I ran it through Visual Studio Code and got this which reduces the parentheses:




    There is a problem in Line 15.  The IF Statement declared on Line 2 is not closed with a final condition.   Something like this is needed:

      ([Installation Stage]="Installed" and [Actual Installation Date]>([Target Installation Date]+Days(3)) ), "Past Due", "Final Status If Nothing is True" )


    ------------------------------
    Don Larson
    ------------------------------



  • 10.  RE: Building Nested Formulas

    Posted 11-08-2022 16:39
    Here is the link to the formula field so you do not have to retype it.

    https://github.com/mcfindustries/Magic/blob/master/Community/NestedExample.quickbase

    ------------------------------
    Don Larson
    ------------------------------