I have a project table (parent) and an activity table (children). When a project closes I want to send a notification to all the stakeholders of the project, which includes the individual owners of the activities. This notification could be triggered either directly by a status update on the project record, or I could pull the status of the project into the activity records... but my problem is that I don't know how to get the activity owners into the project record and I don't know how to fire a notification based on a formula/lookup field. So both ways that I can think of won't work. Any thoughts?
1. If you know that there are not too many child records, like maybe 6 or so as a maximum, then there is a technique with looping reverse relationships to float up field values from the first say 6 children up to the parent, then then use a formula to combine them into a List User field.
2. Have say a date field on the children and when you want to email them of a status change, update that date field in Grid Edit to trigger Notifications.
3. Hire a Developer like myself to write a script to walk down those child record to fire off the Notifications. So you would still need to push a button to fire off the script, and then it would walk down and fire the Notifications.
That said, option 1 sounds viable, as each project typically only has a handful of activities. There are some with more than 6 activities, but only very few. In those cases, I could set up my formula list user field with condition that says if there are more than 6 activities, just send to everyone on the activity team (the team doing the activities is different than the team leading the projects).
So how does the looping reverse relationship technique work?
Summarize Minimum Record ID# of the child. Call it [MIN Record ID#1 of child]
Look that down to the child records.
Then make a new Relationship where 1 Child is related to Many Parents based on that new field on the right side of the relationship.
Lookup the user field you need on that reverse relatiuonship, Say [project manager] from the Child to the Parent and call it [Project Manager 1]
Duplicate the field [MIN Record ID#1 of child]. Conveniently it will also duplicate the reverse relationship saving you a step. call it [MIN Record ID#2 of child]
Edit the summary condition of the duplicated field such that the Minimum record ID but subject to the condition that it is greater than [MIN Record ID#1 of child]. ie the second lowest Minimum Record ID#.
Still on the reverse relationship #2 Lookup the [Project manager] to the Parent . Call it [project manager 2].
On the main relationship Lookup that [MIN Record ID#2 of child] down to the children.
Contact me off line if you would like help setting that all up. Or I will try to help you here as my time allows.