function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Marco PinderMarco Pinder 

Consolidate Contact Roles on the Contact object

I am investigating a way to identify the last time a contact in our org was last contacted or used within the system. This includes being added to a campaign as a campaign member, having an activity on the record (task or event), being sent an external email from email marketing software and being added as a contact role on an opportunity, a contract, etc.

Now, I have been able to handle for all the above events EXCEPT for when a contact is added as a contact role. It seems that there is no way to display this information on the contact record or reference the date they were added as a contact role in a formula of any sort. I know I can run a report, but this is not an option for 350,000 contacts!

The reason behind this is basically for data protection, and not wanting to hold a contact record for longer than necessary.

I fear that I could get to a situation where I deactivate and then ultimately delete a contact prematurely when they had been added as a contact role to an important opportunity only last week!

Does anyone know of anyway that I can consolidate all contact roles a contact may have across any object and display this in some way on the contact record?

Thanks in advance,

Marco
Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox
global class TrackContactUpdates implements Database.Batchable<Sobject> {
    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator('select id from contact' + (Test.isRunningTest()?' order by createddate desc limit 1':''));
    }
    
    global void execute(Database.BatchableContext bc,List<Contact> contacts) {
        Map<id,Contact> contactRecords = new Map<Id,Contact>(contacts);
        for(Contact c:[select id,
                        (select id,createddate from contractcontactroles order by createddate desc limit 1),
                        (select id,createddate from opportunitycontactroles order by createddate desc limit 1),
                        (select id,createddate from accountcontactroles order by createddate desc limit 1)
                       from contact where id in :contacts]) {
            contactRecords.get(c.id).last_contract_role_date__c = c.contractcontactroles.isempty()?null:c.contractcontactroles[0].createddate;
            contactRecords.get(c.id).last_opportunity_role_date__c = c.opportunitycontactroles.isempty()?null:c.opportunitycontactroles[0].createddate;
            contactRecords.get(c.id).last_account_role_date__c = c.accountcontactroles.isempty()?null:c.accountcontactroles[0].createddate;
        }
        update contactRecords.values();
    }
    
    global void finish(Database.BatchableContext bc) {
    
    }
    
    private static testMethod void test() {
        TrackContactUpdates t = new TrackContactUpdates();
        Account a = new Account(Name='Test');
        insert a;
        Contact c = new Contact(Firstname='Test',LastName='Test',AccountId=a.Id);
        insert c;
        Opportunity o = new Opportunity(Name='Test',CloseDate=System.today(),amount=1.00,stagename='Closed Won',AccountId=a.id);
        insert o;
        Contract ct = new Contract(Name='Test',AccountId=a.id,Status='Draft');
        insert ct;
        AccountContactRole acr = new AccountContactRole(Role='Vendor',ContactId=c.id,AccountId=a.id,isprimary=false);
        insert acr;
        acr = [select id,createddate from accountcontactrole where id = :acr.id];
        OpportunityContactRole ocr = new OpportunityContactRole(Role='Vendor',ContactId=c.id,OpportunityId=o.id,isprimary=false);
        insert ocr;
        ocr = [select id,createddate from opportunitycontactrole where id = :ocr.id];
        ContractContactRole ccr = new ContractContactRole(Role='Vendor',ContactId=c.id,ContractId=ct.id);
        insert ccr;
        ccr = [select id,createddate from contractcontactrole where id = :ccr.id];
        Test.startTest();
        TrackContactUpdates tcu = new TrackContactUpdates();
        Database.executeBatch(tcu);
        Test.stopTest();
        c = [select id,last_contract_role_Date__c,last_account_role_date__c,last_opportunity_role_date__c from contact where id = :c.id];
        system.assertEquals(ccr.createddate,c.last_contract_role_Date__c);
        system.assertEquals(ocr.createddate,c.last_opportunity_role_Date__c);
        system.assertEquals(acr.createddate,c.last_account_role_Date__c);
    }
}

The error was caused by the delay in time from the start of the test to the end of the test (which is something that I personally did not have a problem with, since my organization has very little code). I just modified the code so that it now checks the creation date directly instead of trying to save a few queries. Try this and let me know what you think.

All Answers

sfdcfoxsfdcfox

You could make a VF page that's embedded on the contact's page layout, or you could write some Batch Apex Code to update a set of custom fields on the contact to keep track of the various dates, with a weekly or monthly run time, depending on the granularity you need. You could even have the Batch Apex delete qualifying contacts during the batch process if they qualify for deletion, perhaps with a flag that could be set one iteration before deletion (to give a chance to recover the record). I personally like the second option for reporting purposes. It makes it easy to figure out what's going on, such as the average age since last contact (as defined by your parameters), etc.

Marco PinderMarco Pinder

Thank you for your reply, however my coding skills are extremely limited and it is not an issue we would get a developer to address.

 

Would a trigger on each of the Contract and Opportunity objects solve this issue? I have written a trigger previously so could potentially go down that route.

 

Exploring the DataLoader has uncovered the 'ContractContactRole' and 'OpportunityContactRole' objects, however it doesn't appear that I can place a trigger on these, which seems like the ideal place as they reference the ContactId.

 

