I want to create a custom Dashboard with Drupal Views to substitute the built-in CiviCRM dashboard (because CiviCase dashboard shows cases for invisible contacts ).
I want to set a contextual filter on this logged in user's ID (CiviCRM Drupal match table: Drupal User ID).
Then, I want to create several Relationships to access all the Case IDs of the cases this user coordinates: The currently logged in user is the "Case Coordinator". In the "Relationships" section of Drupal Views, I include all of their Clients. Then I include all Case IDs on this Client.
The problem is: This includes all Cases on a Client, no matter if the Case Coordinator of that case is the logged in user, or not. In other words: A Case Coordinator can see all the cases of one client in my custom dashboard, as soon as he coordinates one case on that client. This is undesired behavior. Every case coordinator should only see the cases he actually manages. This also distorts the numbers.
Afaik, this behavior appears because the specific connection between Case Coordinator and Case ID is a CiviCRM Role instead of a relationship between entities. Only relationships can be included in a Drupal View. I can access the CiviCRM role as an array per API Call (get Case) though:
"contacts": [
{
"contact_id": "76",
"sort_name": "client name",
"display_name": "client name",
"email": "[email protected]",
"phone": "0000",
"birth_date": "",
"role": "Client"
},
{
"contact_id": "49",
"display_name": "coordinator name",
"sort_name": "coordinator name",
"relationship_type_id": "9",
"role": "Case Coordinator is",
"email": "[email protected]",
"phone": "",
"creator": "1",
"manager": "1"
}
],
Does anybody know how I can access this CiviCRM Role in Drupal Views? Can I "convert" it into a relationship? Or can I code a custom relationship that contains an API Call to that CiviCRM Role (where I am able to access the fields included by Relationships in my code, at least the Case ID)?
I thought that maybe this could be a solution, but it turns out the accepted answer only does the same solution that I tried above, except that it starts from the other end of the relationship chain (Case ID -> Client -> Case Coordinator). The problem that this returns too many entries remains (here: returns all Case Coordinators of that Client for each Case ID, not only Case Coordinator of this specific Case ID).
I think it is an essential function that Case Coordinators can only view their "own" cases in a dashboard. That's why I wouldn't like to make any compromise here, if possible.
