4 Replies Latest reply on Sep 16, 2013 10:12 AM by philmodjunk

    Date calculation

    LynnRogers

      Title

      Date calculation

      Post

           Hi everybody

           I need some help on a date calculation script. I am using Filemaker Pro 12 Advance and I need a script that will take the date that is in a "creation Date" field in my PM Table and add the service frequency field in my equipment table (which is a number field representing the number of days before the next service) and put the results in my "Due Date" field in my equipment table. example

                  PM Table                                    Equipment Table                             Equipment Table

           Creation Date  Field                  Service Frequency Field                         Due Date Field

                  9/14/2013                 +          30 (Days) number will very         =           10/14/2013

           Any help will be greatly appreciated

           Lynn

        • 1. Re: Date calculation
          schamblee

               You don't need a script all you need is a calculation and would make it non stored calculation so it will update automatically.    I assume your Create Date Field is of type date and your service frequency field is of type number.   

               Your calculation would be

               PM::CreateDate+Equipment::ServiceFrequency   and return  results as type date.   Click on Storage Options then click do not store recalculate as needed. 

          • 2. Re: Date calculation
            LynnRogers

                 Sorry, I did not give you enough information. The reason I need a script is because there are actually 4 different PM tables that each will have a button to update the Equipment table. I have written several different script versions but none of them will calculate the date correctly. I have used set veritable & set field commands but no changes occur in the Equipment::Due Date field and I have also used the replace field content command and it does change the date but it is the wrong date.

                  

            • 3. Re: Date calculation
              schamblee

                   A script would not be the proper way to do this.   With a script you would have to loop thru all your records, which would make your database extremely slow.   A calculation field would be the best / fastest method  to handle this type calculation.   The number of tables would not matter, because the equipment table would have to be related to each pm table and then you could use a case statement in your calculation to determine which table to use or this could be limited by the relationships itself.

              • 4. Re: Date calculation
                philmodjunk

                     Since this date calculation does not refer to Get ( CurrentDate ) it does not have to be an unstored calculation.