Nested IF Statement to Populate Field
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(IsNul...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendYay for airplane Wifi,
Try this
// 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
------------------------------
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
------------------------------
That worked. Thank you!
------------------------------
Terri Wells
------------------------------
------------------------------
Terri Wells
------------------------------
- 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
------------------------------ - 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? - 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
------------------------------ 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:
- When the "PWO Proposal Due Date" is originally filled in, the "Proposal Received 2B Alight" field will always be null
- So, when that field eventually gets completed, I need to determine if the CR was Late or On Time.
- 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
------Original Message------
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?- MarkShnier__YouTerri, 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
------------------------------
