7 Replies Latest reply on Jul 8, 2013 10:30 AM by philmodjunk

    Automated service timer

    AndrewGuy

      Title

      Automated service timer

      Post

           Hi everyone,

           I'm new to Filemaker pro 12, having spend about 3 weeks building a database to keep track of customer records. One very important feature I have been struggling with is an automated way to alert us in the office when a customer's service is up for their boat. The way we want this to happen is to put a date in a field, and then after the specified amount of time (4 months, 1 year, 2 years), a check-box would automatically check itself, and we could sort records based on this to view who needs service. I have tried various scripts, such as I have built a script to check whether the fields date is a certain amount of time away from the current date, and this date check seems to work, but I am having trouble getting the script to run for all of our 1000+ records and update them all. I feel like there has got to be a simple way to do this, but am unsure.

           Thanks! 

        • 1. Re: Automated service timer
          philmodjunk

               Months are not all the same number of days nor are years all the same number of days. Would 120 days work for "4 months" and 365 days work for "1 year"? If so, that simplifies the math a bit.

               You can use an unstored calculation field that checks Get ( CurrentDate ) against the computed date due:

               Get ( CurrenDate ) > DueDate

               Select Number as the result type.

               FOrmat the field with a value list of just 1 as the only value in the value list and select a check box format.

               The field will automatically update to show a 1 when service is due.

               The actual calculation for dueDate if you want to be able to specify an interval in months or years can be a bit more complex.

               Here's a simple version:

               DateOfLastService + MonthstoNextService * 30

               Select Date ast the Result type and make DateOfLastService a field of type date.

               IF DateOfLastService is 1/1/2013 and you specify 1 in MonthstoNextService, you'll get a due date of 1/31/2013

          • 2. Re: Automated service timer
            AndrewGuy

                 I have done something similar to this, but I want a system that will notify us if a due date has passed without having to have modified, recorded, or even highlighted/viewed a record in our set. It would be great to have some sort of system in place to check and update all records with their multiple dates each (nearly) instantly.

            • 3. Re: Automated service timer
              philmodjunk

                   You have two different issues, apparently:

                   1) setting the script up to make regular checks for records with expired due dates. THere are multiple options and the best one depends on your system configuration. If you host your file from FileMaker Server, you can set up a schedule to run the script once a day to pull up a list of records with an expired date. On systems not hosted by server, a robot file can be scheduled to be opened on a daily basis and this can trigger a script in the robot file that performs the check script in your current file. You can also set up such a script to run automatically the fist time you open the file via a setting in field options.

                   3) Doing the actual check. To find records with a 1 in that check box, you would use a script such as:

                   Enter FInd mode []
                   Set field [YourTable::CheckboxField ;1 ]
                   Set Error Capture [on]
                   Perform find[]

                   But you'll get a faster script if you use this method:

                   Enter FInd Mode []
                   Set FIeld [YourTable::Due Date ; "<" & Get ( CurrentDate ) ]
                   Set Error Capture [on]
                   Perform Find []

                   This is faster as you are searching on only indexed fields

                   Either way Get ( FoundCount ) can be used to check and see if any records were found.

              • 4. Re: Automated service timer
                AndrewGuy

                     Okay, so I think I uderstand what you're doing there, but when I put in the script, with pretty much the same set up you described above, the script just goes to search mode, waits for my input, and then if I hit enter or perform find, it tells me that no results match, and the record does not update. I think this would work, but I'm just not sure how to get around that "find mode" part, it just seems to interrupt the search.

                     I have a date filed titled "Engine OCS" with the example date 7/2/2012

                     Using your example, I built this:

                     Enter Find Mode []

                     Set Field [Test 1::Engine OCS; If(Date(Month(Get(CurrentDate)) ; Day(Get(CurrentDate)) ; Year(Get(CurrentDate))) ≥ (Date ( Month(Test 1::Engine OCS) ; Day(Test 1::Engine OCS) ; Year(Test 1::Engine OCS)+1)); "Due"; " ")]

                     Error Capture [on]

                     Perform Find/Replace []

                     I set the value list to have "due" as the value, I got around it by just haing the if statement return text. I know the actual comparison calculation is rather long and bulky, but I understand it, and it works.

                     So I put the date 7/2/2012, which is exactly 1 year ago, so the calculation should be returning a "due" value, but my problem is, once again, that the find command just enters the script into the stock find mode, waiting for my input, which is not exactly what I want the program to do. I want it to be able to be started (like from the layout open script trigger), and run without user input each day.

                • 5. Re: Automated service timer
                  philmodjunk

                       Your set field expression makes no sense. You need it to enter text that correctly evaluates as date based criteria for a find request. Your expression will enter either a 1 (true) or a 0 (false) into that field which will not find anything.

                       And

                       Perform Find/Replace is the wrong script step.

                       It must be

                       perform Find []

                       See this thread for examples of scripts that perform finds: Scripted Find Examples

                        

                  • 6. Re: Automated service timer
                    AndrewGuy

                         Okay, I think I got a little confused about the purpose of that script, sorry about that. I now realize that the Find based script is to search out and return all the records that have been marked as "due" or checked, but I am still confused as how to write a script to look at the dates in the records, compare them to todays date (I understand how to build that compariosn calculation, at least) and then checks the box if a given amount of time has passed, much like the script you mentioned at the end of the first paragraph of your second response. Something that would run every time the program is opened, and it would look at all the dates and check the check boxes.

                         So how would I build a script to essentially "update" every single record? I made a script that can sort of do this, but it only works on the currently selected record. I want it to atonomously check all the records without an operator manualy going through each record and running the program.

                         I know my date calculation looks pretty weird, but I've gotten it to be general enough to where it works. For the example above, I wrote it to see if today's date is greater than or equal to the date of the service, plus a year, that way, for instance, if the service was performed on 7/3/2012, the calculation would see arethmetically that 7/3/2013(todays date) = 7/3/(2012+1), so it retuns the text value "due", which then would set the check box to be checked.

                    • 7. Re: Automated service timer
                      philmodjunk
                           

                                But I am still confused as how to write a script to look at the dates in the records, compare them to todays date

                           I gave you the exact scrip to use in a previous post:

                           Enter FInd Mode []
                           Set FIeld [YourTable::Due Date ; "<" & Get ( CurrentDate ) ]
                           Set Error Capture [on]
                           Perform Find []

                           You only need update that to use your names for the table occurrence and field in place of mine in the first part of the set field step. You can then use Replace Field contents after the Perform FInd to update a field for every record found by the script.

                           7/3/2013(todays date) = 7/3/(2012+1)

                           is not a valid date calculation in FileMaker. FileMaker will interpret 7/3/2013 as 7 divided by 3 divided by 2013.