+ Start a Discussion
brettnnycbrettnnyc 

Tying all contacts with the same field value as all Opportunities

I have a field (called Job Title) in my Opportunities Object and Contacts object that has the exact same picklist values (Accounting Manager, Controller, etc.) How do I create a list (related list or via a report) of all Contact field values (Job Titles) that match the same Opportunity field value (Job Titles)?

SurekaSureka

Hi,

 

You can create a checkbox field in Opportunity. Make this checkbox field "True" if the Opporutnity picklist feild matches with the Contact picklist feild. Then run a report based on this checkbox feild.

 

Thanks

brettnnycbrettnnyc

Thanks ... I created the checkbox field but I'm having trouble making the Codition Formula work because Title of Position (in the Opportunity) & Job Title Listing (in Contacts) are both multi-select picklist fields. I tried ISPICKVAL ... but I keep getting an error: see below.

 

ISPICKVAL( Title_of_Position__c = Contact_Association__r.Job_Title_Listing__c , 
  TRUE, FALSE)

 

Error: Field Title_of_Position__c is a multi-select picklist field. Multi-select picklist fields are only supported in certain functions.

 

Can you help me out with another pointer?

Thank you,

Brett

 

SurekaSureka

Hi,

 

Since you need to compare multi-select picklist, the comparison cannot be done with the formula field. You need to do it with the help of trigger.

 

Below is the sample Trigger:

 

trigger UpdateCheckbox on Contact_Association__c (before insert, before update) 
{
    List<String> lst_Contact= new List<String>();
    List<String> lst_Opportunity = new List<String>();
    set<String> set_Opportunity = new Set<String>();
    set<String> set_Contact = new Set<String>();
    List<Id> lst_OpportunityId = new List<Id>();
    List<Opportunity> lst_Opportunity = new List<Opportunity>();
    Map<Id,Opportunity> map_Opportunity = new Map<Id,Opportunity>();
    Boolean bool;
    for(Contact_Association__c obj_Contact_Association__c: trigger.new)
    {
        lst_OpportunityId.add(obj_Contact_Association__c.OpportunityId);       
    }
    lst_Opportunity = [select Id,Title_of_Position__c from Opportunity where Id in: lst_OpportunityId];
    for(Opportunity a: lst_Opportunity)
    {
        map_Opportunity.put(a.Id, a);
    }
    
    for(Contact_Association__c obj_Contact_Association__c: trigger.new)
    {
        Opportunity a = map_Opportunity.get(obj_Contact_Association__c.OpportunityId);       
        lst_Contact.clear(); 
        lst_Opportunity.clear();
        set_Contact.clear(); 
        set_Opportunity.clear();
        bool = false;
        if(obj_Contact_Association__c.OpportunityId == a.Id)
        {
            lst_Contact = obj_Contact_Association__c.Job_Title_Listing__c.split(';');
            lst_Opportunity = a.Title_of_Position__c.split(';');
        }      
        set_Contact.addall(lst_Contact);
        set_Opportunity.addall(lst_Opportunity);
        for(String s :set_Contact)
        {
            if(set_Opportunity.contains(s))
            bool = true;
        }
        if(bool == true)
        obj_Contact_Association__c.Targeted__c = true;   
        else
        obj_Contact_Association__c.Targeted__c = false;       

    }
}

 

In the above code, "Targeted" is the checkbox to be updated.

 

 

Hope it helps.

 

Thanks

brettnnycbrettnnyc

Thank you!

I have tried several different ways implementing the code you provided, but my lack of knowledge is killing me. Is there a way we can chat directly to see if I can get this implemented? It's a major milestone to moving forward with connecting all these job applicants/candidates with the potential jobs we have open. I have already been down the route of Force.com Developer support and it's not available in SF they said.

Thank you,
Brett brettsloan at sloanps.com

 

brettnnycbrettnnyc

In the trigger can you please identify the fields that I need to replace with my customized fields?

I assume Contact_Association__C = the same field name as the Job Title within my Candidate object?

What is 1st_OpportunityID.add? Do I replace that with one of my pertinent fields or leave it alone?

 

My current fields are the following:

within Object: Positions I have the field "Position Title" (multi-select picklist)

within Object: Candidates I have the field "Position Title" (multi-select picklist)

 

I'm not sure what other fields are necessary.

 

Thanks again!