Possible to Trigger Email when Related Records Are Updated through Complicated Relationship Chain?

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

My app has the following tables:

Associate < Assignments > Clients < Trust Roles > Contacts > Locations > Companies

'Contacts' are people, their contact info is contained in the Trust Roles, Contacts, Locations, and Companies tables.

We are wanting an email triggered to an Associate if one of their Client's 'Trust Roles' contacts had a change in any of the four pertaining tables (Contacts, Location, Companies or Trust Roles).

Is this possible???

Thank you!

Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
I would do this though a set of Subscription report on each table for "My xxxs changed yesterday.  e.g. My Contacts changed yesterday.  The "changed yesterday" is easy because that is just a report filter on date modified = yesterday.

I think that in previous postings we probably already solved the challenge of running reports for "MY" Assignments, Clients, Trust Roles, Contacts, Locations, Companies.  

If not, then by way of reminder, you have a formula field on the Associate table that evaluates to a 1 if the Associate is the current user


IF(User() = [Associate userid],1,0)

Then via lookups and summary maximums you just flow that 1 all the way from Associates to the other of that long wiggly snake to the Companies.

So just add that filter (ie that field = 1)  to your reports and set up the Subscription to All Associates and every morning each respective Associate will receive an email per table of any changes that they may care about in that table.  e.g. "My Contacts changed yesterday".

If they really wanted to be anal about it and check it every hour, you could even set up set of Dashboard Reports for My xxx 's changes today. Then they could check it as often as they want.  Or you can even get a Dashboard to auto refresh, but that is a topic for another question. 
Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb
I love it. So brilliant. Thank you!!!
Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb
It looks like the problem with this solution is that our mgmt team wants the notification sent to associates (whose have Contacts affected by an update to a Location or Company record) to list the Contacts affected. So, if a Location address changes and this affects several Trust Role contacts, we want  Associates to get an email listing info in the Contacts table for affected Contacts.

I can create a FORM which lists report of the Associates affected Companies, Locations and Contacts, but FORMS aren't sent for subscription email-types, just reports.

Yet it looks like we can't use notifications (which send forms) without having enough reverse relationships to cover every Associate affected by the change of a Contact's information, and we'd likely need to be able to accommodate 20, at least.

So it seems there's no native solution to the above...does that seem correct?
You can make a field on the Locations table which is equal to 1 if the location was changed yesterday.  Then do a lookup of that field down to the contacts.  So those would be contacts who's locations were changed yesterday and there can be a report subscription for that.

For companies, it can be similar.  Make a  formula field equal to 1 if the company was changed yesterday.  Then look that up down to Locatoms and then down to contacts.  Then again, make a report of contacts who's companies changed yesterday.

Then for both of those reports, you will have a report version for MY contacts with locations chnages yesterday and MY contacts with Compaines changed yesterday.
Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb
Ooh, this looks great. More brilliance. Thank you!
Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb
I have a notification report set to go out to 'all app users' if a record is changed AND if 'I am Associate'(=1). Then when a record is changed the form is triggered which shows all related locations, contacts, etc.

The problem is that the above attempt to limit who gets the email isn't' working -- the email is being sent to all users of the app, whether or not the changed record is associated to their User via the (=1) trick.

Is there a way to use this type of field to only send notifications to users who are related to the changed record?
That is why I suggested a Subscription report to fire overnight.
Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb
Yeah, I figured. Just wanted to make sure I wasn't missing something. Thanks!
Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb
So, I'm going to create all of the reverse relationships required to:

Have an email go to an Associate if a related Contact is edited, or Contact Location or Contact Company.

We need the email that goes out to be a form so we can show embedded tables that explain how the contact is related (to the Associate's Client).

I'm guessing what I'll need to do is create 10 or more reverse-lookup relationships between Contacts and Trust roles, so I can pull the name from the related 'Consultant' assignment. (FYI-We have reverse lookup relationships between Client Assignments and Clients -- so each client record shows who is in the 'Consultant' Assignment role.

So I'm thinking I'll create a bunch of reverse relationships between Contacts and Trust Roles so we can see a list of names (and email addresses) for their related Associates.

Is that all I'll need to do to be able to get notification-forms to be emailed when the Contact Location or Contact Companies are edited? Perhaps I'll need to create 10 or more reverse relationships between those tables, too, huh?

Thank you!