Change role of many cases

[altair40]

Hello!

A problem for our organization without solution until today...

A staff member who is assigned as coordinator of many cases (circa. 100) leaves: how could we re-affect/change his role to an other staff member "in a automatic way"?

Thanks!

[Erik Hommel]

I can not think of any way that would not involve SQL statements directly in the database (or creating a little extension to do so).

[OregonJohn]

No solution here, but it is a problem I will be facing in the near future.

One important thing, I think, is to change only the cases that are active, because I want the history to remain with the closed cases.

Developing that thought, it would be nice to simply add the new case manager while keeping the old one for historical purposes.

Haven't tried, but can there be two case managers?

John in Oregon

[demeritcowboy]

Yes you can have 2, 3, or even 5 (haven't tested 4 - grin). You could use the end date for the relationship to track the history (roles are really just the same as regular relationships), although without some template modification you wouldn't see it on manage case to know which one is active.

Maybe this is a good developer sprint topic for a beginner-intermediate if it seems interesting to someone. The actual backend part shouldn't be hard, just takes some time to put it all together.

[OregonJohn]

I'm getting in over my head, but this extension mentioned by colemanw on November 13, 2014 seems to offer just what you're looking for. Unfortunately, it looks to me like it's Drupal only.

New features for Webform-Integration - cases, activities, grants and attachments!
...
Multiple Cases - open or update any number of cases on a single webform.
Multiple Activities - Create as many activities as you wish.
Activity and case settings have been decoupled from each other so you can file activities on a case, or not, independent of what cases you are working with.

https://www.drupal.org/project/webform_civicrm

[Erik Hommel]

No, that extension allows you to create/manage a case from a Drupal webform. Very good and useful extensions, but I do not think it will help you to replace a lot of Case Managers in one go?

[altair40]

Thanks for yours feedbacks!
Let's go: i tried something with SQL statements, seems to work but be careful with this, my skills are sooo weak!!

Code: [Select]

// create X new relationship "Coordinator" (id=9) with new staff member (id=224) in succession to old one (id=954)
INSERT INTO civicrm_relationship
(`contact_id_a`, `contact_id_b`, `relationship_type_id`, `start_date`,`is_active`, `case_id`)
SELECT `contact_id_a`, '224', '9', '2015-02-17', '1', `case_id` 
FROM civicrm_relationship
WHERE `contact_id_b` = '954' AND `relationship_type_id`= '9' AND `is_active`= '1'

// create 1 new activity "Assign Case Role" (id = 20) with date/time to display in activity of cases
INSERT INTO `civicrm_activity`
(`source_record_id`, `activity_type_id`, `subject`, `activity_date_time`, `duration`, `location`, `phone_id`, `phone_number`, `details`, `status_id`, `priority_id`, `parent_id`, `is_test`, `medium_id`, `is_auto`, `relationship_id`, `is_current_revision`, `original_id`, `result`, `is_deleted`, `campaign_id`, `engagement_level`, `weight`) VALUES
(NULL, 20, 'Coordinator case is XXX', '2015-02-17 12:53:08', NULL, NULL, NULL, NULL, NULL, 2, 2, NULL, 0, NULL, 0, NULL, 1, NULL, NULL, 0, NULL, NULL, NULL)

// Then create links in activity_contact table between new activity (#3937) for source (record_type_id = 2) and assignee (record_type_id = 1)
INSERT INTO `civicrm_activity_contact`
(`activity_id`, `contact_id`, `record_type_id`) VALUES
(3937, 2, 2)
(3937, 224, 1)

// create link(s) between activity (#3937) and X cases belonging to old staff member (id=954)
INSERT INTO civicrm_case_activity
(`case_id`, `activity_id`)
SELECT `case_id`, '3937'
FROM civicrm_relationship
WHERE `contact_id_b` = '954' AND `relationship_type_id`= '9' AND `is_active`= '1'

// close X old relationship belonging to old staff member (id=954)
UPDATE civicrm_relationship
SET `end_date` = '2015-02-17',`is_active` = '0'
WHERE `contact_id_b` = '954' AND `relationship_type_id`= '9' AND `is_active`= '1'

[Erik Hommel]

Looks good to me, but I would like a second opinion