8 Replies Latest reply on Aug 10, 2012 5:52 AM by taylorsharpe

    Reminder and overdue date alert.

    LadyBear

      Hi,

       

      I am still using filemaker 10 and I would love to be able to pick up when I need to send a reminder to clients to provide me with their updated insurance information or when it is overdue.

       

      The only field I have is the expiry date of their insurance.

       

      My idea is calculate the difference between the current date and the expiry date of their insurance. If this date < or equal to one month prior to the expiry date I would like to send the client a reminder. If the expiry date is overdue I would like an alert in filemaker.

       

      I would like to do something similar for annual payments.

       

      I haven't dealt with a whole lot of date functions at this stage.

       

      Many thanks for your assistance.

        • 1. Re: Reminder and overdue date alert.
          LyndsayHowarth

          You are certainly on the right track.

           

          It would require a status field which would calculate as you have described.

          Date (month(month(date); day(date);year(date)+1)

          ...should give you the renewal date.

           

          Date (month(month(date)-30; day(date);year(date)+1)

          ...should give you one month before renewal is due

           

          case(

          Date (month(month(date)-30; day(date);year(date)+1)  ≥ get(current date)-30; "Expiring";

          Date (month(month(date)-30; day(date);year(date)+1)  ≥ get(current date); "Overdue";

          "")

          ... should give you the value for the status.

           

          If you want to do it for every year in the future, you just have to look at the Client=policy_renewals relationship to and either sort them appropriately so the latest sorts to the top and use the Client_policy_renewals::Date field to calcc on or unsorted you can use the Client_policy_renewals::Max_Date ... created by a summary field.

           

          I hope this all makes sense... dinner is calling...

           

          - Lyndsay

          1 of 1 people found this helpful
          • 2. Re: Reminder and overdue date alert.
            taylorsharpe

            I would just do this:

             

            Assuming Expiration Date field is named "ED":

             

            Overdue Alert Date = Date ( Month ( ED ) - 1 ; Day ( ED ) ; Year ( ED ) )

             

             

            This this works across years.  FileMake has this weird way of recognizing a month outside of the range 1-12 as being the next or previous year.  Pretty cool!

             

            This calculation takes care of months that have something other than 30 days in them (e.g., 31 or 29). 

             

            Then you can use Lyndsey's Case Statement calculation (above message) based on this Overdue Alert Date result to identify the status of an account.  Or here is how I would do it:

             

            Case (

                 ED < Get ( CurrentDate ) ; "Expired"

                 Overdue Alert Date < Get ( CurrentDate ) ; "Current" ;

                 Overdue Alert Date >= Get ( CurrentDate ) ; "Expiring Soon"

                    )

            • 3. Re: Reminder and overdue date alert.
              comment

              LadyBear wrote:

               

              If this date < or equal to one month prior to the expiry date I would like to send the client a reminder.

               

              I think you mean 'if this date is within one month before expiry date'? In any case, I'd suggest you script this AND make a record of the reminder - otherwise you may find yourself sending close to 30 reminders to the same client regarding the same policy.

               

              Finding records whose expiry date is within one month from today is very easy and does not require adding any calculation fields.

              1 of 1 people found this helpful
              • 4. Re: Reminder and overdue date alert.
                LyndsayHowarth

                That's tidier than mine... but I don't suppose you were burning the chops, Taylor...

                 

                What you are also neatly pointing out to Ms Bear is that in the calculation she should investigate using the Let() function so that she can use human-readable values in the case statement.

                 

                - Lyndsay

                • 5. Re: Reminder and overdue date alert.
                  LyndsayHowarth

                  Michael Horak wrote:

                  Finding records whose expiry date is within one month from today is very easy and does not require adding any calculation fields.

                   

                  Which brings us to the problems if you don't record that today you sent a reminder... because tomorrow you might send another one.

                   

                  - Lyndsay

                  • 6. Re: Reminder and overdue date alert.
                    taylorsharpe

                    And boy do I like having a related table documenting the reminders so that down the road when someone says they didn't know, you tell them about the 3 reminders you sent and the dates you sent them.  Also, I use FileMaker to send SMS text messages to some people who prefer to receive reminders that way too.  I just set a schedule script that runs each day looking for who needs reminders and sending them out automaticaly so I don't even have to remember.  If you get really fancy, you can have FileMaker send and Receive Emails into a FileMaker table using plugins like CNS SMTPit and POP3it.  And there is nothing like tracking reminders and due dates with a FileMaker calendaring system such as one from Seedcode.  But combining automatic reminders with invoicing due dates has been a favorite of many of my clients. 

                    • 7. Re: Reminder and overdue date alert.
                      LadyBear

                      Could you please explain what you mean by the calendaring system from Seedcode?

                       

                      Thanks

                      • 8. Re: Reminder and overdue date alert.
                        taylorsharpe

                        Seedcode is a FileMaker developer that has specialized in making calendars and a few other things like synchronizing FileMaker databases and even making FileMaker a chat service.  But their calendars are the best and do things you would not think possible on FileMaker.  Check out their web page at www.seedcode.com.  They have a free version of their calendar that meets many peoples needs and they have a paid one that offers more features.  Download the free one and give it a try.  You'll be really amazed.  I've used it to integrate with many solutions and it impresses clients a lot.  It even has the ability to synchronize with Google Calendars and be a live iCal database server for iOS devices (iPads/iPhones).  Really slick!