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

Hey Bud, we have an addon that does exactly that www.sugaroutfitters.com/.../last-activity-dates hope this helps

Hey Bud, we have an addon that does exactly that www.sugaroutfitters.com/.../last-activity-dates hope this helps

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")

I signed up for the 30 day trial, but it looks like the Sugar folks aren't happy with the package in the On-Demand environment.  Here's the message when I try to upload the package:

Installation failed!

The package you are attempting to install does not conform to the policies established within the Sugar Open Cloud or by your system administrator.

• Sugar Open Cloud customers must obtain a new package from the package provider that addresses the issues described below.
• If you are running Sugar locally, you can relax your Module Loader restrictions to allow the package to be installed.

Package Scan Policy Guide

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.

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

UPDATE ON THE PACKAGE:

The folks at http://www.enable.services updated the package to Release 1.4.1 and the Upload and Installation was seamless!

I am starting my testing today, and I'll post the results soon - Initially it looks like it does what I am seeking, and the $299 USD / year price is acceptable if it does what it should do.

Update on the www.sugaroutfitters.com/.../last-activity-dates package installation.

It's been an interesting 12 days since the installation was successful.  The package started populating the new fields, and I selected the option on the Admin page to "populate missing dates".  Many of the account and contact records were updated, but it was disturbing that there were thousands of account and contact records that were not updated.  Worse, the "Last Activity" field was sometimes not updated even though there was a date in the Last Email or Last Call field.

I created simple reports for accounts and contacts that filtered records where my manually created field had a date for the "Last Email with ____" and the Last Email field from the add-on was empty.  There are thousands of records it those reports.

I've been in communication with the add-on developers support team in the UK.  With the holidays there hasn't been very much activity.  My trial period expires in a couple of weeks and it's likely I'll abandon the project before that date if there's no resolution.