7 Replies Latest reply on Jan 24, 2013 3:07 PM by FF

    Help with calculating a date that comes before given date

    FF

      Title

      Help with calculating a date that comes before given date

      Post

           I need help in writing a script that calculates a date based on X number of days before a given date.

           For example, I need to do a task 14 days before an event starts. How do I write a script for that date?

        • 1. Re: Help with calculating a date that comes before given date
          Sorbsbuster

               I am not clear what you want the script to do.  A calculation to show a date x days before another date is:

               NewDate = OldDate - x

               You can also use that to set a field value to search for in Find mode, for example.

          • 2. Re: Help with calculating a date that comes before given date
            philmodjunk

                 To be more specific, to calculate a date 14 days in advance of a given date:

                 GivenDateField - 14

                 FileMaker dates are stored as a number counting the number of days from 12/31/0000 to the date shown in the field so computing a new date simply requires adding or subtracting the needed number of days.

            • 3. Re: Help with calculating a date that comes before given date
              FF

                    

              OK, thank you for the help. But it is still not working for me.  I have used Filemaker Pro 10 for several years. I taught myself the program. Last week I went to a basic training in Filemaker Pro 12. The presenter help me see more of the potential of the software. I work for a non-profit and can not afford more classes or pay a consultant. Therefore, I am trying some things on my own. I have designed several layouts but I have not tried to write scripts or go beyond the basics. So here is my first problem.

                    

              I started with a Starter Solution from FM 12 called Evant Management. It had a field called "Due date".  So here are the steps I took.

                    

                     
              1.           In Manage Database  I went to the Field Name “Due Date”
              2.      
              3.           Made the Type “Date”
              4.      
              5.           I went to Options under the Auto-Enter tab and checked Calculate value then I click Specify
              6.      
              7.           At the top I select the context where I would find my date that I wanted to work from which was Events then I selected the field I wanted to use, when I did this the program put in Events::Date
              8.      
              9.           Then I typed added - 14
              10.      
              11.           So it looks like this      Events::Date -14
              12.      
              13.           When I back out to my form, there is no date in the Due Date field.

                    

              I hope this helps you understand what I am trying to do. Can you help me?

              • 4. Re: Help with calculating a date that comes before given date
                philmodjunk

                     Change your field type from "date" to "calculation". Then double click it or click options to open specify calculation. Make sure that "date" is specified as the return type.

                     Auto-enter calculations that reference data in a different table or record will not update automatically when that referenced data is changed. They also will not calculate a value for any existing records. You have to take special steps to update this field in existing records. The calculation field option will not have those limitations.

                     For a way to get changes to an auto-enter calc to update existing records, see: Updating values in auto-enter calc fields without using Replace Field Contents

                • 5. Re: Help with calculating a date that comes before given date
                  FF

                       Yea, it worked. Thank you so much!

                       Now to take it to the next level, what if I have more than one Due Date per event. Do I have to have a field for each due date with a different name? If so, I will have alot because I have several who have task that are either due before the event or during the event. The event is a training session and takes three months to complete. Most of them will be like the first one. Example: 3 weeks before the event, 1 week into event, 1/2 way through the event, etc.

                        

                  • 6. Re: Help with calculating a date that comes before given date
                    philmodjunk

                         While you could do that, I suggest you consider setting up a duedates or "timeline" table of records related to your events table. That allows you to set up any number of due dates for a given event without needing to have a large number of individual fields--which will be pretty inflexible.

                    • 7. Re: Help with calculating a date that comes before given date
                      FF

                           OK, I think I understand. It may take me a while to figure it out but this really helps. I will get back to you if I run into more problems. Again, thank you for your help.