How can I "summarize" a user field for a list of child records to get a list of users to send a notification to?

  • 0
  • 2
  • Question
  • Updated 3 years ago
  • Answered

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?

Photo of Ryan

Ryan

  • 68 Points

Posted 3 years ago

  • 0
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
There are a few choices as its not easy what you are asking.

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.
Photo of Ryan

Ryan

  • 68 Points
Hi Mark.  Thanks for the suggestions.  2 and 3 are not ideal as we have a few dozen project managers running hundreds or thousands of projects each year.  Manual intervention on the notifications is going to be cumbersome.

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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
Briefly, it's this.

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.

Keep repeating.

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.
Photo of Ryan

Ryan

  • 68 Points
I was actually going down a similar path before posting the question, but was only going to be able to pull in the Max child ID and Min child ID since those were basically handed to me on a silver platter.  Very clever pulling those back down to the children to use as filter criteria :smile: .  Once I started creating the summary fields and relationships it all fell into place.  Thanks for the tip!!!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
:)