My approach to this task would be different than changing records (GTRR).
I would "walk the portal" (using a looping process) to collect the emails into variables such as $leads and $others, and then use those variable values for addressing appropriate email content, or script separate emails via another loop.
Or one could use a larger loop to both walk the portal and send a separate email from each portal row with the content appropriately based on their role.
Couldn't you just calculate a list of email addresses using an ExecuteSQL call?
Set Variable $to =
ExecuteSQL("SELECT EmailAddressField FROM Resources R JOIN Project_Resources P ON R.primaryKey = P.resourceKey WHERE P.UserRole = 'lead' AND P.projectKey = ?" ; ", " ; "" ; Project::primaryKey )
Set Variable $cc
ExecuteSQL("SELECT EmailAddressField FROM Resources R JOIN Project_Resources P ON R.primaryKey = P.resourceKey WHERE P.UserRole IS NOT 'lead' AND P.projectKey = ?" ; ", " ; "" ; Project::primaryKey )
Send Mail etc...
Grabbing a list out of thin air might be faster and less steps than Stephen's recommendation (although his does work, and might be easier to understand instead of trying to play around with ExecuteSQL).
I do agree that GTRR is a little kludgy, unless you did a hybrid of Stephen's second recommendation (one email per user in a loop). I don't recommend that anyways, as upping your outbound email send count is a great way to get your email server blacklisted.
PS - looking at your script, there's no need to "unset" runtime variables at the beginning of your script. (lines 2-4). runtime variables (IE $variablename) are only generated inside the script at runtime, and are never stored.
As opposed to $$variablename, which is a stored global variable, and will persist for your session unless you unset it.
PPS - use the "comment" script step, it's quite helpful for spacing your script and leaving you notes (IE, verified test to this point. Disabled this step for this reason. etc..)
Thank you Stephen - I am really a new to Filemaker. How do i access the portal and its rows? Can you point me to a sample of code that walks through portal rows.
Update: I think i figured it out. I need to first name the portal and use the Go To Object and Go To Portal Row script commands to access a specific portal on a layout. Looping though the rows and gathering the data should be straight forward.
Thank you Mike - Your approach is also good. I need to read more about SQL queries to get a handle on this. Thank you for the tips.
Sounds like you figured it out.
"Walking the Portal" is a very useful technique for collecting data into variables to use later in the script, and it can also be used with looping script steps to take scripted actions at each portal row -- in this case an email.
You can also use the list() calculation and get a list from the portal emails
It would be more difficult to split out the person marked "lead" though using this method. But yes List() is a very good function for dealing with grabbing portal data. Actually, Last() is an even better one!
Let me know what you think about this approach to set field values in a child table through a many to many join table.
I have my Project layout, and in it a Portal showing records from the child table. In the portal i an displaying fields that i want the user to interact with, but i am also hiding behind other fields that i don't want the user to have access. Based on certain logical steps, i set the value of these records that belong to the related child table.
The script seems to work. I know this is not the most elegant way of getting things done. But it seems to be an easy way to drill down to related records and set field values.
Do you see major issues with this approach?
The only issue I see is that in the many-to-many, you only see the middle "to" of that relationship, and only the first related record from the "many" on the right side via a portal.
If you try and set a value on the right side (the second "many"), it will only set the value for the first related record in the relationship using set field. The remaining related "many" records would remain untouched. If you are only setting fields in your intermediary "to" table, then there is no issue.