How to Trigger a Contact and Account Updates when Email is Archived

Hi Bud Hartley,

Can you clarify what formula you are using for the Last Email field on either the Accounts or Contacts module? I'd like to reproduce the scenario and see if there are any recommendations I can provide to alleviate the issue.

Kind regards,

Chris

Hi Chris,  I';m sorry for the slow response - I've been out.

This is what I use for the Calculated Field: maxRelatedDate($archived_emails,"date_modified")

Hi Bud Hartley

Thanks for sharing the formula. I was able to reproduce the behavior described and I am reasonably confident this is due to the relationship dynamic I described in your prior thread. When using the email archiving email address, new emails created in Sugar are not explicitly related to contacts and accounts. They only carry an implicit relationship due to the association by email address. Since there is no explicit relationship formed at the database level there is no actionable event to trigger Sugar to recalculate the formula on its own. 

However, when you go to the record itself and manually save it, the formula will detect the implicit relationships and return the latest email date modified per your formula (side note: I recommend using date_sent here rather than date_modified since date_modified could be misleading as to when your last actual communication with a contact was).

The implicit nature of the relationship can be best seen by changing the email address on a record temporarily. If you change the email address, the calculated field will return the latest date of only the explicitly related records. Once you change the email address back to the proper value, it will then reference the explicitly and implicitly related records and return the latest date of the two sets. In my test, I had an account with an explicit email from October 27, 2022 and an implicit email from December 20, 2022. When I removed the email address from the account, the calculated field returned October 27, 2022, and when I added the email address back to the account, the field reported December 20, 2022.

The only viable solution I can think of in this use case to handle implicitly related emails would require custom code in the form of a scheduler. The scheduler would look at all emails created since the scheduler last ran and recalculate the last activity date for any records with an email address matching at least one address on the email. I understand you do not want to pursue custom solutions, so perhaps someone has a more creative way to achieve this in an automated, no-code fashion. 

Thanks Chris,

Since we're in the On-Demand Environment custom coding isn't an option.  I'm disappointed that the "last-activity-dates" add-on isn't possible - it looked like a good solution.

Thanks for the suggestion about the date_sent...

I appreciate your assistance and insight on this.  Best wishes for the new year.

Thanks Chris,

Since we're in the On-Demand Environment custom coding isn't an option.  I'm disappointed that the "last-activity-dates" add-on isn't possible - it looked like a good solution.

Thanks for the suggestion about the date_sent...

I appreciate your assistance and insight on this.  Best wishes for the new year.

Hi Bud Hartley,

One point of clarification is that custom coding is possible in the SugarCloud environment. We have a number of customers where we build, install, and maintain custom packages in SugarCloud. I always took your statement to mean it was a business decision to not pursue custom-coded solutions rather than a limitation. If you are open to a potential custom solution, please let me know and we can put together a proposal for you.

Chris