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???
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.
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?
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.
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?
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?