8 Replies Latest reply on Nov 6, 2012 1:35 PM by Mike_Mitchell

    Auto-calculating new dates based on another date field

    bnuttman

      I want to update a field that changes based on another field.

       

      I have a student entry date field and I want to schedule his quarterly review every 3 months. This is easy to do with a second calculated field.

      The problem I am having is to automatically schedule the next quarterly review for the next period.

      For example, if the student entry date field is 3/5/2012(This date does not change), then the first quarterly review field should be 6/5/2012. When the date 6/5/2012 comes around the quarterly review field should then be set to 9/5/2012. And ongoing....

      Any ideas on how to make this work?

        • 1. Re: Auto-calculating new dates based on another date field
          Mike_Mitchell

          You can do this with a script. Something like:

           

          Enter Find Mode [ ]

          Set Field [ {your date field here} ; Get ( CurrentDate ) ]

          Set Error Capture [ On ]

          Perform Find [ ]

          Set Error Capture [ Off ]

          If [ Get ( FoundCount ) ]

            Replace Field Contents [ {your date field here} ; Date ( Month ( Get ( CurrentDate )) + 3 ; Day ( Get ( CurrentDate )) ; Year ( Get ( CurrentDate ))) ]

          End If

           

          LOTS of ways to clean this up (like accounting for what happens if you skip a day when you run this and miss a record or two, dealing with record lock conflicts - what happens if a user is editing a record when you try to update it - and so forth), but that's the basic idea.

           

          As an aside ... from a data management standpoint, it might be worth considering storing your quarterly reviews in a separate table and creating new records instead of just updating a data field. This would give you the ability to store a history of the reviews, what was done during them, etc., as well as avoiding some of the pitfalls of globally replacing field contents. Just a thought; might not work for your business process.

           

          HTH

           

          Mike

          • 2. Re: Auto-calculating new dates based on another date field
            bnuttman

            Hi Mike,

            Thanks for responding. I figured that I could do this in a script using something similar to your technique, but then, the script would have to be run every day. (is there a way to do that automatically?)

            I was wondering if there was a way to do this without a script....

            • 3. Re: Auto-calculating new dates based on another date field
              Mike_Mitchell

              Yes, you can do this automatically in a few ways:

               

              1) You can use the OnFirstWindowOpen trigger to run the script every time the file is opened. (If I did this, I'd use a field in a one-record utility table to store the last time the script ran so it would only run once a day. No need to do it more often than that.)

               

              2) You can, if you're hosting the file using FileMaker Server, schedule the script to run server-side at a time of your choosing. (This would be my preferred method.)

               

              3) You can use an OnRecordLoad script trigger to update the field ... but this method is fraught with issues. What happens if no user loads that particular record in a given day and the deadline is missed? And you'd have to trap for all the conditions that might cause you not to want to run the script - like user access privileges, did we already update the field, what about modification timestamps, etc. Blech.

               

              Of course, if you don't want to do it in a script, you could use an auto-enter calculation on that field. The Evaluate function gives you the option of setting one or more trigger fields that cause a re-evaluation of a calculation when they're updated. That would give you the ability to force the field to update whenever any field(s) you choose are changed on the record in question. However, this has all the problems of method (3) above, plus the added nuisance that it won't update unless one of your trigger fields is changed. Double blech.

               

              One final way around it I can think of at the moment would be to do it as an unstored calculation. It'd be a little tricky to write, but basically, what you'd do is use your anchor date (what you called the "student entry date") and then calculate, based on the current date, what the appropriate next deadline would be. However, you'll have to deal with the performance hit of an unstored calculation whenever you go to do a search on this date (which presumably you want to do). The advantage, of course, is that it would update when needed without having to run a script.

               

              Lots of options. Guess it's a matter of how you want it to work.

               

              Mike

              • 4. Re: Auto-calculating new dates based on another date field
                comment

                bnuttman wrote:

                 

                I have a student entry date field and I want to schedule his quarterly review every 3 months. This is easy to do with a second calculated field.

                The problem I am having is to automatically schedule the next quarterly review for the next period.

                 

                Try =

                 

                Let ( [

                today = Get ( CurrentDate ) ;

                elapsedMonths = 12 * ( Year ( today ) - Year ( StartDate ) ) + Month ( today ) - Month ( StartDate ) - ( Day ( today ) < Day ( StartDate ) ) ;

                elapsedIntervals = Div ( elapsedMonths ; 3 )

                ] ;

                Date ( Month ( StartDate ) + 3 * ( elapsedIntervals + 1 ) ; Day ( StartDate ) ; Year ( StartDate ) )

                )

                 

                The calculation must be unstored.

                • 5. Re: Auto-calculating new dates based on another date field
                  bnuttman

                  Michael,

                  That worked perfectly.  I never could have come up with that solution on my own.

                  Thanks so much.

                  • 6. Re: Auto-calculating new dates based on another date field
                    Mike_Mitchell

                    Thanks, Michael. I was heading out the door and didn't have time to work through the calc at the time.

                     

                    Mike

                    • 7. Re: Auto-calculating new dates based on another date field
                      comment

                      Ahm... what exactly are you thanking me for?

                      • 8. Re: Auto-calculating new dates based on another date field
                        Mike_Mitchell

                        For providing the calculation to answer bnuttman's question. Concise and very helpful.