Pulling and Searching Opportunity Contact Roles from a list of multiple opportunities

Question

I have a custom object with two custom connectors - one to opps and one to contacts in order to create many to many relationships to both. To summarize I am trying to be on the main object (Demo) and be able to check for what contacts are already associated with the Demo and what contacts Could/Should be associated with the demo based on related Opportunities' Opportunity Contact Roles.

I have my custom object contact connector going to a list "Chosen Contacts" and my Opps going to a list "DemoOpps" and my available Opportunity Contact roles are also supposed to go to a list "Available contacts". This third step "appears" to be where the breakdown occurs. I am getting an error of Compile Error: Initial term of field expression must be a concrete SObject: LIST at line 74 column 119.

The is where my string parameter comes into play. I think the issue is that it is saying "WHERE OpportunityID = demoopps.id" because demoopps.id could have multiple ids because it is a list but I know this must be possible somehow...surely you can tell it to find all the opportunity contact roles for multiple opportunities.

Any help would be greatly appreciated.

Thanks,

Amanda

public with sharing class DemoContactEntryExtension {


public Demo__c theDemo {get;set;} { theDemo = new Demo__c();}
public String searchString {get;set;}
public Demo_Contact__c[] chosencontacts {get;set;} { chosencontacts = new List<Demo_Contact__c>(); }
public Opportunity[] demoopps {get;set;} { demoopps = new List<Opportunity>(); }
public Demo_Opportunity__c theDemoOpp {get;set;}  { theDemoOpp = new Demo_Opportunity__c(); }
public OpportunityContactRole[] availablecontacts {get;set;} { availableContacts = new List<OpportunityContactRole>(); }

public String toSelect {get; set;}
public String toUnselect {get; set;}
public Decimal Total {get;set;}

public Boolean overLimit {get;set;}

private Demo_Contact__c[] forDeletion = new Demo_Contact__c[]{};


public DemoContactEntryExtension(ApexPages.StandardsetController controller) {

    Id demoId = ApexPages.currentPage().getParameters().get('id');

    // Get information about the Demo being worked on
    theDemo = [select Id from Demo__c where Id = :demoId limit 1];

    //show associated Demo Opp
    theDemoOpp = [select Id, Opportunity__c, Demo__c from Demo_Opportunity__c where Demo__c = :demoId];



    // If demo contacts were previously selected need to put them in the "selected contacts" section to start with
    chosencontacts = [
        select Id, Contact__c, Demo__c
        from Demo_Contact__c
        where Demo__c = :demoID
    ];

    // Grab associated opportunities
    demoopps = [
        select Id, Name
        from Opportunity
        where ID = :thedemoopp.Opportunity__c
    ];

    updateAvailableList();



}

// this is the 'action' method on the page
public PageReference OppCheck(){
    //if there is only one Opp we go with it and save the demo
        if(theDemoOpp.Demo__c != theDemo.Id ){
            try{
                theDemoOpp.Demo__c=theDemo.Id;
                update(theDemoOpp);
            }
            catch(Exception e){
                ApexPages.addMessages(e);
            }
        }

        return null;

}



public void updateAvailableList() {

    // We dynamically build a query string and exclude items already in the shopping cart
    String qString = 'select Id, ContactId, OpportunityId from OpportunityContactRole where OpportunityId = \'' + demoOpps.Id + '\'';

    // note that we are looking for the search string entered by the user in the First Name OR Last Name
    // modify this to search other fields if desired
    if(searchString!=null){
        qString+= ' and (OpportunityContactRole.Contact.FirstName like \'%' + searchString + '%\' or OpportunityContactRole.Contact.LastName like \'%' + searchString + '%\')';
    }

    Set<Id> selectedEntries = new Set<Id>();
    for(Demo_Contact__c d:chosencontacts){
        selectedEntries.add(d.contact__c);
    }

    if(selectedEntries.size()>0){
        qString += ' and ContactId not in :selectedEntries';

        String tempFilter = ' and Id not in (';

        for(Id i : selectedEntries){

            tempFilter+= '\'' + (String)i + '\',';
        }

        String extraFilter = tempFilter.substring(0,tempFilter.length()-1);
        extraFilter+= ')';

        qString+= extraFilter;*/
    }

    qString+= ' order by OpportunityContactRole.Contact.Name';
    qString+= ' limit 101';

    system.debug('qString:' +qString);        
    availablecontacts = database.query(qString);

    // We only display up to 100 results... if there are more than we let the user know (see vf page)

    if(availablecontacts.size()==101){

        availablecontacts.remove(100);
        overLimit = true;
    }else{
        overLimit=false;
    }
}

public void addTochosencontacts(){

    // This function runs when a user hits "select" button next to a contact

    for(opportunitycontactrole d : availablecontacts){
        if((String)d.Id==toSelect){
            chosencontacts.add(new Demo_Contact__c(Demo__c=thedemoopp.demo__c, contact__c=d.contactid));
            break;
        }
    }

    updateAvailableList();  
}


public PageReference removeFromchosencontacts(){

    // This function runs when a user hits "remove" on an item in the "Chosen Contacts" section

    Integer count = 0;

    for(Demo_Contact__c d : chosencontacts){
        if((String)d.contact__c==toUnselect){

            if(d.Id!=null)
                forDeletion.add(d);

            chosencontacts.remove(count);
            break;
        }
        count++;
    }

    updateAvailableList();

    return null;
}

public PageReference onSave(){

    // If previously selected contacts are now removed, we need to delete them
    if(forDeletion.size()>0)
        delete(forDeletion);

    // Previously selected contacts may have new information, so we use upsert here
    try{
        if (chosencontacts.size() > 0) upsert(chosencontacts);       

    } catch(Exception e){

        ApexPages.addMessages(e);
        return null;
   }

    // After save return the user to the Opportunity
    return new PageReference('/' + ApexPages.currentPage().getParameters().get('Id'));
}

public PageReference onCancel(){

    // If user hits cancel we commit no changes and return them to the Opportunity   
    return new PageReference('/' + ApexPages.currentPage().getParameters().get('Id'));
}


}

Answer 1

It appears that you are only expecting one Id value. In this case, you can simply pull the first value:

Id demoOppsId = demoOpps.get(0).Id; // or perhaps demoOpps[0].Id;
String qString = 'select Id, ContactId, OpportunityId from OpportunityContactRole where OpportunityId = \'' + demoOppsId + '\'';

Also consider how your code will handle the query for theDemoOpp when demoId is actually present in multiple Demo_Opportunity__c objects.

Answer 2

Building on Hraefn's answer:

You've defined demoopps as Opportunity[], but in your SOQL use it as if it were a single Opportunity. If you want a single entry, then Hraefn's answer is a starting point, but if you want to select all of them you can bind the entire list (formatted for easy reading; should be all on one line):

String qString = 'select Id, ContactId, OpportunityId 
                  from OpportunityContactRole 
                  where OpportunityId in :demoopps';

See this other answer for more details.