1 2 Previous Next 18 Replies Latest reply on Sep 9, 2015 5:41 PM by bigtom

    Email Alert Script

    rsjbarker

      I've set up a test db with my trial version of FMP.

      I need help/instructions on how to setup a script to send emails based on certain data within the DB.

       

      For example;

       

      •     The DB contains Licence Records.
      •     Each licence has a different expiry date.
      •     The task required is for the DB to send an email alert 30 days in advance of the licence expiry date.

       

      The Database includes the following relevant records for the script.

       

      1.     Licence Name
      2.     Expiry Date
      3.     Current Date.

       

      I've managed to figure out how to auto send an email from the script, but getting this relevant information included in the email has me stumped. Any help including tutorials or scripting would be appreciated.

       

      Warm regards

        • 1. Re: Email Alert Script
          SoCalFMDude

          Simply Perform Find in your script, searching for Expiry Date = Get(CurrentDate) -30.

          Send to that found set.

          You could make the search <=, and then once it sends emails, set a checkbox in the found set using the Replace command. If you do that, your search should Omit the checked records. That way if you don't open the database every day, you will catch the records you missed.

           

          Have fun!

          • 2. Re: Email Alert Script
            rsjbarker

            Hi,

            I appreciate what you're saying however, not having written a script before, I'm not sure how I would write this in script form? I have the script workspace open in the Filemaker app but I am not sure what I'm doing.

            • 3. Re: Email Alert Script
              bigtom

              Since you are not running FM server running this with a InstallOnTimer in FMPro is a little more of a hassle to deal with , but I think it can work if you have it run in a separate window without disturbing regular use too much if it only runs once a day. I would add a field for email_flag and this would be useful for the same script sending an email at 30 days and 15 days and 10 days or any number of times you want to remind them.

               

              For a simple 30 day notice:

               

              GoToLayout....

               

              Then you need a Find. So use "Perform Find" script step with the

              Expiry Date = Get(CurrentDate) -30

               

              then

               

              Go to Record/Request/Page [First]

              Loop

              Send Mail [No dialog; To: <to>; CC: <CC>; BCC: <BCC>; Subject: <subject>; Message: <message>; “<attachment>”]

              Pause/Resume Script [Duration (seconds): 3] 

              Go to Record/Request/Page [Exit after last; Next]

              End Loop

               

              The pause in in there to allow time for the email to actually send before moving to the next one. Increase or decrease as needed.

               

              For automation you can use a second script to call this one once a day:

              Install OnTimer Script [“EmailScript”; Interval: 84,600]


              With FileMaker Server this is much easier to automate.

              • 4. Re: Email Alert Script
                rsjbarker

                Thanks, I'm trying tol work through this solution step by step.

                • 5. Re: Email Alert Script
                  bigtom

                  Sorry it is not exactly complete. I was in a rush to get out of the office.

                   

                  Basically the script goes to the layout needed and the finds the records that are exactly 30 days before expiring. Then it starts from the first one and sends an email for each record and moves to the next record. When it gets to the last record that was found it exits the script.

                   

                  If you have any specific questions just ask.

                  • 6. Re: Email Alert Script
                    rsjbarker

                    Thanks, That's what it should be doing but I'm having difficulties getting past the first step:

                    When I do the following:

                    GoToLayout....

                    Then you need a Find. So use "Perform Find" script step with the

                    Expiry Date = Get(CurrentDate) -30

                     

                    Here's what happens:

                    fmpscript1.jpg

                     

                    fmpscript2.jpg

                    • 7. Re: Email Alert Script
                      bigtom

                      I hate the perform find window for this. Sorry about not making it more clear.

                      This is the better way to do the find via script in bold.

                       

                      GoToLayout....

                       

                      Set Variable ($Date , Get(CurrentDate) -30)

                      Enter Find Mode [ ] //Uncheck the pause box

                      Set Field ( ExpiryDate ; $Date ) //Sets the date to 30 days ago.

                      Set error capture [on] //This keeps dialog box from interrupting script if found set is 0.

                      Perform Find [ ] //No search criteria specified

                       

                      Go to Record/Request/Page [First]

                      Loop

                      Send Mail [No dialog; To: <to>; CC: <CC>; BCC: <BCC>; Subject: <subject>; Message: <message>; “<attachment>”]

                      Pause/Resume Script [Duration (seconds): 3] 

                      Go to Record/Request/Page [Exit after last; Next]

                      End Loop

                      • 8. Re: Email Alert Script
                        rsjbarker

                        I'll have a crack at it.

                        Thanks

                        • 9. Re: Email Alert Script
                          bigtom

                          For your email subject and body text you will want to use a calculation.

                          Sample:

                           

                          Subject : [ YourTable::License Name & " your license is expiring soon."]

                           

                          Body : [ YourTable::Current Date & "¶¶ " YourTable::License Name "," & "¶¶ Your license is set to expire on " & YourTable::Expiry date & ". Please renew as soon as possible. Further instructions and info as needed.¶¶Regards,¶¶rsjbarker"]

                          • 10. Re: Email Alert Script
                            bigtom

                            You might be able to get the Pause down to 0.5 seconds or so. It has been my experience when you need FM to interact with an outside application or service it is wise to give it a little time to do so. If you can get away without the pause go for it and throw a Freeze Window script step in there right after getting to the Layout to speed up the process if you need to.

                            • 11. Re: Email Alert Script
                              rsjbarker

                              I've got the mail sending through the script workplace and I'm trying to implement your steps. But already stuck on Step 2:

                              I've completed your  Step1: 

                              Step 1: Set Variable ($Date , Get(CurrentDate) -30)
                              Step 2: Enter Find Mode [] Uncheck the Pause Box. But
                              I can't find the toggle where I can turn the Pause on or off?  

                              Sorry I'm a bit of a dummy with all this.

                              • 12. Re: Email Alert Script
                                bigtom

                                Use the blue gear.

                                 

                                Screen Shot 2015-09-07 at 1.31.59 PM.png

                                • 13. Re: Email Alert Script
                                  rsjbarker

                                  Hi,

                                  Thanks. I'm out of the office until tomorrow. I'll check it out then,

                                  Thanks for all your help....It's appreciated.

                                  • 14. Re: Email Alert Script
                                    rsjbarker

                                    Hi Big Tom,

                                    Thanks for the help.


                                    I've got this part sorted.


                                    • Set Variable ($Date , Get(CurrentDate) -30)
                                    • Enter Find Mode [ ]
                                    • Set Field ( ExpiryDate ; $Date )
                                    • Set error capture [on]
                                    • Perform Find [ ]

                                     

                                    But I'm not sure where the next part goes....

                                    Do I enter it directly underneath on the same "script page"

                                    Or, do I open a new "script page" for the info below?

                                     

                                    Go to Record/Request/Page [First]

                                    Loop

                                    Send Mail [No dialog; To: <to>; CC: <CC>; BCC: <BCC>; Subject: <subject>; Message: <message>; “<attachment>”]

                                    Pause/Resume Script [Duration (seconds): 3] 

                                    Go to Record/Request/Page [Exit after last; Next]

                                    End Loop

                                    1 2 Previous Next