11 Replies Latest reply on Jun 13, 2016 9:47 AM by ZoocMan85

    Birthday Script

    ZoocMan85

      Hey Everyone,

       

      I have a customer management system that has a persons name, address, phone, birthday etc. What i would like to do is have the program run a search once a day through the database and see if a person has a birthday today. If they do, send them an email. What is the easiest way of doing this ?

       

      Thanks

       

      ZoocMan

        • 1. Re: Birthday Script
          SteveMartino

          Add to start up script.

          Set Error Capture [On]

          Go to Layout //where birthday field resides

          Enter Find Mode [ ]

          Set Field [ Table::Birthday; Get(CurrentDate)

          Perform Find

          If [not Get(FoundCount)]

               Show Custom Dialog (No birthdays today.)

               //This will leave you on a layout with no records selected, so more steps may be necessary

          End If

            //If you have records you will be on a record, or records of the found set.

          //You could go right into sending emails

          • 2. Re: Birthday Script
            erolst

            That will only works for newborns … … who probably don't have their own mail address yet.

             

            I think the OP needs discrete day and month fields – or, using ExecuteSQL(), a global and a relationship …

             

            Set Variable [ $birthdayIDs ;

            ExecuteSQL ( "

              SELECT id

              FROM Contacts

              WHERE DAY ( DOB ) = ? AND MONTH ( DOB ) = ?

              " ; "" ; "" ; Day ( Get ( CurrentDate ) ) ; Month ( Get ( CurrentDate ) )

            )

            ]

            If [ not IsEmpty ( $birthDayIDs ) ]

              Set Field [ Table::gFoundIDs ; $birthDayIDs ]

              Go to Related Record [ from: Contacts_byFoundIDs ; layout: Contacts_list ; related only ]

              # [ send mail etc. ]

             

            You should also have a field in a Globals/Utility table to store the last date you sent these mails.

            Then do a check at the beginning of the script – you don't want to send these mails twice on the same day

            • 3. Re: Birthday Script
              SteveMartino

              Yeah forgot to trim the year off of it...

              • 4. Re: Birthday Script
                bgustin

                How about this?

                 

                Set Variable [$day;Vale:Month ( Get ( CurrentDate ) ) & "/" & Day ( Get ( CurrentDate ) ) & "/*"]

                Enter Find

                Set Field [Table::Birthday; $day}

                Perform Find

                • 5. Re: Birthday Script
                  ZoocMan85

                  This setup is going to be running on a p2p filemaker setup. The one pc thats hosting it just basically sits at the main screen. This solution will never really close, knowing this... should this be on an ontimer script  ?

                  • 6. Re: Birthday Script
                    bgustin

                    If you are not running FMP server (that can run scripts at timed intervals), I would put it on a opener script, that runs when you turn the machine on in the morning.

                    • 7. Re: Birthday Script
                      ZoocMan85

                      Yeah but the issue with that is the machine probably will never turn off. Any other work around?

                      • 8. Re: Birthday Script
                        SteveMartino

                        Can 'Install On Timer Script' work for you?

                        • 9. Re: Birthday Script
                          ZoocMan85

                          Yes that would be fine . Would i have it still as a startup script?

                          • 10. Re: Birthday Script
                            SteveMartino

                            You could.

                            Create your b'day script.

                            Use 'Install On Timer Script' step in new script, select b'day script as the script and select interval as 86400

                            60 (sec/min) X 60 (min/hr) X 24 (hrs/day)

                            Just remember the script will wait 24 hours before running the first time.  So run it manually the first day, and run it manually if you ever shut the computer off and back on.

                            • 11. Re: Birthday Script
                              ZoocMan85

                              Ok so i switch over to filemaker server, the peer to peer setup is awful. I want to make this a server side script that runs.  What is the easiest way for me to do this ?