Discussions

Expand all | Collapse all

Nested IF Statement to Populate Field

  • 1.  Nested IF Statement to Populate Field

    Posted 03-02-2020 10:47

    I'm trying to determine if a proposal is late.  Below is the formula:

    If([Proposal Received 2B Alight]<=[PWO Proposal Due Date],"Yes","No")

    If(ToDays(Today())<[PWO Proposal Due Date])and If(IsNull[[Proposal Received 2B Alight]) ,"Yes","No")

     

    The first "If" statement works without any issues, but I get the following syntax error on the second "If" statement:

    Error:

    Column: 0 > There are extra characters beyond the end of the formula.

    I'm a new QuickBase user, and any help would be appreciated.



    ------------------------------
    Terri Wells
    ------------------------------


  • 2.  RE: Nested IF Statement to Populate Field

    Posted 03-02-2020 10:55
    np to help you, but we need to know what logic you want.  To quote the famous Kirk Trachy, "if you can say it we can do it."


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Nested IF Statement to Populate Field

    Posted 03-02-2020 11:12
    I'm trying to test for the following:
    1. If Date "A" <= Due Date
      1. The proposal is on time, enter "Yes" otherwise enter "No"
    2. I'm checking for today's date and comparing it to the Due Date
      1. If the Due Date is < then today, enter "Yes" otherwise enter "No"
      2. If the due date is > today, and the Proposal Received date IsNull, enter "Yes" otherwise enter "No"


    ------------------------------
    Terri Wells
    ------------------------------



  • 4.  RE: Nested IF Statement to Populate Field

    Posted 03-02-2020 11:27

    Terri
    Sorry but your logic is still not clear. There is only one field that we are populating and we can't put both a No and a Yes. 


    Is what you were really saying is that you want to do the first test and if true, you want Yes, else go onto the second test?



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Nested IF Statement to Populate Field

    Posted 03-02-2020 11:45
    What I'm trying to say (but doing it poorly) is:
    • if the first condition is meet, then enter "Yes" in the field,
      • If not look at today's date,
        • If today's date < the Due Date or if the Due Date IsNull enter "No"

    I hope this makes more since.

    ------------------------------
    Terri Wells
    ------------------------------



  • 6.  RE: Nested IF Statement to Populate Field

    Posted 03-02-2020 11:50
    Ok. Just boarded a plane. If there is Wifi I will post back soon.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Nested IF Statement to Populate Field

    Posted 03-02-2020 12:25
    Edited by Mark Shnier (YQC) 03-02-2020 12:26
    Yay for airplane Wifi,
    Try this

    If(
    [Proposal Received 2B Alight]<=[PWO Proposal Due Date],"Yes",

    Today()<[PWO Proposal Due Date] and IsNull([Proposal Received 2B Alight]) ,"Yes","No")


    // I'm not sure what order you want the tests as Quick Base will keep processing until it hits the first true result.  So you might need to flip the test sequence.

    Note that with Quick Base IF formulas it is rare that you need to nest your IFs.  YOU typically, open with the IF and then stack the tests vertically for readability.


    EDITED Above to remove with an extra bracket
    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Nested IF Statement to Populate Field

    Posted 03-02-2020 13:06
    That worked.  Thank you!

    ------------------------------
    Terri Wells
    ------------------------------



  • 9.  RE: Nested IF Statement to Populate Field

    Posted 03-05-2020 09:09
    The formula above works great when the data is entered initially.  The issue I'm having now, if the date fields change the "Yes" / "No" field keeps the original value.  How do I get this field to update every time one of the dates change?

    As always, thanks for your help.

    ------------------------------
    Terri Wells
    ------------------------------



  • 10.  RE: Nested IF Statement to Populate Field

    Posted 03-05-2020 11:53
    As Mark said you may need to flip the formula around because if the first line "[Proposal Received 2B Alight]<=[PWO Proposal Due Date]" is true it will always be Yes in the box even if the second condition changes. 

    Which dates are changing and what are you expecting to happen when it changes?



  • 11.  RE: Nested IF Statement to Populate Field

    Posted 03-05-2020 12:47

    Below is what the formula currently looks like:

     

    If([Proposal Received 2B Alight]<=[PWO Proposal Due Date],"Yes",

    Today()<[PWO Proposal Due Date]and IsNull([Proposal Received 2B Alight]),"Yes","No")

     

    I'm seeing two problems:

    1. When the "PWO Proposal Due Date" is originally filled in, the "Proposal Received 2B Alight" field will always be null
      1. So, when that field eventually gets completed, I need to determine if the CR was Late or On Time.
    2. The other issue I have is human error, if they enter a date incorrectly that field will never get updated.

     

    If I flip the formula, do you think that might fix the issue?

     

    Any suggestions would be helpful.

     

    Thanks,

    Terri Wells






  • 12.  RE: Nested IF Statement to Populate Field

    Posted 03-05-2020 12:23
    Terri,

    There is an old cliche that the computer answers the question that you asked, not the question you wanted answered.   Here is a flow chart on the logic as described.

    Once you get your first "Yes"  the process will stop.   You can string together an unlimited number of questions, but it will not keep going down the chain once you get to an answer.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 13.  RE: Nested IF Statement to Populate Field

    Posted 03-05-2020 14:53
    Terri, just chiming on with Don,

    You need to think through the different possibilities and the sequence that you expect them to occur and then be able to say in words what logic that you want.  Like Don says, the way that the IF statement works is that it will keep checking the various conditions and then it will stop at the first one that is true. 

    There can be 100 tests, in a row, Quick Base itself does  not care how many, so you need to be able to list the tests to check and QB will walk them in that sequence.

    So start with the words and we can help with the syntax.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------