What’s super useful for account maintenance is last contact by Account. So basically that the account captures the date of last call/email/meeting for this account, opportunity->account, quote->account, contact->account.
I’ve done this before were I do a before save hook on the activities to update the parent account when the activity is saved.
Pros/Cons:
Pro: It’s not super server intensive
Cons: It’s kind of cumbersom because you have to implement in each activity module, also, it doesn’t go back in time, so it’s only relevant after some time.
Option 2:
I’m considering doing a scheduled job to loop through the accounts and look for related calls/meetings/emails sent to the account and then updating the acccount with the most recent activity date.
Pros: once run it give a good picture immediately
Cons: I’m thinking this will be super server intensive, and it will only be accurate once the job runs, unless I do both option #1 and option #2 combined.
I’m just in the planning stages and looking for ideas. Anyone ever done this? Tackle it differently? I think #2 is really the best option, just concerned about server load if I run it other than 2am in the morning.
(i think) It’s a dirty hack, But why not use a sql event on timer or a trigger on new entry to whichever module is providing the accounts with that information?
f.ex what i’ve done recently - update table based on the LAST (max) c2.date_entered value - so one person may have multiple call records but it’s only picking the next_call value based on last date entered.
It’s quick and dirty (i know) but the mysql server is running this every 30 seconds and i’ve barely noticed a difference
I may be entirely wrong with this methodology but it works 
Update Contacts with Next Call Date from Last Call
INNER JOIN contacts cn ON cstm.id_c = cn.id
INNER JOIN calls_contacts_1_c c1c ON cn.id = c1c.calls_contacts_1contacts_idb
INNER JOIN calls_cstm cc ON c1c.calls_contacts_1calls_ida = cc.id_c
INNER JOIN calls c ON cc.id_c = c.id
SET cstm.next_call_date_c = cc.next_call_c
WHERE c.date_entered = (
SELECT MAX(c2.date_entered)
FROM calls c2
INNER JOIN calls_contacts_1_c c1c2 ON c2.id = c1c2.calls_contacts_1calls_ida
WHERE c1c.calls_contacts_1contacts_idb = c1c2.calls_contacts_1contacts_idb
)
AND cc.next_call_c > '2024-04-23'
event creation query below
DROP EVENT IF EXISTS `Update Contacts with Next Call Date from Last Call`;
CREATE DEFINER=`HIDDEN`@`%`
EVENT `Update Contacts with Next Call Date from Last Call`
ON SCHEDULE EVERY 30 SECOND
STARTS '2024-04-26 08:28:50'
ON COMPLETION NOT PRESERVE ENABLE DO
UPDATE
contacts_cstm cstm
INNER JOIN contacts cn ON cstm.id_c = cn.id
INNER JOIN calls_contacts_1_c c1c ON cn.id = c1c.calls_contacts_1contacts_idb
INNER JOIN calls_cstm cc ON c1c.calls_contacts_1calls_ida = cc.id_c
INNER JOIN calls c ON cc.id_c = c.id SET cstm.next_call_date_c = cc.next_call_c
WHERE c.date_entered = ( SELECT MAX(c2.date_entered) FROM calls c2 INNER JOIN calls_contacts_1_c c1c2 ON c2.id = c1c2.calls_contacts_1calls_ida WHERE c1c.calls_contacts_1contacts_idb = c1c2.calls_contacts_1contacts_idb )
AND cc.next_call_c > '2024-04-23'
2 Likes
Maybe this could even be done without setting up the data beforehand.
If you create a subpanel with custom SQL you can make the query grab that latest interaction from related tables, I guess.
1 Like
Ideally, I don’t want to just see it in the Account, but be able to report on accounts with no contact in 6 months, and also drive workflows like: “A” Account contact monthly, “B” Account contact quarterly, “C” account bi-annually, etc. So I can just create scheduled follow up calls for the reps.
1 Like
As long as the scheduled length of time between follow up calls is greater than the same day, and it sounds like it is, then running the update to all Accounts most recent contact date, after hours, late a night, every night, and at that time it would create each scheduled follow up call for the assigned rep, this should work and give you correct results. Because a new periodic follow up call is always more far off in the future than tonight at midnight.
Thanks @chris001 hadn’t thought that far ahead yet other than I wanted to schedule calls. I can’t limit them to once per record, because I want the workflow to trigger anytime the account hasn’t been contacted in X length of time. I could easily get in a situation where every time it runs I’m scheduling new calls becuase the condition will always be true until the call is “held”. I’ll also have calculate “date of next contact” as a way of limiting call creation by the workflow to only Accounts that don’t already have a call scheduled within the timeframe.
What about do the same algorithm as Suite 8. For each account, opportunity, quote, contact, it looks at dates of last contact and comes up with
Account: AVERAGE WON OPPORTUNITY PER YEAR: $0 TOTAL OPPORTUNITY VALUE: $75,000 - the timeline on the sidebar shows date of last contact.
Opportunity: THIS OPPORTUNITY HAS BEEN IN THIS SALES STAGE FOR: 1 DAY(S),
Contact: YOUR LAST INTERACTION WAS AN EMAIL ON :2024-05-04 21:00
Quote: n/a - would be good to add.
Invoice: n/a - same.
Thanks @Chris I thought about that but its not storing it. It just calculates when you load the record. Im assuming it just grabbing from the activity history that gets loaded when the record loads. It wouldn’t be yoo difficult to jump on that, but I really want to store the value for all accounts so I can run reports and workflows bases on it. Im currently working on some sql queries that run in the sceduler. Im alost there.