1

Need help in identifying the records that needs to be deleted in a custom object: Opportunity_LGT

Background: Opportunity_LGT is a custom object that was created to store few fields of Opportunity standard object for different purposes. When standard Opportunity is created/updated , trigger will create/update Opportunity_LGT. Opportunity_LGT has lookup to Account object and it holds ID of the standard Opportunity record as text field – Opportunity_id_txt.

We recently implemented the delete logic, when opportunity is deleted , corresponding Opportunity_LGT must get deleted.

What is required: I need to delete Opportunity_LGT records which doesn’t have corresponding standard Opportunity record. Oppportunity standard object has 55,000 records and Opportunity_LGT has 55,283 records. I need to delete additional 283 records in Opportunity_LGT records.

Issue:

I have written below code to get those 283 Opportunity_LGT records in an CSV file and send email to me.. I can delete those records via DATALOADER.

When I execute the code on "Open execute anonymous window".. It give error messages – "System.LimitException: Too many query rows: 50001". Because I have more than 50,000 records.. I have spend lot of time on looking at other options.. Looking for a direction here and different approaches.

public class TempDeleteOpplgtwithnoOpp {
Public void getalltodeleterecords()
{

    //*Map<ID, Opportunity> Oppmap = new map<id, Opportunity>([SELECT ID, NAME FROM Opportunity LIMIT 10]);
    Set<id> oppidlist = new Set<id>();

    for(Opportunity opp : [SELECT ID FROM Opportunity])
    {
        oppidlist.add(opp.id);   
    }

   string OpplgtDelNoOppStringFinal = 

    'ID'+','+'Name'+','+'CreatedDate'+','+'LastModifiedDate'+','+'Opportunity_Lgt_Name'+','+' Opportunity_id_txt __c'+'\n';


       List<id> opplgttoDelIdSet = new List<ID>();

       for(Opportunity_LGT__C opplgt : [SELECT ID, Name, CreatedDate, LastModifiedDate, Account_Code__c,
                  Opportunity_Lgt_Name__c, Opportunity_id_txt __c FROM Opportunity_LGT__C] )
       {
           IF (!Oppidlist.contains(opplgt.id)) 
           {                  
               opplgttoDelIdSet.add(opplgt.id);
               String OpplgtDelNoOppString = opplgt.id +','+opplgt.name+','+opplgt.CreatedDate+','+opplgt.LastModifiedDate+','+opplgt.Opportunity_Lgt_Name__c+','+opplgt.Opportunity_Record_Id__c +'\n';
            OpplgtDelNoOppStringFinal = OpplgtDelNoOppStringFinal+ OpplgtDelNoOppString ;   
           }
       }

        system.debug('Opplgtlist.size: ' + Oppidlist.size()); 
        system.debug('opplgttoDelIdSet.size:' + opplgttoDelIdSet.size());

           Messaging.EmailFileAttachment DelOLNoOpp = new Messaging.EmailFileAttachment();
    Blob DelOLNoOppcsvBlob = Blob.valueof(OpplgtDelNoOppStringFinal);
            String DelOLNoOppcsvname = 'DelOLNoOppOL.csv';
            DelOLNoOpp.setFileName(DelOLNoOppcsvname);
            DelOLNoOpp.setBody(DelOLNoOppcsvBlob);


            Messaging.singleEmailMessage ademail = new Messaging.singleEmailMessage();
            List<String> toAddress = new List<String>{'raghavendra.shettigar@inventivHealth.com'};
            ademail.setSubject('OppLightDeleteFiles');
            ademail.setToAddresses(toAddress);
            ademail.setPlainTextBody('Opportunity Light Delete');
            ademail.setFileAttachments( new messaging.emailFileAttachment[]{DelOLNoOpp });
            Messaging.SendEmailResult[] r = Messaging.sendEmail(new Messaging.singleEmailMessage[]{ademail});
  • Is it a master detail between Opportunity and opportunity_LGT? – Salesforce Learner Sep 23 '15 at 14:26
  • There is not master detail relationship between Opportunity and Opportunity_LGT. – Razu Sep 23 '15 at 14:41
  • However, Opportunity_LGT has lookup relationship with Account – Razu Sep 23 '15 at 14:41
0

As you said - (The exception is being thrown because the maximum number of rows you can return in SOQL calls in Apex is 50000).

I think you should write all of your this (finding your 283 records) logic/code into "Batch Apex Class" and call this batch class from here (in your getalltodeleterecords() method).

In addition your code should respect system limits. Use Limits Class to access limits dynamically in apex.

| improve this answer | |
0

Your first mistake was not using a lookup field to link it to the opportunity.

You best bet:

  1. Create a checkbox on the LGT record called Matched Opportunity
  2. Write a batch that will cycle through the opportunities
  3. In the batch query for LGT records than have an matching Opportunity ID in the txt field (Since you are operating on opportunity the IDs will be available)
  4. Mark the checkbox on those LGT records
  5. Update the LGT Records

Then once it is run you should be left with the 283 records that are not matched.

Then you can use dev console to simple delete them.

| improve this answer | |
  • I can send the 55,283 LGT records to Apex Batch class execute method as scope. To find a matching ID in Opportunity, I need to spin through all 55,000 opportunity records... not sure how to spin through opportunity record without a LIST of ID's.. can you provide some direction on this ? – Razu Sep 23 '15 at 15:29
  • You batch through the opportunities, get the IDs in scope, then query for LGT record having matching ids in the txt fields. – Eric Sep 23 '15 at 15:31
  • Thanks for the answer, Mohit and Eric. It helped me to take it further. – Razu Sep 23 '15 at 15:48
0

You may like to filter your query on the Opportunity_LGT object .Since you have no relationship directly to opportunity and you have relationship only with Account ,you may first collect set of AccountIds from the opportunity if its a trigger context .

set<Id> accIds=new set<Id>();
for(Opportunity opp:[Select AccountId from Opportunity where ID in :setoppIds]){
 accIds.add(opp.AccountId);
}

//Filter your opportunity_LGT__c with this set .

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.