14 Replies Latest reply on Dec 7, 2012 2:28 PM by melshamamyan

    Sending multiple emails

    melshamamyan

      Title

      Sending multiple emails

      Post


           Hi,

           We currently have a filemaker database in which we assign touble ticket's to our techs and at the end of the day we want to be able to send an email to each tech. Here is how we want it to work. If a tech has 3 tickets under his name in Filemaker, we want to hit the send email button, select which tickets get emailed as a text via outlook.

            

           Currently we have the send email button which generated a script to send an email for 1 ticket, how do we group the tickets by tech or select mutiple tickets in 1 email? We don't want to send out 3 different emails to 1 tech this will help us stay efficient.

           Please ask me to clarify if it sounds confusing.

            

           Thanks,

           Mel

        • 1. Re: Sending multiple emails
          melshamamyan

               So in essenese we need to create a loop which reads:

               DO{ //this loop is for the email application script to generate the email

               if(status="active"; Ticket#, "") // this will be a script inside the send mail options whitin FM for the subject line, if the ticket is active for a certain tech it //will insert the ticket #

               messege: ticket number & date created & company name & customer name & phone # & Date promised & service description // this is the messege inside the email..

               While{tech="mel"} // this loop keeps going until it finds all the tickets for mel then complies the email and ticket #'s.

               Any help with programming that into filemaker through a script or calculation?

                

               Thanks

          • 2. Re: Sending multiple emails
            philmodjunk

                 Much depends on the structure of your database and that is not something you have shared with us.

                 Looping through the found set is one option, but not the only one. And the exact details of your looping script can vary considerably depending one what tabels and relationships you have for your techs and the trouble tickets.

            • 3. Re: Sending multiple emails
              melshamamyan

                   Would you like to see some screen shots of the tables and database?

              • 4. Re: Sending multiple emails
                philmodjunk

                     That would be a good start. But include some commentary about the purpose of each table so we don't have to guess. If you have a lot of tables, you can crop your relationship graph down to just those that are relevant to this issue.

                • 5. Re: Sending multiple emails
                  melshamamyan

                       There are not too many tables on this database, I have also attached a picture of where the send email button goes along with the list of tickets which are active and assigned to a tech.

                  • 6. Re: Sending multiple emails
                    melshamamyan
                    /files/35455c0d0d/ticket_fm.JPG 989x656
                    • 7. Re: Sending multiple emails
                      philmodjunk

                           Remember that I asked for some explanations about the purpose of each table?

                           On what table is the layout you uploaded as a screen shot based?

                           In which table do you have email addresses for each Tech?

                           Is each "trouble" ticket a different record in Time Billings?

                           How do you assign a tech to a trouble ticket?

                           Are active time billings, pending time billings and time billings three occurrences of the same data source table?

                      • 8. Re: Sending multiple emails
                        melshamamyan

                             On what table is the layout you uploaded as a screen shot based?

                             The screen shot is based on the time billings layout.

                             In which table do you have email addresses for each Tech?

                             I Do not have email addresses for each tech, since outlook is opened up everytime you click on send email I figured we can manually pick who to email it to.

                             Is each "trouble" ticket a different record in Time Billings?

                             Yes, everytime you create a new ticket it is a new record.

                             How do you assign a tech to a trouble ticket?

                             The ticket # is auto generated every time you create a new ticket. You select the tech from a drop down menu. Look at the screen shot of the ticket screen below.

                             Are active time billings, pending time billings and time billings three occurrences of the same data source table?

                             I don't know, but when you click on status on the ticket create screen there is a drop down that we can choose from, active, complete or canceled.

                        • 9. Re: Sending multiple emails
                          philmodjunk

                               I recommend that you set up a table of techs with their name, a serial id number field and an email address and link each record in Time Billings via that serial ID to selected records in that Tech table.

                               That would enable you to click one button and email lists of assigned "tickets" to each of your techs.

                          • 10. Re: Sending multiple emails
                            melshamamyan

                                 Phil can you please expand on creating a table of techs with their name and serial ID field? Is this a table I create whitin Time Billings or under a new layout?

                                 And how does that enable me to click on one button and email a list of assigned tickets to each of the techs?

                                 Do I have to manually tie the serial ID number and the ticket #?

                            • 11. Re: Sending multiple emails
                              philmodjunk

                                   Is this a table I create whitin Time Billings or under a new layout?

                                   Neither is the case.

                                   Go to Manage | Database | Tables

                                   Enter the name of a new table: Techs and click create.

                                   Click the fields tab

                                   Define at least three fields:: __pkTechID (auto-entered serial number) , TechName, EmailAddress

                                   Link them to your existing tables in this relationship:

                                   Time Billings-----<Techs

                                   Time Billings::_fkTechID = Techs::__pkTechID

                                   See the first post in: Common Forum Relationship and Field Notations Explained if my notation is not familiar.

                                   Format _fkTechID as a drop down list or pop up menu with a value list of __pkTechID in the first field and TechName as the secondary field. To assign a record in Time Billings to a tech, select a tech using this field.

                                   Now you can go to the new layout created automatically for Techs and enter the name and email address of each technician. A portal to Time Billings on this layout will list all Time Billings records assigned to this tech record.

                                   I now am going to speak only in general terms in this post, but we can "drill down" from here in to greater detail in follow up posts if you need more explanation...

                                   From this layout, a script can send out one email for each tech in your found set. You can perform a find to find all records with at least one assigned Time Billings record. A calculation can be used with the list function to pull data from the set of related time billings records--exactly what data you need in your email is not clear to me at this point--one reason why I am not spelling out details yet.

                                   Other issues to resolve before we go into greater detail:

                                   Will this be a "canned" email where the database can compose the entire message in the body of each email sent or do you need to enable the user to add more info to each email before it is sent?

                                   Do you need to send an email listing all Time Billings records assigned to each tech or is there additional criteria such as a list of all assigned Time Billings records that are still "open" or have some such specified status?

                                   Do you want to send each tech a "snapshot link"? (Look this up in help to learn more.)

                              • 12. Re: Sending multiple emails
                                melshamamyan

                                     I have attached a screen shot of the script I am using in order to traverse through the records, identify the tech and send an email. Only I am still having a couple of problems and some help would be appreciated.

                                     When the script executes(person clicks on send email) it find the first person named Charles, opens up outlook, puts in the proper information and waits for you to send the email.

                                     How do I make it traverse through ALL the records first, copy the correct information I want to send in the email at once, then open the email client to send to Charles? I don't want to send Charles 10 emails of the tickets he is assigned to, I want to combine all of those emails into 1.

                                     Any ideas?

                                • 13. Re: Sending multiple emails
                                  philmodjunk

                                       That's oen of the reasons that I am suggesting a table of Techs. From that layout, List ( Time Billings::Ticket # ) will return a list of all Time Billings Records assigned to that technician.

                                       Using your approach, I'd first perform a find to find only the TimeBilling records that you want to list in an email to at least one Technician. Then I'd sort by the Technician field, so that all Tickets assigned to a given tech are in a contiguous group of records.

                                       The Looping part of your script can then look like this:

                                       Go to Record/Request/Page [First]
                                       Loop
                                          Set Variable[$Tech ; Value: Time Billings::Tech] --> I'm assuming that this is the field you are using to assign tickets to techs
                                          Set Variable[$TicketList ; Value: "" ]
                                          Set Variable[$TechName ; value: // refer to a field here with the tech's name, include another such variable for the email address if you have it in a table.]
                                          Loop
                                              Exit Loop if [ $Tech ≠ TimeBillings::Tech // script has advanced to next Tech's group of Time Billings records]
                                              Set Variable [$TicketList ; value: List ( $TicketList ; TimeBillings::Ticket # ) ]
                                              Go To Record/Request/Page [Next ; Exit after Last]
                                          End Loop
                                          #At this point we've gathered a list of ticket numbers to include in one email to a tech.
                                          Send Mail ---> here you can use the different variables such as $TicketList and $TechName as part of your email.
                                          Exit Loop IF [ $Tech = TimeBillings::Tech and Get ( FoundCount ) = Get ( RecordNumber ) ]
                                       End Loop

                                       Note:

                                          Exit Loop IF [ $Tech = TimeBillings::Tech and Get ( FoundCount ) = Get ( RecordNumber ) ]

                                       has been carefully designed to make sure that the last group of records are correctly handled. Get ( FoundCount ) = Get ( RecordNumber ) is only true for the last record in the found set.

                                  • 14. Re: Sending multiple emails
                                    melshamamyan

                                         Just want to say thanks for taking the time to go through this with me. Here is what I get when I attempted to follow those directions. I have uploaded a screen shot and I am sure something is wrong.

                                         I am still getting my mail client to only bring up 1 messege at a time. Im thinking its because in the messege box I have the wrong messege variables?

                                         How do I get the $Tech, and $TicketList into the messege to copy all of the information it has saved from the loop?