SalesForce: Linking an custom field and account activity data

Question

I am slowly getting the hang of salesforce and I am trying to get some fields linking up between accounts and opportunities and more.

My current issue is with Account Activity details. I have a set of accounts which show data and details connected to the account such as:

Account Owner Account Name Type Website Phone number and more...

In this account we have a feed of activity which give details of phone calls, meetings or emails which link to the account. Each event within the account activity has a Type field which is either: Meeting, Call or Email.

What I am trying to do is create a Custom Field within the main account which looks at the account activity, and displays the date of the last meeting, so in terms of sudo code it would be something like:

SELECT DueDate WHERE type=Meeting and DueDate <= TODAY()

Or something along these lines (I know thats basically SQL).

If anyone could help that would be brilliant, specially by 5pm as my boss said he would buy me a pint if I get it figured out!

Thanks all.

---------- EDIT ---------

I am trying something like this:

CASE(TEXT(Activity.Type), Meeting, TODAY - LastActivityDate)

when creating a custome formula field, so im trying to get to the Event field called Type by doing:

Activity.Type

This does not work but it was a guess which was taken from LastActivityDate. I may be going about this all wrong, so anything will help. cheers!

---------- EDIT ---------

This is an image of the field that I am trying to access, I cannot seem to find any way to accessing this Type field

I don't think there's a way to do this through just one custom formula field. You would need some code, specifically a Trigger on the Event object that writes to a custom field on the Account (Last_Event_Type__c for example) whenever an Event is inserted or updated. Using that custom field, you could do a formula comparison as you were trying. Something like: CASE(TEXT(Last_Event_Type__c), Meeting, TODAY - LastActivityDate) — Matt K
Thanks for this @MatthewKeefe, so where would I put code like this, would it go in a custom formula field in the account or in the event? — lukehillonline
@MatthewKeefe I have just tried your code and it does not recognise Last_Event_Type__c or Last_Event_Type as valid. any other ideas? — lukehillonline
Last_Event_Type__c should be a custom (text) field, you would need to create it. — Matt K
yeah during my playing about today I tried that and still had no luck, I am trying all sorts at the moment, and failing on everything! ha — lukehillonline

Kirill Yunussov Kirill Yunussov · Accepted Answer · 2012-04-25T18:42:06

You cannot do that with a formula, and you can't really use LastActivityDate field, because it tracks ALL types of events, not just "Meeting".

One way to accomplish this is to create an after-insert/after-update trigger on the Event object, which would update the "Last_Meeting_Date__c" field on the Account (you will need to create that field).

Here is the code:

trigger EventUpdateAccountMeetingDate on Event (after insert, after update) {
        Map<String,Date> acctToDate = new Map<String,Date>();
    for(Event e : Trigger.new){
        if(e.Type=='Meeting' && e.AccountId!=null && (!acctToDate.containsKey(e.AccountId) || acctToDate.get(e.AccountId) < e.activityDate)){
                acctToDate.put(e.accountId, e.activityDate);
        }
    } 
    if(acctToDate.size()>0){
        List<Account> accts = [select id, Last_Meeting_Date__c from Account where id in :acctToDate.keyset()];
        List<Account> acctsToUpdate = new List<Account>();
        for(Account a : accts){
            if(a.Last_Meeting_Date__c==null || a.Last_Meeting_Date__c<acctToDate.get(a.id)){
                a.Last_Meeting_Date__c = acctToDate.get(a.id);
                acctsToUpdate.add(a);
            }
        }
        if(acctsToUpdate.size()>0){
            update acctsToUpdate;
        }
    }
}

I didn't try to deploy it, so not sure if it will ask you to increase code coverage for this trigger. You may have to create a test class for it.

SalesForce: Linking an custom field and account activity data

1 Answers