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

Unable to delete records based on a lookup field value

We have two tables Assignments and Schedules.


An assignment can have 1 to many schedules.  A schedule can only have 1 assignment.


Due to how we have our reports set up, there is NO WAY we can have a foreign key back from Schedule back to Assignment. 


What we have is in the Schedule is a lookup field that points back to the Assignment table. When we delete the Assignment, that lookup field in the schedule table goes away.  


We have a trigger on the Assignment with a "BEFORE DELETE".  We fire off a class that attempts to delete all of the Schedule entries that are tied to it.  the code for that is below:


public class deleteScheduleRows 



    public static void deleteRows(ID assignmentID)


     Integer iCount;

     iCount = [SELECT count() FROM SFDC_Schedule__c WHERE Assignment__c =: assignmentID];

     while (iCount > 0)


     List<SFDC_Schedule__c> newList = [SELECT id FROM SFDC_Schedule__c WHERE Assignment__c =: assignmentID LIMIT 900];

     delete newList;

     iCount = iCount - 900;





However, it seems that the Assignment is being deleted first, and its removing the lookup field value in the Schedule table so there is no way to remove the old rows.


I tried removing the "@future" from the class, but then when I attempt to create the list it throws an error about hitting the 1001 limit.


Any ideas?




--Todd Kruse




There are a few things you can do to make this work.

I would 1st try and change the relationship Schedules has with Assignments to a master detail. Then cascading deletes will work for you out of the box.

If you can't do that because of architecture constraints then try this.

You have to remove the @future part of the code there is no way around it. The assignment will be deleted first every time.

I would write an Apex Schedule Job to take care of the orphans once a day.

The code would query on all the Schedules that don't have Assignments.

With in the scheduled class you will have greater limits, but keep in mind you will still be subject to 1000 rows per list. 

So you have to keep the process running till all the schedules are deleted.



We thought of the Master Detail route, but since there are records in the table, it wouldn't allow us to change it to that.  We are going to look at leaving it as a lookup field, update the database and then delete all the orphan records.  Then go back and change the field type to Master Detail.


Thanks again