Nested conditions in Custom Calculated field | Community
Skip to main content
A
AlisonAn5
Level 3
June 7, 2016
Question

Nested conditions in Custom Calculated field

  • June 7, 2016
  • 3 replies
  • 884 views
Hi all, I am trying to create a custom calulated field that needs to meet 2 conditions before returing a result. It is referencing a date field and I want a True result only if the date is less than Today and Not Blank. So far I've been unsuccessful. I originially had it as below, but apparently the system includes blank as less than TODAY. IF(Launched Recurring Benefit Date<$$TODAY,Recurring Benefit,0) I also tried the opposite (below) with the same unsuccessful results IF(Launched Recurring Benefit Date>$$TODAY,0,Recurring Benefit) I'm fairly certain I'll need some sort of nested statement, but I can't seem to get it right. I greatly appreciate any help!
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

3 replies

A
AlisonAn5Author
Level 3
June 7, 2016
False Alarm! I had a lightbulb moment when I was almost out the door! This finally ended up working IF(Launched Recurring Benefit Date>$$TODAY,0, IF(ISBLANK(Launched Recurring Benefit Date),0,Recurring Benefit)) Whew...now I can sleep tonight...
    E
    EricLu2
    Level 10
    June 8, 2016
    < > Good job! Many times when I stop thinking about something, the answer automagically appears. Eric
    A
    Anonymous
    June 10, 2016
    When I'm building large nested if statements, I like to use a text editor like Sublime that can do parenthesis matching. You can copy white space into a formula successfully (and it makes it a lot easier to read) For true nesting, I like to build recursively. So start with your simpliest condition and put in placeholders like IF(IsBlank(ABCD),"True","False") Then copy in the whole statement and replace the appropriate True/False: IF(IsBlank(ABCD),"True",IF(IsBlank(ABCDE),"True","False") ) I've even been known to create a temp form to carefully test different parts of an expression and then collapse them together. I've built nested statements several pages long to encode complex business rules into a single value for conditional formatting of views, but it takes patience and LOTS of care. I also keep a seperate documentation page with a table of my calculated fields, the calculations and an explanation of what is going on. Finally I like to copy the calculation into the description of the field so that I can easily copy in the expression on a new form (or if the calculation gets damaged) Here's a simple example: IF(IsBlank(Agreed Budget Estimate),"Planning", IF(Actual Revenue>Agreed Budget Estimate,IF(Actual Revenue-Agreed Budget Estimate<2,"On Budget","Over Budget"), IF(IsBlank(Actual Completion Date), IF(Actual Revenue>(Agreed Budget Estimate*.95),"Near Budget", IF(Planned Revenue>Agreed Budget Estimate,"Planned Over Budget", IF(Planned Revenue=Agreed Budget Estimate,"On Budget", "Under Budget"))), IF(Actual Revenue=Agreed Budget Estimate,"On Budget","Under Budget"))))
    Terms & ConditionsAccessibility statement

    Loading footer...