6 Replies Latest reply on Nov 28, 2011 6:38 AM by DavidJondreau

    Date range "If" calculation doesn't work



      Date range "If" calculation doesn't work



      Apart from my regular contact management needs (next contact dates, priorities, etc.), I have certain "big rock" events/tasks that HAVE to happen, or the consequences are catastrophic.  Some of these events aren't due until next year (for example, August 13, 2012).  Given the difficulty of remembering these specific important events, and wanting to keep them separate and more important than the rest of my scheduled contacts, I want Filemaker to alert me not only when that date arrives, but 1-7 days in advance, and 7-14 days in advance.  These are big deal events, and I want a heads-up as well as a target date reminder.

      What I've Done

      I created a separate date field for this type of critical event/task, and then a series of calculated fields that compare the date in the "big rock date" field with today's date.  These calculation fields are supposed to detect when the "big rock date" field is 7-14 days away, and produce a banner message at the top of my main layout, "BIG ROCK AHEAD."  When the date is 1-7 days away, another field is supposed to say, "BIG ROCK IS ALMOST HERE."  And on the due date, another says, "BIG ROCK DUE TODAY."  This all actually worked, until I upgraded from Filemaker 5.5 to 11.  Evidently FM has changed the way it handles date calculations (I have another date-based calculation solution elsewhere that also used to work but now doesn't).

      What Works

           If(big rock due date = Get(CurrentDate), "BIG ROCK DUE TODAY", "")


           If(big rock due date < Get(CurrentDate), "BIG ROCK HAS PASSED!", "")

      Those work great.

      What Doesn't Work

      Date ranges.  I won't replicate here everything I've tried.  Thought it might be better to simply ask, "How to define a date range equivalent to today's date + 1-7 days, and today's date + 8-14 days, and have it return the desired text result?

      I tried a three-variable "If" calculation (today +7 < big rock due date < today +14).  Doesn't work.

      I tried breaking it down into multiple two-variable If calculation fields, and then recombining them.  Can't get them to work.

      I've made sure all the date-based calculations returned "date" results.

      I've tried GetAsNumber(date field) with a number result -- which worked in FM 5.5 -- but that didn't work (can't seem to get 81312 to be greater than 121511, and 121511 + 7 = 121518).  Argh.

      Blank results -- for example, If(big rock due date < today + 14, "BIG ROCK AHEAD", "") [note the "" at the end] -- are presumably considered "0" and hence are LESS THAN today.  Argh.

      I tried fixing that through another calculation field that makes all zero results "12/31/2099," just to put it on the right side of the time line, but that won't work (can't seem to get it to recognize the blank as "0" or "").

      Many of the results of my efforts produce multiple results for the same big rock due date, or illogical results (a future date as a past big rock date).

      Jeesh, I've spent way too much time on this.

      Bottom Line:

      How do I define a calculation field to identify when a big rock due date is in the date range of (today + 2 to 7 days), or (today + 8-14 days), and return a specific text result?  I can't get this to work, and it's driving me crazy.



        • 1. Re: Date range "If" calculation doesn't work

          You're getting tripped up by order of operations. Use parentheses to clarify what you're comparing. You should also use Case() instead of multiple If()s.


          Case (

          Big Rock Due Date = Get ( CurrentDate ) ; "BIG ROCK TODAY" ;

          Big Rock Due Date < Get ( CurrentDate ) ; "BIG ROCK OVER" ;

          ( Big Rock Due Date > Get ( CurrentDate ) ) and ( Big Rock Due Date < ( Get ( CurrentDate ) + 8 ) ) ; "BIG ROCK IS ALMOST HERE" ;

          ( Big Rock Due Date > ( Get ( CurrentDate ) + 7 ) ) and ( Big Rock Due Date < ( Get ( CurrentDate ) + 15 ) ) ; "BIG ROCK AHEAD" ;

          "NO TEXT DEFINED"


          • 2. Re: Date range "If" calculation doesn't work

            Hi Stephen,

            I find it easier to take advantage of Let() to clarify what is needed.  In my opinion, extra parentheses do not add clarity and should only be used if required to change the order of operations (which is not necessary in this situation).

            It helps to remember PEMDAS 'please excuse my dear aunt sally' which gives the order of operations evaluation which is:

            Exponent (powers, roots)

            This string ...

            ( Big Rock Due Date > Get ( CurrentDate ) ) and ( Big Rock Due Date < ( Get ( CurrentDate ) + 8 ) )

            ... evalutes the same if it is listed without the red parentheses.  Also, it is best to put functions which need to be evaluated more than once, such as Get(CurrentDate), within Let() to save evaluations.  Here is another calc to consider which might be easier to read (or to adjust in the future if you need to):

            Let ( [
            date = Big Rock Due Date ;
            now = Get ( CurrentDate )
            ] ;
            Case (
            now + 15 < date  ; "" ;
            "BIG ROCK " &
            Case (
            date  <  now ; "OVER" ;
            date = now ; "TODAY" ;
            date <  now + 8 ; "ALMOST HERE"  ;
            "AHEAD" )

            ... be sure the result is text and that, in the calculation Storage Options, you check 'do not store calculation results' so the display will stay current.

            ADDED: You may wonder why I list the Big Rock Due Date again ... if I have a calculation which includes a long field name and many times it is also combined with long table occurrence name, then I will name it something short in variable so the calculation is easier to read.

            • 3. Re: Date range "If" calculation doesn't work

              I agree with you LaRetta, Let() can make things easier. I just didn't want to throw too much at him on the first go.

              I wouldn't use "date" as a variable name though. Fiemaker can get weird when using the name of a pre-existing function as a variable.

              • 4. Re: Date range "If" calculation doesn't work

                You are right, David!  Thanks for the catch!  I usually use 'd' instead!

                • 5. Re: Date range "If" calculation doesn't work

                  Thank you so much. 

                  I went with David's solution, because I can grok it, and because of another detail I wanted to include (not mentioned in my original post).  My solution has four different text boxes (overlapping), so I can make them different colors (color coded, so I can tell status just by the color as well as the text).  I took the structure of David's solution and broke it into four separate calc fields. I think the value of Let is to simplify complex calculations within a single field (right?).

                  LaRetta's Let solution is very helpful, though, because I have seen the function but never understood it.  Now I think I will be able to use it in the future.

                  Thanks again.  Awesome help.


                  • 6. Re: Date range "If" calculation doesn't work

                    You only need one field. You can change the color of the field using Conditional Formatting.