I am trying to write a script that sends an email to the person assigned as project lead in the SPE department and copy the other folks assigned to the same project.
I have the following three tables: Project, Project_Resources, Resources.
The idea is that one project can have many resources associated to it, and each resource can work on many project. The join table Project_Resource captures the link between Project and Resrouces and also captures the role a resource plays in a project Resource Role field.
I can defintiely send an email to all resources associated to a project using the GTRR script, but if i want to discriminate the Lead from the SPE department as the To and Copy the other folks, it seems that i am not grabing the correct record from the join table.
Attached are screen shots of the Project form with poral showing assigned resrouces. The ERD, and the script. Any help with this would be greatly appreciated. Thank you.