14 Replies Latest reply on Jun 25, 2013 6:20 PM by taylorsharpe

    Filemaker Server is sending hundreds of unwanted emails?

    DEC

      Hi There!

       

      I've set up a server-side scheduled script to send out a daily notification email, and was horrified to observe as Filemaker Server sent over 500 emails to the designated email addresses. Of course I tested the solution using only my own email addresses, so no harm done, but I'd love to figure out what I'm doing wrong. Here's the setup:

       

      1. Contract Table: This is a table of contracts, each of which has a field for "To," "Cc," "Bcc," "Subject" and "Email Body," for the purpose of crafting a custom notification for each Agreement record. Note that the user will more likely than not include more than one email address in the "To," "Cc," and "Bcc," fields.

       

      2. Agreement Alert Table: The key fields in this table are: i) Notification Date (there may be multiple notifications per agreement); and ii) Notification Status. These two fields appear as a portal on the Contract Table layout.

       

      3. The Script Process:

       

      A. The script first searches among all of the records in the Agreement Alert Table for records which have a Notification Date that matches the current date, and which have a Notification Status of "Pending."

       

      B. Assuming the search results in a found set, the script then loops through each Agreement Alert record to i) Send Mail based on the parameters from the related Contract Table (e.g., the "To," "Subject" and "Email Body" fields); and then ii) switch the Notification Status to "Sent. It exits after the last record.

       

      C. The script is programmed to send out one email using data from the current records, and not collect addresses across the found set.

       

      The result is hundreds of duplicate emails to each address in the "To," "Cc" and "Bcc" fields.

       

      The database is being served via Filemaker Server 12 on a remote MacMini.

       

      Thanks in advance for your insight!

       

      Warm Regards,

       

      D

        • 1. Re: Filemaker Server is sending hundreds of unwanted emails?
          mikebeargie

          Can you post the script so we can see?

           

          Are all the emails duplicates of the same email?

           

          Putting an email step inside of a loop is always dangerous if things go wrong, and you could be looking at getting blacklisted or shut down if things go haywire while not on watch.

          • 2. Re: Filemaker Server is sending hundreds of unwanted emails?
            taylorsharpe

            One common mistake that can cause this problem is that in the Send Email script step, there are two choices:

             

            1) One Email using data from the current record

            2) Multiple emails (one for each record in found set)

             

            Since I am always looping through and sending unique information in each email, I always want choice 1.  If you are looping through and have choice 2, you will send a copy of each mail to everyone else and go to the next record and do the same thing.  That way everyone gets tons of duplicates that are copies of emails meant for other people. 

             

            See if that could be the problem. 

            • 3. Re: Filemaker Server is sending hundreds of unwanted emails?
              DEC

              Thanks to you both, gentlemen... 

               

              The script is actually comprised of two scripts, pasted below.  The server is scheduled to run Script 1 on a daily basis; Script 2 is embedded within it.  I checked to ensure that the script is set to send the emails using from the current record, and also it doesn't collect addresses from records across the found set.

               

              I don't expect the script to result in sending more than a few emails each day, so the failure capture process that shoots me an email isn't a foreseeable issue; having said that, if you gents have a more efficient suggestion as to alerting me (the developer) of failures, that'd be awesome : )

               

              Thank you!

               

              -D

               

              SCRIPT 1:

              Go to Layout [ “Agreement_Alerts_InvisibleFind” (Agreement_Alerts) ]

              Enter Find Mode [ ]

              Set Field [ Agreement_Alerts::Alerts_Date; "//" ]

              Set Field [ Agreement_Alerts::Alerts_Status; "Pending" ]

              Perform Find [ ]

              If [ Get ( FoundCount )="0" ]

              Go to Layout [ original layout ]

              Exit Script [ ]

              End If

              Go to Record/Request/Page [ First ]

              Perform Script [ “Script 2” ]

              Loop

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

              Perform Script [ “Script 2” ]

              End Loop

              Go to Layout [ original layout ]

              Exit Script [ ]

               

               

               

              SCRIPT 2:

              Set Error Capture [ On ]

              Send Mail [

              Send via SMTP Server;

              To: Agreement_Alerts::Calc_To;

              CC: Agreement_Alerts::Calc_CC;

              BCC: Agreement_Alerts::Calc_BCC;

              Subject: Agreement_Alerts::Calc_Subject;

              Message: Agreement_Alerts::Calc_Body;

              Name: "D";

              EmailAddress: "D@ilovefilemaker.com";

              Reply-To Address: "D@ilovefilemaker.com";

              SMTP Server: "smtpout.secureserver.net";

              Port: 80;

              Authentication Type: Plain Password;

              User Name: "D@ilovefilemaker.com";

              Password: "filemaker" ]


              [ No dialog ]


              If [ Get ( LastError ) = "0" ]

              Set Field [ Agreement_Alerts::Alerts_Status; "Sent" ]

              Else

              Set Field [ Agreement_Alerts::Alerts_Status; "Error: Call D for tech support at XXX-XXX-XXXX." ]

              Set Field [ Agreement_Alerts::ErrorCode; Get ( LastError ) ]

              Send Mail [ Send via SMTP Server;

              To: "D@ilovefilemaker.com";

              Subject: "Send Agreement Email Alert Failure from Database";

              Message:

              "The below email failed to send: " & ¶ &

              "To: " & Agreement_Alerts::Calc_To & ¶ &

              "cc: " & Agreement_Alerts::Calc_CC & ¶ &

              "bcc: " & Agreement_Alerts::Calc_BCC & ¶ &

              "Subject: " & Agreement_Alerts::Calc_Subject & ¶ &

              "Email Body: " & Agreement_Alerts::Calc_Body & ¶ &

              "Error Code: " & Agreement_Alerts::ErrorCode;

              Name: "D";

              EmailAddress: "D@ilovefilemaker.com";

              Reply-To Address: "D@ilovefilemaker.com";

              SMTP Server: "smtpout.secureserver.net";

              Port: 80;

              Authentication Type: Plain Password;

              User Name: "D@ilovefilemaker.com";

              Password: "filemaker"]

              [ No dialog ]

              End If

              Set Error Capture [ Off ]

              Exit Script [ ]

              • 4. Re: Filemaker Server is sending hundreds of unwanted emails?
                mikebeargie

                CHANGE THIS:

                Go to Record/Request/Page [ First ]

                Perform Script [ “Script 2” ]

                Loop

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

                Perform Script [ “Script 2” ]

                End Loop

                 

                TO THIS:

                Go to Record/Request/Page [ First ]

                LOOP

                Perform Script [ “Script 2” ]

                Go to Record/Request/Page [ Next ]

                Exit Loop If ( get(recordNumber) = get(foundCount) ) //better way of exiting a loop.

                End Loop

                 

                the way you had it set, it would exit the loop before sending the email for the last record.

                 

                Still waiting to hear if all the emails were duplicates of the same email, or if they were all different?

                • 5. Re: Filemaker Server is sending hundreds of unwanted emails?
                  DEC

                  Awesome; thanks!  I've got very little experience with loops; I try to avoid them if I can, as they can be dangerous if flubbed : )

                   

                  As for the duplicates issue...

                   

                  There are only 4 sample records in the found set that I established for the purpose of testing.  2 have future dates, and so that leaves only 2 alerts in the found set.  The script sends the alerts to the proper email receipients (there are 4 in total, spread across the to, cc, and bcc fields); the problem is that it'll send the same email hundreds of times to the proper recipients...

                   

                  Hope this clarifies : )

                  • 6. Re: Filemaker Server is sending hundreds of unwanted emails?
                    BruceRobertson

                    No Mike, it is YOUR method that exits the loop before handling the last record.

                     

                    Your method, corrected is:

                     

                    Go to Record/Request/Page [ First ]

                    LOOP

                    Perform Script [ “Script 2” ]

                    Exit Loop If ( get(recordNumber) = get(foundCount) ) //better way of exiting a loop.

                    Go to Record/Request/Page [ Next ]

                    End Loop

                     

                    The corrected exit after last method is:

                     

                    Go to Record/Request/Page [ First ]

                    Loop

                    Perform Script [ “Script 2” ]

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

                    End Loop

                     

                    Or to be more rigorous with exit after last


                    If[ get(foundCount)]

                    Go to Record/Request/Page [ First ]

                    Loop

                    Perform Script [ “Script 2” ]

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

                    End Loop

                    End If

                    • 8. Re: Filemaker Server is sending hundreds of unwanted emails?
                      DavidJondreau

                      Lately, I prefer Omit[] over Go to Next record[]. It has a couple benefits (I like the Exit Loop right after the start of the Loop[], it doesn't through a error at the last Go to Record, and you can skip the error checking of a found set after the find).

                       

                      Go to Layout [ “Agreement_Alerts_InvisibleFind” (Agreement_Alerts) ]

                      Enter Find Mode [ ]

                      Set Field [ Agreement_Alerts::Alerts_Date; "//" ]

                      Set Field [ Agreement_Alerts::Alerts_Status; "Pending" ]

                      Perform Find [ ]

                      Loop

                      Exit Loop If [ Get ( FoundCount ) = 0 ]

                      Perform Script[ Script 2 ]

                      Omit Record

                      End Loop

                       

                      Back to the OP, there's nothing in the psuedocode you posted that would cause the problem. If you can post your file, that would be best.

                      • 9. Re: Filemaker Server is sending hundreds of unwanted emails?
                        mikebeargie

                        totally right, I misordered it, the go to next record should be last before end loop.

                         

                        Man, is today a monday or something?

                        • 10. Re: Filemaker Server is sending hundreds of unwanted emails?
                          mikebeargie

                          I agree with David below, might help us if you could post a sample file with this piece isolated so we can test it out. Nothing looks suspicious in terms of repeating/duplicating.

                          • 11. Re: Filemaker Server is sending hundreds of unwanted emails?
                            DEC

                            Wow... careful character-by-character adherence to Bruce's prescription and file example (thanks for that!) cracked it.  And thanks to the group in general for taking the time to lend a hand.  It's amazing what Filemaker can do, but time and time again I've found myself indebted to the brilliant minds and generous hearts of the folks on this forum with respect to the most difficult tasks that have confronted me. 

                             

                            Thanks so much to you all for your kind consideration!

                             

                            Warm Regards and Best Wishes,

                             

                            D

                            • 13. Re: Filemaker Server is sending hundreds of unwanted emails?
                              DEC

                              I've honestly not yet been able to determine what the error was, other than to say that when I redrafted the script to match Bruce's recommend (below); it worked perfectly.  I also got rid of the cc and bcc fields, since the client actually doesn't have a business need for them.  I'll later study the issue a bit more in depth so that I can obtain an understanding of the failure and avoid it in the future, but I'm at this point in such a celebratory mood that I'm going to take a day off and raise a glass to all you gents before cracking into it : )

                               

                              -D

                               

                              Go to Record/Request/Page [ First ]

                              Loop

                              Perform Script [ “Script 2” ]

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

                              End Loop