3 Replies Latest reply on Apr 6, 2013 1:15 AM by NaturSalus

    email recipients through scripting

    NaturSalus

      Title

      email recipients through scripting

      Post

            

           Hello,

           I have the following relationship:

           Deviation --<DeviationPerson >--Person

           The DeviationPerson table has the DeviationPerson::PersonTask field.

            

      What I want to do is to collect the emails of the people to which I want to send an email: So that I can use it to send them emails.

      I choose who will get an email by passing "Mail Recipient" as a script parameter. So I can generate DeviationPerson records for which DeviationPerson::PersonTask = "Mail Recipient".

      So, for the current Deviation record there are related Person records that have  DeviationPerson::PersonTask = "Mail Recipient". Well, I would like to collect in a field the contents of their Person::Email fields.

            

           In order to send emails I have a standard setup.

      On a layout based on the Deviation table I have a portal to the DeviationPerson TO with all the people that I want to send emails to. From here I have the Email button that triggers the Launch Email script that is paused in the Email layout in wich I can make final modifications and from which I can trigger the Send Email script that will send the emails.

      The "magic" must occur in the Launch Email script.

            

      My standard Launch Email script is the following:

      # Error Handling

           Allow User Abort [ Off ]

           Set Error Capture [ On ]

           # Error Handling

           Allow User Abort [ Off ]

           Set Error Capture [ On ]

           Commit Records/Requests

           New Window [ Name: "Mail Recipients"; Height: 573; Width: 539 ]

           # Populate email global fields

           Set Field [ zResources::Email_Header; "Deviation #" & Deviation::DeviationNumber ]

           Set Field [ zResources::Email_Body; "Deviation: " &Deviation::DeviationTitle & "¶¶" &

           "Leader: " & "¶" &

           "Date: " & Deviation::DeviationOpeningDate ]

           Set Field [ zResources::Email_Cc; "" ]

           Set Field [ zResources::Email_Recipients; Substitute ( List (Person::Email ) ; "¶" ; "; " ) ]

           Set Field [ zResources::Email_Subject; "Deviation #" & Deviation::DeviationNumber & "requires your involvement" ]

           # Go to Email_Detail layout so that the user can send the email

           Go to Layout [ “Email_Detail” (Deviation) ]

           Show/Hide Status Area [ Lock; Hide ]

           Adjust Window [ Resize to Fit ]

           #

           Pause/Resume Script [ Indefinitely ]

           #

           Exit Script [ ]

            

      What I want to achieve is to make sure that the following script step:

      Set Field [ zResources::Email_Recipients; Substitute ( List (Person::Email ) ; "¶" ; "; " ) ]

      captures the emails of  the Person records related to the current Deviation record that also have DeviationPerson::TaskPerson = "Mail Recipient"

            

           So somehow I must collect the Person::Email values of those Person records related to the current Devaition record and that have DeviationPerson::TaskPerson = "Mail Recipient"

      I tried that finding  those records at the beginning of the Launch Email script as follows:

            

      # Error Handling

           Allow User Abort [ Off ]

           Set Error Capture [ On ]

           #

      # Collect the emails of the mail recipients

      Go to Layout [ “dev_DeviationPerson” (DeviationPerson) ]

      Go to Record/Request/Page [ First ]

      Loop

      Perform Find [ Specified Find Requests: Find Records; Criteria: DeviationPerson::PersonTask: “= "Mail Recipient"” ] [ Restore ]

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

      End Loop

      #

           Commit Records/Requests

           New Window [ Name: "Mail Recipients"; Height: 573; Width: 539 ]

           # Populate email global fields

           Set Field [ zResources::Email_Header; "Deviation #" & Deviation::DeviationNumber ]

           Set Field [ zResources::Email_Body; "Deviation: " &Deviation::DeviationTitle & "¶¶" &

           "Leader: " & "¶" &

           "Date: " & Deviation::DeviationOpeningDate ]

           Set Field [ zResources::Email_Cc; "" ]

           Set Field [ zResources::Email_Recipients; Substitute ( List (Person::Email ) ; "¶" ; "; " ) ]

           Set Field [ zResources::Email_Subject; "Deviation #" & Deviation::DeviationNumber & "requires your involvement" ]

           # Go to Email_Detail layout so that the user can send the email

           Go to Layout [ “Email_Detail” (Deviation) ]

           Show/Hide Status Area

           [ Lock; Hide ]

           Adjust Window

           [ Resize to Fit ]

           #

           Pause/Resume Script [ Indefinitely ]

           #

           Exit Script [ ]

            

           But didn't work surely because I am doing something dummy that I don't see.

            

      What is the correct way of doing it?

            

           Thanks,

            

            

            

        • 1. Re: email recipients through scripting
          philmodjunk

               Note, I stopped reading at:

               

          What I want to achieve is to make sure that the following script step:

               

          Set Field [ zResources::Email_Recipients; Substitute ( List (Person::Email ) ; "¶" ; "; " ) ]

               

          captures the emails of  the Person records related to the current Deviation record that also have DeviationPerson::TaskPerson = "Mail Recipient"

               Create new Occurrences of the DeviationPerson and Person tables (select both at the same time and click ++ button.). Define a relationship to the new occurrence of DeviationPerson like this:

               Deviation::__pkDeviationID = DevaitionPerson|Email::_fkDeviationID AND
               Deviation::constMailRecipient = DevaitionPerson|Email::TaskPerson

               define constMailRecipient as a calcualtion that returns exactly the same text as is entered in TaskPerson to identify the person as an email recipient.

               Now your list function can refer to the Occurrence of Person linked to DevaitionPerson|Email to get the list of email recipients.

               I know you don't use FileMaker 12, but if you did, there are ways to do this with ExecuteSQL that do not require the added table occurrences.

                

          • 2. Re: email recipients through scripting
            NaturSalus

                 Hello Phil,

                 Thank you for looking into my question.

                 I'll try your approach.

            I know you don't use FileMaker 12, but if you did, there are ways to do this with ExecuteSQL that do not require the added table occurrences.

            You have mentioned so many times the ExecuteSQL that I am curious.

            I do have FM 12 Pro Advanced and I think that others can benefit from your answer to this post. So if you have time, I am willing to try your suggestion on FM 12.

            As I am slowly learning from my past mistakes, I try to keep tables, table occurrences and layouts to a minimum. In this respect ExecuteSQL sounds promising. Anyhow, in the next 2 years FM 13 will be out and is going to be based on FM 12, so I better check out what  Execute SQL can do for me.

                  

            Thanks

            • 3. Re: email recipients through scripting
              NaturSalus

                   Hello Phil,

                   It works perfectly well.

                   Thanks