0

Because there is no ability to create a trigger to detect changes to case team members, we end up with situations where tasks assigned to a case team member are "orphaned" when a case team member is later removed from the case team.

We would like to remove those tasks. One way is to run a scheduled job that looks for orphaned tasks and deletes them.

We were hoping to use a Not In query to identify those tasks, like:

Select ID From Task t
   Where What.Type = 'Case' And IsClosed = false
   And OwnerID Not IN
   (select MemberID from CaseTeamMember where ParentID = t.WhatID)

Basically find any tasks for a case where the task owner is not a case team member.

In order for this to work, the subquery must be correlated with the outer query. I am trying to do that using a table alias on Task (t) and then referencing that in the subquery: ParentID = t.WhatID.

However, this syntax is not accepted.

Are correlated subqueries possible and, if so, how? If not, is there another way to do this that does not require iterating over every case or task?

1 Answer 1

1

No, correlated subqueries are not supported. But in Apex you can use the same variable with the case identifier in both queries:

SELECT Id
FROM Task
WHERE Type = 'Case'
AND WhatId = :caseId
AND IsClosed = FALSE
AND OwnerId NOT IN (
    SELECT MemberId
    FROM CaseTeamMember
    WHERE ParentId = :caseId
)

This makes it impossible to run it in a bulkified fashion with this particular syntax, as you would fetch all tasks for all case team members not in an ordered/grouped fashion. Writing Apex code to sort the tasks by OwnerId is quite straightforward, if you need to, however. Just iterate the tasks and sort in a map of Id (user) to a list of tasks (Map<Id, List<Task>>).

I might add:

If you are using automated processes like Apex or Flow to manage the case team member records, you can surely create a subflow or Apex that is called right after the update to do this for you, kind of simulating a trigger event.

2
  • Thanks for answering the question and the extra info. I'm not 100% sure I am following the extra info, so some questions: 1 - The only way for me to run the query above with the caseid variable is to iterate through every active case? Some sort of batchable process? 2 - The reference about apex or flow you mean remove from the UI the native ability to manage the case team and instead replace it with custom UI to do so, which will allow us to capture changes to the case team member and make appropriate task changes? Nov 26, 2023 at 23:06
  • 1. You could use the IN operator in both queries instead of = when comparing the case id, and then it will fetch all tasks for all cases you have provided the identifiers for. 2. what I meant was if you do have some custom automation in place to handle those records, you could trigger more automation to handle the CRUD operations on that object - for example: if you have some logic like "when the case owner changes, the owner should be removed from the case team". Nov 27, 2023 at 12:00

You must log in to answer this question.

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