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.)
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
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.
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 ;-)
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:
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.
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.
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.
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.
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.
When I added that relationship with the X operator I lost the option to allow creation of records. What am I doing wrong?
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.
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?
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]
Set Variable [$SportList ; value: List ( $SportList ; SportsTeams::SportName ) ]
Go to Record/Request/Page [Next ; exit after last ]
Note that $SportList & ", " & SportsTeams::SportName
would create a comma separated list.
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.