function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MVJMVJ 

SOQL Questions - Using In Clause

In trying to keep within the governors limits for APEX I am trying to limit the number of records that get returned in a SOQL Query.

I need to find Opportunities that DO NOT have a Contact role tied it when the opportunity moves into a specific stage.

Since there is a limit to the number of records that can be returned within an APEX Trigger I wanted to only get the Opportunity Id's that do not have the contact role.

To do this on one offs is easy.  The problem is trying to write the routine for bulk updates/inserts.

I want to get just the unique Id's that have at least on Contact Role defined.  I need the following Query to get that list.

I tried the following Query on the Apex Explorer and get a Malformed Query error.  Is does not like the Select in the In Clause.

SELECT Id from Opportunity Where Id in (Select OpportunityId from OpportunityContactRole where OpportunityId in ('006T0000002FSIv','006T0000003LFwJ'))

In the example that I have above I expected to get back on Opty Id.  Of the two listed on one of them has a contact linked to it.

I am just trying to test out my theory before I start writing code that may not be possible.  If I understand the limits I can only return 1,000 records in the resulting Query.  If I make the call and pull back all the records from the OportunityContactRole it is possible that I can exceed the 1,000 record limit.  If I get the query above to work that will make me return at most the number of records that are in the Trigger.

Thanks in advance for any help.
 
MVJMVJ
All I solved the problem above with the following code and a workflow rule.  It works just fine and handles Bulk inserts and updates.

Code:
    public static void optyContactRoleCheck(Opportunity[] Optys){
        Map<Id, Opportunity> optyMap = new Map<Id,Opportunity>();
        Map<Id, OpportunityContactRole> optyWithContactMap = new Map<Id, OpportunityContactRole>();
        Map<integer, Opportunity> optyWithOutContact = new Map<integer,Opportunity>();
        boolean bCreateActivity = false;
        integer iOptysWithOutContact = 0;
        
        for (Integer x=0; x < Optys.size() ;x++){
            optyMap.put(Optys[x].Id, Optys[x]);
        }
        
        for (OpportunityContactRole Ocr : 
             [Select Id, OpportunityId From OpportunityContactRole
              Where OpportunityId in :OptyMap.KeySet()]){
            
            if (!optyWithContactMap.containskey(Ocr.OpportunityId)){
                optyWithContactMap.put(Ocr.OpportunityId, Ocr);
            } //end if      
        }   // end for              
        
        // Go thru the Optys to flag the ones without a contact
        iOptysWithOutContact=0;
        for (Integer x=0; x < Optys.size() ;x++){
            if ((Optys[x].stagename == '6. Negotiation & Closing'
                 || Optys[x].stagename == '7. Delivery & Billing'
                 || Optys[x].stagename == '8. Implemented') 
                 && !OptyWithContactMap.Containskey(Optys[x].Id)
                 && !optys[x].Contact_Role_Activity_Created__c){
                
                // Opty does not have a contact role defined
    // Set the Activity flag to tru so the workflow can pick up
    // the opportunity and create the activity
                optys[x].Contact_Role_Activity_Created__c = true;
                iOptysWithOutContact++;
            }
        }
        
    } // end of optyContactRoleCheck

 
sfdev1sfdev1
How you fire off this code with a Workflow rule?
MVJMVJ

The APEX Code above identifies the records that do not have an contact related to the opportunity.  It sets a flag onthe opportunity record to true.

A workflow rule looks for records that are created or updated and did not previously met the cobdition where the flag is true then creates an activity assigned to the opportunity.

Since workflow rules fire after APEX code this works just well.