0

I have got a requirement where i have created 3 custom objects.

Obj1 : Project, Obj2 : Employee

Obj3 : Assignment - This is Junction Object ( Master-Detail relationship to both Project and Employee)

Junction Object has only 2 fields Project__c and Employee__c

I have a Visualforce page which shows a dropdown of all Projects and upon selecting any project, I am displaying all the Employees associated to it. Now my many to many works like a charm. No issues so far !!

On the same page, I have a Remove button and on click of this I have to remove the link between Project and Employee. So I thought of deleting the junction object record which links the chosen project and employee. ( Is deleting a good idea ? )

Now the ask is : I am deleting the junction object record, but Project and Employee Records remain as it is. Now I have a requirement which says, I should write a batch which picks up all the employees which don't belong to any of the Projects.

The problem i am facing here is, Since I have deleted the junction object record in previous step but I don't know how I can query the orphan Employee records which don't belong to any of the projects.

Please suggest. Is deleting a junction object record better practice than just un-linking the junction object record by setting the jnObj.employee__c to NULL?

2
  • 3
    In case if you junction object has master-detail relationships, then you can make rollup summary and check how many assignments are per project or per employee quite easy, using filter. In case of lookup, you can do custom triggers to performa same calculations or use DLRS
    – kurunve
    Dec 28, 2017 at 8:39
  • What would you do to the orphaned employee records in the batch class?
    – Mahmood
    Dec 28, 2017 at 11:31

2 Answers 2

2

Ideally, you should delete the relative record (employee) and its links (junction object) records would be deleted automatically. But I am not aware of your requirements, so there might be some case you needed that.

I am wondering what is the purpose of your batch class where you are picking orphaned employees. (deletion, I suspect!)

With the current requirements in place, you could do a SOQL in your batch class to get orphaned employees

SELECT Id FROM Employee WHERE Id NOT IN ( SELECT employee__c FROM Assignment )

Edit

As mentioned in the comments, using SOQL like above might have selectivity issues in case number of assignments and employees increase in the system. Therefore, proposing another solution

Create a roll-up summary field, say No_of_Assignments__c on Employee and count number of Assignments and the above SOQL would be updated as

SELECT Id FROM Employee WHERE No_of_Assignments__c = 0

You didn't mention what you are going to do with the orphaned employees. Problem with above solution is, New employees will also be picked as orphaned. To correct that,

  1. you need to create another checkbox field Orphaned__c. By default it will be Unchecked.
  2. create a workflow on Employee, use created, and any time it's edited to subsequently meet criteria and enter criteria using formula

AND( (PRIORVALUE(No_of_Assignments__c) > 0), (No_of_Assignments__c = 0) )

  1. in the action update the Orphaned__c field to true.

Now, your final SOQL will only be picking the employees that have been affected will be

SELECT Id FROM Employee WHERE No_of_Assignments__c = 0 AND Orphaned__c = true

5
  • Depending on the number of Employees and Assignments, this query may run into selectivity issues.
    – Derek F
    Dec 28, 2017 at 13:47
  • Thanks all. Initially I needed a Query so @Mahmood gave the right query. Thanks to** kurunve for simpler solution as well. But yes, if the number of assignment records are huge then the query might be little heavier as **Derek mentioned. So i will stick to Roll Up summary approach for time being. Dec 28, 2017 at 14:15
  • Thanks @DerekF, I have updated the answer. I didn't want to go for the overkill, but you asked for it. :P
    – Mahmood
    Dec 28, 2017 at 14:18
  • @Mahmood for now I have to delete the orphan Employee records using batch. Keep the Project records as it is. The moment I delete the Junction Object record, the employee record will sit ideal until my Apex Batch picks it up in the night and delete them. Dec 28, 2017 at 14:21
  • Instead of deleting the relation (assignments), why are you not deleting the Employee? That would delete the assignments as well, not to mention, will save you from all of the above batch class, additional fields, and automation as well.
    – Mahmood
    Dec 28, 2017 at 14:24
1

You cannot unlink your junction object because it is master-detail. Both parents are required.

You could accomplish your objective of removing orphaned Employee records in a number of ways. The most natural approaches to me would be a nightly batch process to query for and delete them, or an after delete trigger on your junction object to check whether associated Employee records still have a Project.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .