Limit search of Activities to Cases for which I am the coordinator

Question

The search specified below (image and Query Info > API below) works brilliantly. It shows all activities and their corresponding cases where the Activity is "open" (i.e. not Completed nor a couple of other Activity statuses). The one refinement it needs is to limit the results "My Cases" (something like requiring that result column "Case Clients - Contact Case Roles: Contact (Near side) Display Name" equal Extra > Current User ID). The use case is a My Open Activities in Cases dashlet.

I've tried adding conditions under "With (optional) Contact Case Roles" but in all cases the result is that the column "Case Clients - Contact Case Roles: Contact (Near side) Display Name" comes back blank.

Any guidance on how to approach this appreciated.

{ "version": 4, "select": [ "Case_CaseActivity_Activity_01.activity_date_time", "Case_CaseActivity_Activity_01.created_date", "Case_CaseActivity_Activity_01.status_id:label", "Case_CaseActivity_Activity_01.activity_type_id:label", "Case_CaseActivity_Activity_01.subject", "case_type_id:label", "id", "Case_CaseContact_Contact_01.display_name", "Case_CaseContact_Contact_01_Contact_RelationshipCache_Case_01.near_contact_id.display_name" ], "orderBy": [], "where": [ [ "case_type_id:name", "IN", [ "college_assistance_fund" ] ], [ "status_id:name", "!=", "Closed" ], [ "is_deleted", "=", false ], [ "Case_CaseActivity_Activity_01.status_id:name", "NOT IN", [ "Cancelled", "No Followup", "Completed" ] ] ], "groupBy": [], "join": [ [ "Contact AS Case_CaseContact_Contact_01", "INNER", "CaseContact", [ "id", "=", "Case_CaseContact_Contact_01.case_id" ] ], [ "Activity AS Case_CaseActivity_Activity_01", "INNER", "CaseActivity", [ "id", "=", "Case_CaseActivity_Activity_01.case_id" ] ], [ "Case AS Case_CaseContact_Contact_01_Contact_RelationshipCache_Case_01", "LEFT", "RelationshipCache", [ "Case_CaseContact_Contact_01.id", "=", "Case_CaseContact_Contact_01_Contact_RelationshipCache_Case_01.far_contact_id" ], [ "Case_CaseContact_Contact_01_Contact_RelationshipCache_Case_01.near_relation:name", "=", "\"Case Coordinator\"" ], [ "Case_CaseContact_Contact_01_Contact_RelationshipCache_Case_01.is_active", "=", true ] ] ], "having": []

Answer 1

I got something close to this search working starting from a related contacts search and pulling in case id from the relationshop

And then filtering the relationship contact for current user

Answer 2

Is it a default install you're playing with - the role Case Coordinator - that role in a default install is never the case manager - it's either Homeless Services Coordinator or Senior Services Coordinator. So that would explain blanks.

Then for your "current user" part, you can do it if they are listed on the given activity in some role, either reporter/assignee/target. Because then the Activity Contact - Contact ID is available and you can equate it to "select current user".

But otherwise I don't think the case-contact-role-contact-id is available. But what you could do is leave that part out, then create a form for the search display and have contact display name as a filter. It just wouldn't automatically be their name, they'd have to type it in the filter.