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

I posted a question a few days ago about updating "Last Call" and "Last Email" fields that use the "maxRelatedDate" in Contact and Account records.  Chris Raffle suggested it may have been a setting in the Config file, but as it works out, that wasn't the problem.

After many hours of investigation and testing, I have FINALLY sorted out the issue.

The "Last Call with Contact" field in the Contacts Module, and the "Last Call with Account" field in the Accounts Module are automatically updated because the creation of a Call Record triggers an update of the Contact and Account records (as Chris Raffle pointed out).

Unfortunately, the Archiving of an Email (with the BCC to the Email Archiving address) doesn't trigger a record update!  Therefore the "Last Email with ___" field doesn't  get updated :-(

I've been trying to create a process that would trigger a Contact or Account update when an Email is archived without success. I spent time this weekend doing mass no-op updates of Contact and Account records to update the Last Email field, but I'd like to find a way to automate the updates.

Any ideas or suggestions of things I should try?

Parents
  • Hi ,

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

    Bud Hartley | Cape Foulwind, NZ (and Oregon, USA)

  • Hi 

    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.

    Bud Hartley | Cape Foulwind, NZ (and Oregon, USA)

  • Hi ,

    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

Reply Children
No Data