If you ,or anyone else, has further suggestions I would really appreciate your input.

 

Thanks very much,

 

Marco

sfdcfoxsfdcfox

I took the liberty of writing up a demonstration, both for you and for others that may one day look for a similar solution.

 

This batch setup can be scheduled to run once a week (Setup > Develop > Apex Classes > Schedule). You need two classes: one to run the scheduler, and one to actually perform the batch updates.

 

Here's the solution I came up with:

 

global class TrackContactUpdates implements Database.Batchable<Sobject> {
    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator('select id from contact' + (Test.isRunningTest()?' order by createddate desc limit 1':''));
    }
    
    global void execute(Database.BatchableContext bc,List<Contact> contacts) {
        Map<id,Contact> contactRecords = new Map<Id,Contact>(contacts);
        for(Contact c:[select id,
                        (select id,createddate from contractcontactroles order by createddate desc limit 1),
                        (select id,createddate from opportunitycontactroles order by createddate desc limit 1),
                        (select id,createddate from accountcontactroles order by createddate desc limit 1)
                       from contact where id in :contacts]) {
            contactRecords.get(c.id).latest_contract_role_date__c = c.contractcontactroles.isempty()?null:c.contractcontactroles[0].createddate;
            contactRecords.get(c.id).latest_opportunity_role_date__c = c.opportunitycontactroles.isempty()?null:c.opportunitycontactroles[0].createddate;
            contactRecords.get(c.id).latest_account_role_date__c = c.accountcontactroles.isempty()?null:c.accountcontactroles[0].createddate;
        }
        update contactRecords.values();
    }
    
    global void finish(Database.BatchableContext bc) {
    
    }
    
    private static testMethod void test() {
        TrackContactUpdates t = new TrackContactUpdates();
        Account a = new Account(Name='Test');
        insert a;
        Contact c = new Contact(Firstname='Test',LastName='Test',AccountId=a.Id);
        insert c;
        Opportunity o = new Opportunity(Name='Test',CloseDate=System.today(),amount=1.00,stagename='Closed Won',AccountId=a.id);
        insert o;
        Contract ct = new Contract(Name='Test',AccountId=a.id,Status='Draft');
        insert ct;
        AccountContactRole acr = new AccountContactRole(Role='Vendor',ContactId=c.id,AccountId=a.id,isprimary=false);
        insert acr;
        OpportunityContactRole ocr = new OpportunityContactRole(Role='Vendor',ContactId=c.id,OpportunityId=o.id,isprimary=false);
        insert ocr;
        ContractContactRole ccr = new ContractContactRole(Role='Vendor',ContactId=c.id,ContractId=ct.id);
        insert ccr;
        Test.startTest();
        TrackContactUpdates tcu = new TrackContactUpdates();
        Database.executeBatch(tcu);
        Test.stopTest();
        c = [select id,firstname,lastname,latest_contract_role_Date__c,latest_account_role_date__c,latest_opportunity_role_date__c from contact where id = :c.id];
        system.assertEquals(system.now(),c.latest_contract_role_Date__c);
        system.assertEquals(system.now(),c.latest_opportunity_role_Date__c);
        system.assertEquals(system.now(),c.latest_account_role_Date__c);
    }
}

 

global class ScheduleTrackContact implements Schedulable {
    global void execute(SchedulableContext sc) {
        TrackContactUpdates tcu = new TrackContactUpdates();
        Database.executeBatch(tcu);
    }
    
    private static testMethod void test() {
        ScheduleTrackContact stc = new ScheduleTrackContact();
        Test.startTest();
        System.Schedule('test','0 0 0 ? * 1',stc);
        Test.stopTest();
    }
}

Once you have these two classes in place, just schedule the classes, and let the system do all the work. You'll need three custom contact fields: Last Account Role Date, Last Contract Role Date, and Last Opportunity Role Date, all of which should be date/time fields and made read-only from the page layout. It will cover all 350,000 contacts in your database every week and give them the latest timestamp of the last roles added to them.

 

Let me know if you have any further questions.

Marco PinderMarco Pinder

Wow, thank you very much for this. It looks great and I am excited to try it. I had to do a little editing as some erroneous characters had made their way into the code (so I placed into Notepad and removed them). I also had to change the API names from "latest" to "last" to match the custom fields I created on the contact object. The classes now save OK.

 

I am currently waiting for a scheduled  run in our sandbox environment, however the test code only covers 21% and 23% respectively.

 

To resolve this issue before pushing to production, do I need to include other test criteria to handle for the mandatory fields we have on the contact object in order to get at least 75% coverage?

 

You have been a huge help so far and for my first visit to these Developer boards I am extremely impressed. What a great community this is.

 

Thanks,

 

Marco

 

Marco PinderMarco Pinder

I just wanted to elaborate a bit more on my experience with this code.

 

First of all, the batch ran (it took approximately 3 hours) and it worked like a charm. Thank you so much!

 

