7 Replies Latest reply on Mar 13, 2015 6:57 AM by philmodjunk

    My table has the following fields: Region, Coordinator, Manager, RecruiterA, RecruiterB,...

    ErickJ.Gonzalez

      Post

      My table has the following fields:

      Region, Coordinator, Manager, RecruiterA, RecruiterB, RecruiterC and RecruiterD

      I have two buttons in my database, that when I change the status of a family in my database I click:

      Button 1 -  and an email with a pdf attachment of the record is sent to "Sarah (coordinator)" and cc's "Michael (supervisor)", "RecruiterA"  and "RecruiterB" in Region1

      or if I click:

      Button 2 -  an email with a pdf attachment of the record is sent to "Melissa (coordinator)"  and cc's "Maria (supervisor)", "RecruiterC"  and "RecruiterD"  in Region2

      So here is my issue:

      Ideally I would press one button to generate an email to the corresponding individuals. Each family has a corresponding recruiter and I would like to have the email go to the corresponding recruiter and not both recruiters in the region.

      As it stands right now I click the button and have the script set up to show the dialog box, where I then manually remove the email of the Recruiter that should NOT receive the email.

       

        • 1. Re: My table has the following fields: Region, Coordinator, Manager, RecruiterA, RecruiterB,...
          FilmUser

          I don't know if this is the easiest way, but I have a field - addresses_email, a calculation field, using Cases to determine the email addresses needed based on values that are in the relevant fields. Then this field is selected in the script to populate the email addresses. You can make separate fields for cc, etc. When something changes in email addresses, or people who are the recipients, just change the calc fields and the addresses change in the script.

          • 2. Re: My table has the following fields: Region, Coordinator, Manager, RecruiterA, RecruiterB,...
            philmodjunk

            Each family has a corresponding recruiter

            What data in your database identifies which recruiter corresponds to a given family?

            Seems like you have more than one table, at least two, possibly 3. Can you describe this in more detail?

            And I don't understand what "rule" is specifying "recruiter A" and "recruiter B" for button 1, but specifies "Recruiter C" and "Recruiter D" for button 2. This sounds a bit like your data model may not have an optimum design here as you could use a related table to list each recruiter linked to the particular region, coordinator and manager/supervisor (you say manager in one place and supervisor in another).

            I can point out that you can specify multiple values to pass as a script parameter to your script when the button is clicked. The script can then use that data to generate an email with different recipients specified depending on the data passed as a script parameter and thus different buttons can use the same script to get different results.

            • 3. Re: My table has the following fields: Region, Coordinator, Manager, RecruiterA, RecruiterB,...
              ErickJ.Gonzalez

              Your right Phil I have 3 tables:

              Database Name: Validated/Pending COEs

              This database is exclusively for my use with the exception of the email that it generates to provide a status update the Recruiter (AKA Community Liaison) and the regional office staff.

              When a recruiter/community liaison submits identifies a family they complete an application which is in turn is submitted for approval. The info that goes into the database comes directly from the application called a COE.  The family "belongs" to the recruiter that identified it.

              Button 1 (script): 

              Save Records as PDF [Restore; No dialog; "COE Status Update.pdf; Current Record]

              Send Mail [Send via E-mail Client; No Dialog; To: "coordinator@region1.org"; CC: "manager@region1.org, recruiterA@region1.org, recruiterB@region1.org"; Subject "COE Status Update"; Message: " Hello Eastern Regional Team,¶this is...]

              Button 2 (script):

              Save Records as PDF [Restore; No dialog; "COE Status Update.pdf; Current Record]

              Send Mail [Send via E-mail Client; No Dialog; To: "coordinator@region2.org"; CC: "manager@region2.org, recruiterA@region2.org, recruiterB@region2.org"; Subject "COE Status Update"; Message: " Hello Eastern Regional Team,¶this is...]

              I have a field called "COE Status".  The ideal would be have a script that generates an email to correct group of people based on the COE status- which can be "Pending" (when I receive it and  prior to approval), "Approved" (when it has been approved), "Found Ineligible" (when the family does not qualify) and "COE Returned" (when the family may still qualify however the application needs additional info or corrections).

              (The PDF that is attached to the email provides the corresponding staff with all the pertinent info they need to begin providing services to the family/student)

              Thank you,

              • 4. Re: My table has the following fields: Region, Coordinator, Manager, RecruiterA, RecruiterB,...
                philmodjunk

                What is the "context" for your buttons?

                They are placed on a layout of course, but on which of the three tables shown is that layout based? And where on that layout are they placed? In the header? the Body? a portal row?

                At first glance, I don't see a need for two buttons as the current record or current portal row where the button is located would seem to be all that is needed to determine which set of data is used by your script. so I need to know why you have two buttons.

                Additional comments:

                Matching records by Name is far from optimum. Names are not unique and names in a database have to be changed at times and for a number of reasons. Both duplicate names and changing names create complications for the correct function of your database. I suggest adding an auto-entered serial number field to community liaison and using it instead of the name to match to other tables in relationships.

                I see multiple "child" and DOB fields. A more flexible option would be to use a related table to list children and details such as DOB that is specific to a particular child.

                • 5. Re: My table has the following fields: Region, Coordinator, Manager, RecruiterA, RecruiterB,...
                  ErickJ.Gonzalez

                  The Buttons are located in the header of the main layout "Validated/Pending COEs", and I agree there should not be 2 buttons but as I am a beginner at w/ Databases and FM I do not know how to write the script  so that it does exactly what I need it to do. 

                  A bit frustrated because while clearly there is a better way I have not found a venue to receive training on FileMaker Pro (from the ground up) and I have been piecemealing this on my own. THANK GOODNESS for knowledgeable people like yourself > any recommendations for trainings/courses/classes on FM.  I have gone through a lot of youtube videos and lidia, but without some of the fundamentals some videos and articles can be difficult to understand.

                  A little about this database:

                  Only I enter data into it, It generates my envelopes as well as a welcome letter to the family. The only piece that is missing is the ability to email the corresponding individuals the status of the application. 

                  I have just done away altogether with the middle table the "Recruiter/Region" as it was redundant and the info was also contained in the "Community Liaison" Table .  I am attempting to give each recruiter/community liaison a serial number... but when I create a auto-enter field for a serial number (Indexed, Auto-enter Serial, Can't Modify, Always Validate, Required Value, Unique) in the Community Liaison Table and exit the layout mode the field is blank...

                   

                   

                  • 7. Re: My table has the following fields: Region, Coordinator, Manager, RecruiterA, RecruiterB,...
                    philmodjunk

                    Defining an auto-enter field option will not result in data (such as a serial number) being entered into the fields of existing records. You'd need to use Replace Field Contents with its serial number option to update them.

                    I would use these tables and relationships:

                    Children>----Families>-----Recruiters>-------Regions

                    Families::__pkFamilyID = Children::_fkFamilyID
                    Recruiters::__pkRecruiterID = Families::_fkRecruiterID
                    Regions::__pkRegionID = Recruiters::_fkRegionID

                    For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                    But back to your buttons. What I see is a form view layout. A layout where you see the record for one family at a time. So let's consider your buttons from the context of viewing that one record for one family. How do you know whether to click button 1 or button 2? What circumstances/data determine which button you should click?

                    Training in FileMaker:

                    There are a number of resources available from books to videos to other tutorials. Even free videos on YouTube. There is also this offering free from FileMaker: https://itunes.apple.com/us/book/filemaker-training-series/id787527886?mt=11

                    And I have a few small offerings of my own in this area, but they are intended more for an "intermediate level" developer looking to improve their skills and assume a certain level of knowledge on the part of the user from the start:

                    You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

                    Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
                    Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

                    Caulkins Consulting, Home of Adventures In FileMaking