CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using CiviCase (Moderator: Dave Greenberg) »
  • Change role of many cases
Pages: [1]

Author Topic: Change role of many cases  (Read 669 times)

altair40

  • I’m new here
  • *
  • Posts: 2
  • Karma: 0
  • CiviCRM version: 4.4.7
  • CMS version: Drupal 7
  • MySQL version: 5.5.41
  • PHP version: 5
Change role of many cases
January 31, 2015, 06:19:31 am
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!

« Last Edit: January 31, 2015, 06:21:44 am by altair40 »

Erik Hommel

  • Forum Godess / God
  • I live on this forum
  • *****
  • Posts: 1773
  • Karma: 59
    • EE-atWork
  • CiviCRM version: all sorts
  • CMS version: Drupal
  • MySQL version: Ubuntu's latest LTS version
  • PHP version: Ubuntu's latest LTS version
Re: Change role of many cases
February 03, 2015, 12:00:17 am
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).
Consultant/project manager at EEatWork and CiviCooP (http://www.civicoop.org/)

OregonJohn

  • I’m new here
  • *
  • Posts: 17
  • Karma: 0
  • CiviCRM version: 4.5.5
  • CMS version: Drupal 7.34
  • MySQL version: 5.5.40
  • PHP version: php5-mysql
Re: Change role of many cases
February 03, 2015, 12:49:05 pm
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

  • Ask me questions
  • ****
  • Posts: 570
  • Karma: 42
  • CiviCRM version: Always the latest!
  • CMS version: Drupal 6 mostly, still evaluating 7.
  • MySQL version: Mix of 5.0 / 5.1 / 5.5
  • PHP version: 5.3, usually on Windows
Re: Change role of many cases
February 03, 2015, 01:55:58 pm
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 new here
  • *
  • Posts: 17
  • Karma: 0
  • CiviCRM version: 4.5.5
  • CMS version: Drupal 7.34
  • MySQL version: 5.5.40
  • PHP version: php5-mysql
Re: Change role of many cases
February 04, 2015, 02:53:11 am
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.

Quote
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

  • Forum Godess / God
  • I live on this forum
  • *****
  • Posts: 1773
  • Karma: 59
    • EE-atWork
  • CiviCRM version: all sorts
  • CMS version: Drupal
  • MySQL version: Ubuntu's latest LTS version
  • PHP version: Ubuntu's latest LTS version
Re: Change role of many cases
February 04, 2015, 11:20:57 pm
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?
Consultant/project manager at EEatWork and CiviCooP (http://www.civicoop.org/)

altair40

  • I’m new here
  • *
  • Posts: 2
  • Karma: 0
  • CiviCRM version: 4.4.7
  • CMS version: Drupal 7
  • MySQL version: 5.5.41
  • PHP version: 5
Re: Change role of many cases
February 17, 2015, 06:52:11 am
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

  • Forum Godess / God
  • I live on this forum
  • *****
  • Posts: 1773
  • Karma: 59
    • EE-atWork
  • CiviCRM version: all sorts
  • CMS version: Drupal
  • MySQL version: Ubuntu's latest LTS version
  • PHP version: Ubuntu's latest LTS version
Re: Change role of many cases
February 17, 2015, 07:53:44 am
Looks good to me, but I would like a second opinion  ;D
Consultant/project manager at EEatWork and CiviCooP (http://www.civicoop.org/)
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using CiviCase (Moderator: Dave Greenberg) »
  • Change role of many cases

This forum was archived on 2017-11-26.