13 Replies Latest reply on Oct 18, 2016 12:29 PM by jehjeh

    Email Script with Certain Criteria

    jehjeh

      I have created a script to send email and omit records that DO NOT have an email address in the email address field. The following script works exactly as it's supposed to.

       

      Go to Record/Request/Page [First]

      Loop

         If [IsEmpty (Producer Database::Current Email Address)]

            Omit Record

         Else

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

         End If

      End Loop

      Send Mail [BCC: Producer Database::Current Email Address; Subject: "My Subject line here"]

       

      What I am trying to incorporate into that same script is the command to tell it to also look at a field that contains the word "Inactive" and to look at a "Check Box" field in order to omit those records as well.  The "Inactive" field will only have that one word in it. So if that field is empty then it should be allowed to use that record to send email. The "Check Box" field should only omit if the check box IS checked. I would like my final result be that if any or all of the three fields meet the criteria, (i.e. empty email address field, filled Inactive field or a check box that is checked), then that record will be omitted from sending an email to that recipient.

       

      I am using FileMaker 7.  That is not going to change anytime soon.

       

      Your help is greatly appreciated

      Eddie

        • 1. Re: Email Script with Certain Criteria
          philmodjunk

          My condolences that you are stuck with version 7. Not only is it much more limited that today's versions, it was  a "major change" release that then had a number of issues as a result.

           

          But the following method works for all versions from 7 to 15:

           

          No loop needed.

           

          Enter Find mode[] ---> no pause, no stored criteria

          Set Field [Producer Database::Current Email Address ; "*" ]

          Set Field [Producer Database::InactiveField ; "=" ]

          Set Field [Producer Database::CheckBoxField ; "=" ]

          Set Error Capture [on]

          Perform Find []

           

          This will find all records in Producer Database where there is an email address and inactivefield is empty and checkboxfield is also empty. This will thus omit any record where email is empty or inactivefield is not empty or check box field is not empty....

          1 of 1 people found this helpful
          • 2. Re: Email Script with Certain Criteria
            jehjeh

            Does the setup you provided need to go into what I already started or is that it from start to finish?

             

            I can seem to get it to look like your example without the pause.

             

            Enter Find mode[]

            • 3. Re: Email Script with Certain Criteria
              philmodjunk

              The only thing that I left out was the send mail step.

               

              In the script editor, click the Enter find mode step and look down at the bottom left section of the window. You should see a check box labeled "pause" that you need to clear before the script can enter find mode without pausing.

              • 4. Re: Email Script with Certain Criteria
                keywords

                Just a proviso to add to Phil's scripted Find. You say: "if any or all of the three fields meet the criteria". I believe Phil's will only find the ALL case as shown (i.e. records which meet all three criteria). If you want the ANY OR ALL result I think it should be:

                Enter Find mode[] ---> no pause, no stored criteria

                Set Field [Producer Database::Current Email Address ; "*" ]

                New Request

                Set Field [Producer Database::InactiveField ; "=" ]

                New Request

                Set Field [Producer Database::CheckBoxField ; "=" ]

                Set Error Capture [on]

                Perform Find []

                 

                Someone please correct me if I am wrong.

                1 of 1 people found this helpful
                • 5. Re: Email Script with Certain Criteria
                  philmodjunk

                  jehjeh specified any or all records that do not have specific info. My script finds records that have all of the opposite--which produces the same thing

                  Omit any with a  missing address

                  omit any with "inactive" in the field

                  Omit any with check box selected

                   

                  is the same as finding

                  All records with an email address and an empty inactive field and an empty check box field.

                  • 6. Re: Email Script with Certain Criteria
                    mweldon@ansi.org

                    Hi-

                    You should use the "Else if" step, and the Send Mail step needs to be within the Loop, If parameters as shown below.

                     

                    Go to Record/Request/Page [First]

                    Loop

                        If [IsEmpty (Producer Database::Current Email Address)]

                             Omit Record

                       Else if [(not Is Empty (Producer Database::Inactive field)]

                             Omit Record

                       Else if [(not Is Empty (Producer Database::check box field)]

                             Omit Record

                      Else

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

                          Send Mail [BCC: Producer Database::Current Email Address; Subject: "My Subject line here"]

                       End If

                    End Loop

                    • 7. Re: Email Script with Certain Criteria
                      keywords

                      With respect, Phil, that misses the point I was making. Attached is a file containing 3 fields and 27 records—all the possible combinations of 1, 2 and 3. There are also two scripts: one finds three criteria in a single Find Request (in effect, an AND find); the other finds the same three criteria but in three separate Find Requests (an ANY find). Given that the OP stated: "if any or all of the three fields meet the criteria" I think it is the second type he/she was seeking. However, regardless of that, my point is that they are not the same.  I believe this file demonstrates the difference between these two approaches.

                      • 8. Re: Email Script with Certain Criteria
                        philmodjunk

                        I'm afraid that you've missed my point. I'm using an "and" find yes, but not on the same criteria as would be used for the "or" version.

                         

                        This works because I'm including the opposite of each value what Jehjeh originally specified to be omitted.

                        • 9. Re: Email Script with Certain Criteria
                          jehjeh

                          Good Morning Everyone,

                           

                          Thank you all for your replies, I have tried each of the solutions offered with only partial success.

                           

                          Phil's script yielded me the email that I want to send, but it included ALL email addresses and did not omit the ones that are listed as "Inactive" or had the check box ticked. It did omit the ones that didn't have any email address in the box. I did add in the Send Mail step at the end.

                           

                          Keyword's script yielded me the email I wanted to send but without any email addresses in the address line. In this case I also needed to add the Send Mail step at the end.

                           

                          Mweldon's script yielded me the best result, however not exactly what I need. I had to adjust the script to have the Send Mail step placed after the End Loop step in order to get it to work properly. With the Send Mail step before the End If step, the program would just get hung up and I had to press the esc. button to get out of it. After moving the Send Mail step to the end after the End Loop step, I got the email that I want to send, however there was only one email address in the "send to" window. That email address was from the last record.

                           

                          Any other solutions are welcome.

                           

                          Thank you,

                          Eddie

                          • 10. Re: Email Script with Certain Criteria
                            jehjeh

                            Update to my last post.

                             

                            I have resolved the problem.

                             

                            The method that worked exactly they way I wanted it to was Mweldon's method with a couple of slight tweaks. I had to move the Send Mail step to the end of the script and within that step, where you choose the recipients, (Producer Database::Current Email Address),  I had to check the box below the field that says, "Get values from every record in found set". With all that criteria in place, I got the results that I was looking for.

                             

                             

                            So ultimately it looked like this:

                             

                            Go to Record/Request/Page [First]

                            Loop

                                If [IsEmpty (Producer Database::Current Email Address)]

                                     Omit Record

                               Else if [(not Is Empty (Producer Database::Inactive field)]

                                     Omit Record

                               Else if [(not Is Empty (Producer Database::check box field)]

                                     Omit Record

                              Else

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

                            End Loop

                            Send Mail [BCC: Producer Database::Current Email Address; Subject: "My Subject line here"]

                                                         Check the box that says. "Get values from every record in found set"

                             

                             

                            I would like to thank all of you for the effort and help that you provided.

                             

                            Eddie

                            • 11. Re: Email Script with Certain Criteria
                              philmodjunk

                              The method you are using seems quite slow and inefficient.

                               

                              My scripted method base on performing a find does indeed work. I don't know why you got different results, but you can compare your result to mine to see that it does work, at least for the criteria that you specified.

                               

                              And since the objection was raised that I was setting up an "and" find when I should be using an "or" type find, I included a script that uses the "or" method and one that uses the "and" method that corresponds to my original suggestion. both find the same set of records.

                               

                              And since you are still using .fp7 files, I've included a version in that format.

                              1 of 1 people found this helpful
                              • 12. Re: Email Script with Certain Criteria
                                mweldon@ansi.org

                                Hi Eddie, I'm glad I could help, and I'm sorry about that glitch with the "Send Mail" step. I should have confirmed that bit of advice before giving it.  I have a lot of complex Email scripts myself and many include the "Send Mail" within the loop. I also wanted to mention two other bits of helpful information when it comes to E-Mail. -Cheers

                                 

                                One is is that if you want to send mail at a lightning speed,  you can send mail from FileMaker Server. You would need to configure the Send Mail option to use "SMTP Server", instead of using the "Email Client" option. You will also need to specify your outgoing smtp server and port information. Once you have set this up you can send mail to hundreds of records really really fast.

                                 

                                The other tip if you decide to stay with the Email Client option is that there is some free software called Click Yes from Context Magic that I've been using with my Microsoft Outlook Email.  It's a small app that automatically clicks yes when sending emails using Outlook so you don't get blocked by Outlook's security. I use version "Express Click Yes 1.2" and it works great but you may want to use ClickYes Pro. It's up to you. below is a link to compare and download any of their free apps.

                                A program is trying to send e-mail on your behalf. Bypass this message with ClickYes.

                                • 13. Re: Email Script with Certain Criteria
                                  jehjeh

                                  I have retried Phil's method and it does work. The problem seems to have been in the way I set up the script.

                                   

                                  In each of the three "Set Field" steps I initially couldn't figure out how to include the criteria needed to create the step.

                                  I was able to include the field but not the "*" and "=". So I was typing it in manually. I am assuming that the program was not able to understand that command as I had entered it and ignored it. I was finally able to figure out how to include that criteria into the step and now Phil's method works correctly. I did add the "Send Mail" step to the end of the script in order to send mail to the list of recipients.

                                   

                                  Enter Find mode[] ---> no pause, no stored criteria

                                  Set Field [Producer Database::Current Email Address ; "*" ]

                                  Set Field [Producer Database::InactiveField ; "=" ]

                                  Set Field [Producer Database::CheckBoxField ; "=" ]

                                  Set Error Capture [on]

                                  Perform Find []

                                  Send Mail [BCC: Producer Database::Current Email Address; Subject: "My Subject line here"]

                                   

                                  Phil raises the question of the other method being slow and inefficient. As the database stands now for testing purposes, there are only a hand full of records. So testing was the same speed for both methods. I think that when all the records are imported and the database grows, it may slow down. For the time being I will leave both methods set up in the database. If I see a difference in speed and efficiency, I will go with the one that works best.

                                   

                                  Thanks to everyone that has helped.

                                   

                                  Eddie