Regarding test run, I read the original error where the test was failing and it was due to a validation rule on the contract object where we require a Sales Region on a custom field. I included this is the test class when declaring the contract fields and it moves along a little further. Code coverage is now 24% rather than 21% and the latest error is:

 

Test Result

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Do you have any idea of how I could resolve this please? It looks as though the external entry point is referring to the line of code: "system.assertEquals(system.now(),c.last_contract_role_Date__c);"

 

Thanks,

 

Marco

sfdcfoxsfdcfox
global class TrackContactUpdates implements Database.Batchable<Sobject> {
    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator('select id from contact' + (Test.isRunningTest()?' order by createddate desc limit 1':''));
    }
    
    global void execute(Database.BatchableContext bc,List<Contact> contacts) {
        Map<id,Contact> contactRecords = new Map<Id,Contact>(contacts);
        for(Contact c:[select id,
                        (select id,createddate from contractcontactroles order by createddate desc limit 1),
                        (select id,createddate from opportunitycontactroles order by createddate desc limit 1),
                        (select id,createddate from accountcontactroles order by createddate desc limit 1)
                       from contact where id in :contacts]) {
            contactRecords.get(c.id).last_contract_role_date__c = c.contractcontactroles.isempty()?null:c.contractcontactroles[0].createddate;
            contactRecords.get(c.id).last_opportunity_role_date__c = c.opportunitycontactroles.isempty()?null:c.opportunitycontactroles[0].createddate;
            contactRecords.get(c.id).last_account_role_date__c = c.accountcontactroles.isempty()?null:c.accountcontactroles[0].createddate;
        }
        update contactRecords.values();
    }
    
    global void finish(Database.BatchableContext bc) {
    
    }
    
    private static testMethod void test() {
        TrackContactUpdates t = new TrackContactUpdates();
        Account a = new Account(Name='Test');
        insert a;
        Contact c = new Contact(Firstname='Test',LastName='Test',AccountId=a.Id);
        insert c;
        Opportunity o = new Opportunity(Name='Test',CloseDate=System.today(),amount=1.00,stagename='Closed Won',AccountId=a.id);
        insert o;
        Contract ct = new Contract(Name='Test',AccountId=a.id,Status='Draft');
        insert ct;
        AccountContactRole acr = new AccountContactRole(Role='Vendor',ContactId=c.id,AccountId=a.id,isprimary=false);
        insert acr;
        acr = [select id,createddate from accountcontactrole where id = :acr.id];
        OpportunityContactRole ocr = new OpportunityContactRole(Role='Vendor',ContactId=c.id,OpportunityId=o.id,isprimary=false);
        insert ocr;
        ocr = [select id,createddate from opportunitycontactrole where id = :ocr.id];
        ContractContactRole ccr = new ContractContactRole(Role='Vendor',ContactId=c.id,ContractId=ct.id);
        insert ccr;
        ccr = [select id,createddate from contractcontactrole where id = :ccr.id];
        Test.startTest();
        TrackContactUpdates tcu = new TrackContactUpdates();
        Database.executeBatch(tcu);
        Test.stopTest();
        c = [select id,last_contract_role_Date__c,last_account_role_date__c,last_opportunity_role_date__c from contact where id = :c.id];
        system.assertEquals(ccr.createddate,c.last_contract_role_Date__c);
        system.assertEquals(ocr.createddate,c.last_opportunity_role_Date__c);
        system.assertEquals(acr.createddate,c.last_account_role_Date__c);
    }
}

The error was caused by the delay in time from the start of the test to the end of the test (which is something that I personally did not have a problem with, since my organization has very little code). I just modified the code so that it now checks the creation date directly instead of trying to save a few queries. Try this and let me know what you think.

This was selected as the best answer
NikiVankerkNikiVankerk

Here is a simple VF page that displays all the Account Contact Roles for a specific Contact - it can be added as an inline VF page to the Contact layout for display purposes.  This version works for PE orgs since it is just a flat list - if you wanted to add Edit/Del capabilities like it has on the Account's view of the Contact Roles then you need to add an extension and it only works for EE and above.

 

<apex:page standardController="Contact">
  <apex:form >

    <apex:pageBlock >
        <apex:pageBlockSection collapsible="false" columns="1">
          <apex:pageBlockTable value="{!Contact.accountcontactroles}" var="r">
              
              <apex:column headerValue="Property Name">
                  <apex:commandLink value="{!r.account.name}" action="/{!r.accountid}" target="_top"/>
              </apex:column>
              <apex:column headerValue="Role">
                  <apex:outputField value="{!r.role}"/>
              </apex:column>
              <apex:column headerValue="Primary?">
                  <apex:outputField value="{!r.isprimary}"/>
              </apex:column>              

          </apex:pageBlockTable>
        </apex:pageBlockSection>
    </apex:pageBlock>
    
  </apex:form >
</apex:page>

 

To use this:

  1. Create this VF page and add security for all user profiles that will access it
  2. Edit the Contact layout to create a section with 1 column, possibly name the section 'Contact Roles' and display header
  3. Drag in the VisualForce page created above, edit settings to display scroll bar for when it gets longer than the allocated height.