14 Replies Latest reply on Jul 8, 2014 12:14 PM by john.s

    Sending email from Portal

    john.s

      Title

      Sending email from Portal

      Post

           I'm trying to send an email from a layout with multiple portals and could really use some help...I thought I was starting to get the hang of FM but this has me stumped.

           I've attached a screenshot with the table relationships.

            

      I have a Layout based on Schools with a Portal to “self join table” Schools | Select Active | Non Active.  The Portal is used to select a school and “button set up” goes to related record for that school using the current layout…

            

      Three other portals each to Join_School_Sports table…one for each sports season…

      Portals create a record for each sport selected to be covered for the school.

            

      At the beginning of each sports season after creating the sports to be covered for the school I want to send an email to the Athletic Director to confirm the sports. 

            

      I have a button for each season that will run a script to send the email.  I pass a script parameter with the _pk_SchoolCode and the Season.  I can’t figure out how to pull the name and email address for the Athletic Director out of the SchoolStaff table (field with Position = “Athletic Dir”  or how to pull the Sports Names for that school out of the SportsTeams table for each corresponding record in the Join_Schools_Sports table.

      Screen_Shot_2014-07-06_at_8.14.03_AM.png

        • 1. Re: Sending email from Portal
          philmodjunk

               Where are these buttons located on your layout. Are they inside one of the three portals?

               Since you have three portals to the same join table, it would seem that they are filtered portals. How are they filtered? (Post the expression used.)

          • 2. Re: Sending email from Portal
            john.s

                 Buttons are on the Layout based on Schools table and are not inside the portals.  the portals are filtered with SportsTeams::Season = 1...each portal is filtered with the corresponding Season ie. 2 for winter and 3 for Spring 

            • 3. Re: Sending email from Portal
              philmodjunk

                   If you placed the buttons inside the portal rows, the mouse click on the button would also but the focus on that portal record--which would simplify the process needed to get to data in the correct record in your SportsTeams table. You could then use Go to related records to get to the correct SportsTeams table.

                   You have three different options for accessing the needed data if you are using FileMaker 12 or newer. If using an older version, you have two different options:

                   1) Add table occurrences of SportsTeams and SchoolStaff that use additional match fields, some linking to calculation fields that have a constant value such as "Athletic Dir" to match to the correct record.

                   2) Use the data to perform scripted finds on layouts based on both SportsTeams and SchoolStaff to find the needed record and copy the data you need from that record to variables so that you can include that data in your email. (see Scripted Find Examples for some scripted find examples. )

                   3) If using FileMaker 12 or 13, an ExecuteSQL() function could extract the needed data for your email.

              • 4. Re: Sending email from Portal
                john.s

                     Thanks Phil,

                     I'm using FileMaker 12...I'll play with these suggestions and see how far I can get before needing more help...scripting is not one of my strengths, I do ok with simple scripts but get lost quickly ;-)

                      

                      

                • 5. Re: Sending email from Portal
                  john.s

                       Wow this is very frustrating...my lack of knowledge.  I'm trying to simplify the layout and have only one portal for the sports seasons.  We only set up one season at a time so I thought I could change the filtering from a fixed number i.e. SportsTeams::Season = 1 for spring...2 for Winter, etc.  and set a global field and use it in the filter which would allow me to eliminate two of the portals.  So I have a text field g_SelectSeason that I've put on the layout.  The field is updated with radial button using a Value List with Custom Values of:

                       1 Fall

                       2 Winter

                       3 Spring

                       I have this formula in the Portal Filter

                       SportsTeams::Season  =  GetAsNumber (SportsTeams::g_SelectSeason)

                       It always gives me the Fall season.  I have put the global field on the layout to see if it is changing when the radial button changes and it does.

                       Again I must be making a rookie mistake but can't seem to see what it is.

                        

                  • 6. Re: Sending email from Portal
                    john.s

                         OK...I just found one of my mistakes.  The portal is to a Join table that creates records.  While the portal is filtered by season using the formula the drop down for selecting teams is based on a value list for a specific season.  So the only way I can eliminate the other two portals is if there is a way to conditionally select value lists.

                    • 7. Re: Sending email from Portal
                      philmodjunk

                           From what I see here,

                           SportsTeams::Season  =  GetAsNumber (SportsTeams::g_SelectSeason)

                           Should work. A conditional format expression can refer to a field, such as SportsTeams::Season that comes from a related table instead of the portal table as long as the relationship matches to a record with the value you need in order for this to work. Since the relationship from Join to SportsTeams is Many to one, this should work.

                           But you are likely encountering a portal refresh issue as changing the value of the global field will not automatically update what records appear in the portal. Try this as a test. After selecting a different season in g_SelectSeason, run a script with this single script step: Refresh Window [Flush Cached Join Results]. If that results in a portal that correctly displays the records that you want, we can look at better methods than Refresh WIndow [Flush... for getting things to update.

                      • 8. Re: Sending email from Portal
                        john.s

                             Thanks Phil,

                             The refresh window script worked so I added an OnObjectModify Script Trigger to the field where the season is selected to run the script...the only change I needed to make was to add a commit record step before the refresh window...I remember that from another one of your posts.  So, now the form has been simplified and I'll see if I can get the original email stuff working.

                              

                              

                        • 9. Re: Sending email from Portal
                          philmodjunk

                               Yet Refresh Window [Flush cached join results] is often a far from ideal solution to this issue. I suggested it as a test to see if that was the issue keeping this from working for you.

                               If you move the global field to the Schools table and use it as a match field to the join table, You can select any field in the join table as a matching  field, but use the X operator instead of =. The filtered portal should then update automatically with no scripting required save possibly that commit records step.

                          • 10. Re: Sending email from Portal
                            john.s

                                 When I added that relationship with the X operator I lost the option to allow creation of records.  What am I doing wrong?

                                  

                            • 11. Re: Sending email from Portal
                              philmodjunk

                                   You are doing nothing wrong. That relationship change would keep "allow creation..." from working. But you can still add a scripted button that creates a new record in the portal's table.

                              • 12. Re: Sending email from Portal
                                john.s

                                     Thanks Phil...I'll have to set that up with a scripted button to create the new records.  Right now I'm trying to get the email working.  So far it pulls the correct Athletic Director from the SchoolStaff table and inserts their email address and it puts their name in the body of the email.  I'm having trouble getting the list of sports into the email.  One of your suggestions was to use a scripted find and copy the data into variables.  I have the scripted find working...what I can't figure out is how to copy the data into variables.  Each school has a different number of sports to be covered so I'm really lost as to how to set up the copy portion of the script...I'm assuming it will be a loop but from there I get lost.  Can you point me the right direction?

                                      

                                • 13. Re: Sending email from Portal
                                  philmodjunk

                                       But can't you use a relationship to get that list? Something like: List ( SportsTeams::SportName ) ? (and you can use substitute, to replace the returns with commas if you prefer.)

                                       That eliminates the need for a script, let alone a scripted find. With a scripted find, unless you are using FileMaker 13 and define a new "list" type summary field, getting a list of values from a found set is a bit cumbersome. You have to either loop through every record of the found set, or use a special layout with just the one field present on the layout where you can use copy all records to copy the values to the clipboard for pasting into a field.

                                       The following builds a return separated list of sport names from a found set of SportsTeams records:

                                       Go to Record/Request/Page [first]
                                       Loop
                                          Set Variable [$SportList ; value: List ( $SportList ; SportsTeams::SportName ) ]
                                          Go to Record/Request/Page [Next ; exit after last ]
                                       End Loop

                                       Note that $SportList & ", " & SportsTeams::SportName

                                       would create a comma separated list.

                                  • 14. Re: Sending email from Portal
                                    john.s

                                         The script works perfectly.  When I have some free time I will try to set it up with the relationship you suggested...it seams like a cleaner way to do it.  At least for now I have it working.

                                         Thank you soooo much Phil...you are a wealth of knowledge and a real asset to this forum.

                                         John