5 Replies Latest reply on Jun 30, 2011 11:05 AM by philmodjunk

    Creating emails with an attachment for multiple records

    NicoleHartman1367

      Title

      Creating emails with an attachment for multiple records

      Post

      Hi, 

      I am trying to write a script that will create an individual email for every record I have that will be sent directly to the right contact person for that record and will attach an excel file with only that specific records data.

      I know how to create an individual script that exports the record's data and then creates an email and pulls that exported file and looks up the contact's email address to create the email, but using this method I would have to create a seperate script for each record.

      How do I go about creating one script that will do this for every record in my database?

      Thanks in advance for your help!

      Nicole

        • 1. Re: Creating emails with an attachment for multiple records
          philmodjunk

          Enclose your script for sending one email in a loop. You may have two switch back and forth between a layout based on your table of emails and the layout based on the table from which you are exporting to generate the excel file:

          #Start on your table of email addresses
          Go To record/request/page [first]
          Loop
             Go To Layout [//layout for your excel export]

             //put your script steps for your excel export here

             go To layout [original layout]
             go To record/request/page [next ; exit after last]
          End Loop

          • 2. Re: Creating emails with an attachment for multiple records
            NicoleHartman1367

            Ok, that makes sense. One stumbling block I have though is that I am having to enter find mode to find records which create the excel spreadsheet I am attaching. When I enter find mode I don't see where I can enter a calculation to determine the find. 

            So my set-up is that I need to send an email for every record I have and for every record there are 100+ records in another table that I need to sort and filter to attach through an excel file. 

            So right now my script looks like this:

            Go to Layout (SKUs - which is where the attached records are)

            Enter Find Mode (Sort on a Business Partner Number, which is what each of my email records is)

            Perform Find

            Export Records (Exporting the list of SKUs per a Business Partner)

            Send Mail (Use the email address associated with the business partner and attach the file that was just exported based on the file name used at export)

            Can you help me with what the calculations would look like in the export records command and the Send mail command in order to get them to grab the right business partner sku records and then email them to the email for the right business partner and loop through the list of business partners to do this?

            Thanks,

            Nicole

            • 3. Re: Creating emails with an attachment for multiple records
              philmodjunk

              You wouldn't do that with the Export Records or Send Email steps. Export Records works from your found set, so you need to set up a find that pulls up the found set of records you want to export.

              Since you don't specify what "calculation" is the problem for your find, I can only suggest a generalized solution. If that doesn't work for you, feel free to respond back with a specific example of what you need your find to do here.

              Loop
                Go To Layout [SKUS}
                Enter Find Mode[] // clear the pause check box
                Set Field [Yourtable::yourField ; //Put your calculation here to produce the criteria you need.]
                Set Error Capture [on]
                Perform Find []
                If [ Get (FoundCount ) > 0 ]
                   #Export your Records
                   Go To Layout [Contacts]
                   #Send your email
                End If
                Go To layout [Contacts] //needed, just in case your find doesn't pull up any records to email
                Go To Record/Request/Page [Next ; exit after last]
              End Loop

              • 4. Re: Creating emails with an attachment for multiple records
                NicoleHartman1367

                Thanks!

                The calculation I am still having trouble with is what I put into the Set Field that will filter my SKU records to only give me records that are related to the Business Partner who's data I am pulling. 

                So for each contact they will have a list of 100+ SKUs records in the SKU table and I need a calculation to product the records for the specific export/email I am sending.

                So my initial thought would be that I would so something like:

                If ( SKUs::Distributor Number=Contact::Distributor Number ; 1 ; 0 ) but that is not working. 

                My desired result would be for the script to be able to know which contact I am on and be able to create a calculation that would evaluate what contact I am on and create a found set of SKUs for that contact to export. 

                Thanks for all of your help! I really appreciate you always responding to my posts!

                Nicole

                • 5. Re: Creating emails with an attachment for multiple records
                  philmodjunk

                  Do it this way:

                  Loop
                    Set Variable [$DistNumb ; Value: Contact::Distributor Number]
                    Go To Layout [SKUs]
                    Enter Find Mode[] // clear the pause check box
                    Set Field [SKUs::Distributor Number ; $DistNumb]
                    Set Error Capture [on]
                    Perform Find []
                    If [ Get (FoundCount ) > 0 ]
                       #Export your Records
                       Go To Layout [Contact]
                       #Send your email
                    End If
                    Go To layout [Contact] //needed, just in case your find doesn't pull up any records to email
                    Go To Record/Request/Page [Next ; exit after last]
                  End Loop

                  It's also possible to use Go To Related Records to pull up the records to be exported if you have a relationship linking Contact to SKUs by Distributor Number.