13 Replies Latest reply on Dec 13, 2014 10:37 AM by philmodjunk

    Resetting a number field after one year

    TimCranwill

      Title

      Resetting a number field after one year

      Post

      Number field starts as 10 and is reduced though out the year But at the one year mark, Based on start date field, I want it to reset back to 10.

      Thanks

        • 1. Re: Resetting a number field after one year
          philmodjunk

          Do you also update the start date field at this time or do you need to update this at intervals of 1 year?

          A server scheduled script can perform a find to find all records that have "aged" to the point they need this update and then a replace field contents script step can update the field back to 10. But if you keep the start date field unmodified, you'll also need to set up an additional date field that you update each time that you do this so that your scripted find criteria can search on this date field.

          Enter Find Mode []
          Set Field [YourTable::DateField ; "<" & Get ( CurrentDate ) - 365 ]
          Perform Find []

          Will find all records in YourTable where DateField is older than 365 days from today's date.

          • 2. Re: Resetting a number field after one year
            TimCranwill

            The start date field will never change, yes every yearly "interval" the field resets back to 10.

            • 3. Re: Resetting a number field after one year
              CarlisleLandel

              Can you say more about how this field works?   Is it automatically updating depending on today's date?  How is it incrementing downwards?  Does it drop by 1 every 36.5 days?  

              Anyway, you could change it to a calc field = Round(((Mod(Get(CurrentDate-StartDate);365)/36.5);1); this will count down by hundredths of a year.

              • 4. Re: Resetting a number field after one year
                TimCranwill

                The number field is manually subtracted from. Regardless of the value in the number field it has to go back to 10 every one year interval.

                • 5. Re: Resetting a number field after one year
                  philmodjunk

                  And I've outlined a way to do that. Feel free to ask a follow up question if you need a more detailed response.

                  • 6. Re: Resetting a number field after one year
                    CarlisleLandel

                    If you're manually subtracting, then why not manually set to zero at the one year interval?

                     

                    Anyway, if the database is always on, then run a daily script to find all the records where DayofYear(StartDate)=DayofYear(Get(CurrentDate)) and then Replace Field Contents with the value 10.

                     

                    • 7. Re: Resetting a number field after one year
                      philmodjunk

                      I like the elegance of that last suggestion! Much simpler than mine!

                      There are several ways to run such a once a day script. Scheduling it via server is simplest, but this is not the only way this might be done.

                      • 8. Re: Resetting a number field after one year
                        CarlisleLandel

                        Thanks!  Yep, scheduling it via server works easiest, by a longshot.  I'm a Mac driver, so if it were hosted on my machine I'd schedule a cron job.  I've no idea how to do it on a Windows box, but I'm sure there is something similar.

                        • 9. Re: Resetting a number field after one year
                          philmodjunk

                          It's much the same on Windows systems.

                          Windows Scheduled Tasks can be set up to open a database file on a schedule. That file can have a script set to run OnFirstWindowOpen that performs a script in the main file that does this update.

                          Hmm, one possible wrinkle to think about with the "day of the year" method is to add code to handle situations where, due to one glitch or another, that scheduled script didn't run or failed to update a record or two (such as if another user has the record locked). I think you'll still need a field that records the date the record was last updated.

                          • 10. Re: Resetting a number field after one year
                            CarlisleLandel

                            Hmm, one possible wrinkle to think about with the "day of the year" method is to add code to handle situations where, due to one glitch or another, that scheduled script didn't run or failed to update a record or two (such as if another user has the record locked). I think you'll still need a field that records the date the record was last updated.

                            Yep, hence my "database is always on" proviso.  You could handle your scenario with global date field that records the date the script was run, and then run an if step that compares the run date to today-1, and if they aren't equal, you do the find for the appropriate date range and then do the replace field step; you'd finish by setting the global date field to today.

                             

                            • 11. Re: Resetting a number field after one year
                              philmodjunk

                              In a hosted database, changes to a global field (unless made by the Perform Script on Server or server scheduled script) don't persist. So in some situations, you may need a non global field in a related table.

                              And what you describe does not handle the situation where a user has edit locked a record and the replace field contents operation then skips that record instead of updating it. Ideally, this sort of update should be done in the early morning hours to avoid this issue, but some systems are being accessed round the clock and users can also accidentally leave their system up with the cursor in a field when they leave if they do not have the system set up to log out idle users.

                              • 12. Re: Resetting a number field after one year
                                CarlisleLandel

                                Oh, yeah.  The global field thing.  I always forget.  Anyway, though there may be a better way, I handle global values with an orphan table (I call it "variables") with a field to hold the value.  That works.   You'd update it for today's date the end of the script, and use it for the comparison.  

                                As for dealing with those folks who have locked up a record?  Boot em off!  wink 

                                I think we've pretty much got a solution in hand, though.   Have a great weekend.

                                 

                                • 13. Re: Resetting a number field after one year
                                  philmodjunk

                                  As for dealing with those folks who have locked up a record?  Boot em off!

                                  Yes, but other than doing that manually, (something I'm not going to vounteer to do every night during the "wee hours" wink) how would you "boot them off" automatically? Idle log outs would deal with the people that walk away from their system with a work station locked up, it doesn't handle active users working late at night--an issue that may or may not be possible depending on how a given system is set up and used. If it is possible for that to occur, you still have the situation where you may need to find records that have a date older than today's date in order to update them the next time around. That can easily be handled if you add a date field that you update with a current date each time your script does such a batch update.

                                  And it's not unheard of for a system issue, power outage, crash, hung server schedule... to keep such an automated update from taking place on a given day and then again you have a situations where the system has to "play catch up" in order to properly update the records.