If contact has multiple cases, how do I filter a view to show only case roles associated to that case type

Question

We have contacts that may have multiple cases, each with a case coordinator.

I have created a view for each case type that shows the associated case coordinator along with last activity date, links to add activities, etc.

Everything works great except if a contact has multiple cases, the case coordinator from a different case type may be listed instead of this particular case type's case coordinator.

I have a case type filter set to the specific type, but that doesn't seem to be enough.

Here are the relationships:

Here are the filters:

Answer 1

Thanks for the hint ErikH, you led me down the correct path. We are trying to avoid coding and prefer to just manipulate the views as much as possible.

The view I was working on was a CiviCRM Case view.

I rebuilt it as a CiviCRM Relationship view and added these relationships:

After that, the Case Coordinators were listed correctly and I was able to add all other pertinent fields to the view.

The fields I displayed were:

Client Name (links to another view of client information)

Case Type

Case Coordinator

Case Status

Case Opened Date

Last Activity Date (turned on aggregation for the view and set only this field to Maximum)

View Case Activities (links to another view of activities filtered by contact id)

Add New Activity (links to a webform for that case type)

Answer 2

The case coordinator relationship (table civicrm_relationship) will hold the case_id for a case relationship. You should either link to that or retrieve it using the API (if you are coding)