Migrating to CiviCase

On a recent project, I setup CiviCRM and CiviCase to enable a local organization to better manage its client database. The old database had been developed in Borland Paradox and was quickly becoming unusable. As a free and open source solution, CiviCRM turned out to be just the solution needed to accommodate this non-profit.

While CiviCRM itself does allow for the import of data through its API interface, I found that the contacts importer rejected a good portion of this data. The data had been entered by many volunteers into a database which did not impose many constraints on what data was required or how it could be entered. As with any case history, this data was essential to working with clients. With the volume of data involved, manual re-entry was not an option.

Fortunately, while CiviCRM’s data model is sophisticated, it’s relatively easy to decipher and I was eventually able to import the bulk of the data into CiviCase using a series of MySQL queries and MySQL Workbench.

The Prep

Before running any import procedure or other major operation, you should make sure you have a backup of your CiviCRM database in case things go wrong. In MySQL Workbench, it’s as simple as using the Server >> Data Export menu and exporting the entire database to a script.

In my case, Importing the cases was part of the larger project of doing an initial import of all data from the old system into CiviCRM. Fortunately, the old Paradox program could export to CSV which I then imported into custom tables that I created in the CiviCRM database using the Table Data Import Wizard in MySQL Workbench. This wizard is available from the context menu that you see when you right-click on a database or table under the list of Schemas. It is not the same as the Data Import on the Server menu.

Once the data has been imported into an intermediate table, you should verify the import by running a few queries and look for any inconsistencies. In my case, I found that the old database had not kept any control over how dates were entered. This meant that I had to write a function to make some repairs to the data which I detailed in an earlier post.

Another feature you will see in the upcoming queries is a RECIMPORT field in the import table. This field can be set to 1 or 0 to indicate specific records that should or should not be imported. This might be helpful if you end up with incomplete or otherwise useless records.

The Tables

While the Case header information only involves a few fields, CiviCase uses several tables for storing contact and activity information for cases. Cases that are imported into the case header table won’t even show up until they’re fully linked to a contact. Here’s a list of the tables involved.

• civicrm_case – primary case data (header)
• civicrm_case_contact – to link each case to a contact id
• civicrm_activity – to create the necessary activities for each case
• civicrm_case_activity – links activity to case id
• civicrm_activity_contact – multiple records for each activity
• civicrm_relationship – relationships between case subject and assignees

You can actually get away without filling in the relationships for purely historical data but it’s only an extra query.

The Queries

The following are example queries based on the ones I used. Of course, yours will be different but I wanted to include some things to keep in mind when writing to the CiviCase tables.

civicrm_case

INSERT INTO civicrm_case
 (case_type_id, `subject`, start_date, end_date, details, status_id, crm_id)
SELECT ... 
FROM ImportTable ...

The crm_id field in the INSERT statement is actually a custom integer field that I added to the table. After doing the initial import of the contacts, I wrote the CiviCRM ID (civicrm_contact.id) back to the import table that I’d used to hold the CSV data. Inserting the value into the Case records makes the following queries much easier. It also serves to distinguish the imported cases from those that have been entered manually. While it’s not recommended to make changes to the core tables in CiviCRM, adding an extra temporary field didn’t seem to pose much risk.

The case_type_id field holds the id of whatever case type you want to use. This is why I suggested earlier that you create a custom case type within CiviCase to use for imported cases. You can then get the id from the civicrm_case_type table.

The subject and details fields can be used to hold additional data which you can use later to re-assign case types if you choose or simply retain as much background information as possible.

The status_id field does not link to anything within the data model. You can get the appropriate value from the Case Statuses screen within CiviCRM (Top menu: Administer >> CiviCase >> Case Statuses).

civicrm_case_contact

INSERT INTO civicrm_case_contact
 (case_id, contact_id)
SELECT CC.id, CC.crm_id
FROM civicrm_case CC
WHERE (CC.crm_id is not null);

The civicrm_case_contact table links each case to the correct CiviCRM contact by storing the respective ID values. This is where the addition of the crm_id field in civicrm_case starts to come in handy. You should also check the count of the records affected by the query to ensure it’s the same as the number of cases inserted earlier. If it’s not, you’ll have case records that are not linked to any contact.

After you create these records, the contact record will show the number of cases created for the contact but won’t show the cases themselves. For that, each case needs an activity record.

civicrm_activity

INSERT INTO `civicrm`.`civicrm_activity`
(`activity_type_id`,
`subject`,
`activity_date_time`,
`status_id`,
`priority_id`,
`medium_id`,
`case_id`)
SELECT 13, 'Request', CC.start_date, 2, 2, 1, CCC.case_id
FROM civicrm_case_contact CCC
INNER JOIN civicrm_case CC
ON CC.id = CCC.case_id;
WHERE (CC.crm_id is not null);

Every case in CiviCase has at least one activity associated with it and the activities are defined as part of the case type (Menu: Administer >> CiviCase >> Case Types).  Even if there are no specified activities, CiviCase will insert an ‘Open Case’ activity when the case is created. I was not able to find where the values for all of the ID fields were stored so I created a dummy case record and got the appropriate values that way. In this case, it’s an ‘Open Case’ activity (activity_type_id: 13) with a status of ‘Completed’ (status_id: 2) which has been Prioritized as ‘Normal’ (priority_id: 2) and was initiated by an in person meeting (medium_id: 1).

The case_id field is another custom field that I added to simplify the next query. The query uses a link to the civicrm_case and civicrm_case_contact tables in order to create a single activity for each case, transfer the case start date over as the activity date and reference the case ID.

civicrm_case_activity

INSERT INTO civicrm_case_activity
 (case_id, activity_id)
SELECT CA.case_id, CA.id
FROM civicrm_activity CA
WHERE CA.case_id IS NOT NULL;

Once the activities are created, each one has to be linked to a specific case. After this is done, the actual cases will show up under the client record. With the case_id field added to the activity table, it just takes a simple query to transfer the case_id, activity_id combination into the table.

civicrm_activity_contact

INSERT INTO civicrm_activity_contact
(activity_id, contact_id, record_type_id)
SELECT CA.id, 1, 2
FROM civicrm_activity CA
WHERE CA.case_id IS NOT NULL

INSERT INTO civicrm_activity_contact
(activity_id, contact_id, record_type_id)
select CA.id, CCC.contact_id, 3
FROM civicrm_activity CA
INNER JOIN civicrm_case_contact CCC
ON CCC.case_id = CA.case_id
WHERE CA.case_id IS NOT NULL;

The civicrm_activity_contact table links each activity to specific contacts. These contacts include the Reporter, the activity Assignee and the Case Contact.

The first query above adds an activity contact for each activity previously created, pulling the activity ID from civicrm_activity and then manually assigning a contact_id. You’ll need to fill in your own contact_id for the CiviCRM user you want to assign as the reporter for the imported data. The record_type_id field indicates the role of Assignee (1), Reporter (2) or Contact (3). So with this query, I’ve created a Reporter contact record for each activity.

The second query adds a record for each activity naming the associated case contact. (record_type_id = 3). The case_id field I added to civicrm_activity once again allows me to link back to the civicrm_contact table to get the Contact ID.

civicrm_relationship

INSERT INTO civicrm_relationship
 (contact_id_a, contact_id_b, relationship_type_id, start_date, is_active, case_id)
SELECT CCC.contact_id, 1, 9, CC.start_date, 1, CC.id
FROM civicrm_case CC
INNER JOIN civicrm_case_contact CCC
on CCC.case_id = CC.id
WHERE CC.crm_id IS NOT NULL;

CiviCase allows for relationships to be defined between contacts including family relationships and working relationships between outside contacts and people within the organization. When a case is manually entered in CiviCase, the software creates one of these relationships between the case reporter and the case subject (contact). These relationship records are stored in civicrm_relationship.

The values for the relationship_type field are listed in civicrm_relationship_type. In this query, I’m creating a “Case Coordinator is …” relationship for each case imported. The contact_id_a record is for the subject of the case and the contact_id_b record is the coordinator so, again, I manually assigned a contact_id value as the coordinator for all of the imported cases and pulled the contact ID from the civicrm_case_contact table.  The Case ID and date information were available from civicrm_case itself.

The only thing I’d caution you on here is that adding a single contact to thousands of cases within the database might cause reporting issues when CiviCRM is asked to pull up the data for thousands of relationships at once. So take this into account when adding relationships during a mass import.

Correction – August 2016: I previously stated that CiviCRM did not allow for the import of case data. In fact it does with some knowledge of its API.

---

Python Crash Course, 3rd Edition: A Hands-On, Project-Based Introduction to Programming

• Use powerful Python libraries and tools, including pytest, Pygame, Matplotlib, Plotly, and Django
• Make increasingly complex 2D games that respond to keypresses and mouse clicks
• Generate interactive data visualizations using a variety of datasets
• Build apps that allow users to create accounts and manage their own data, and deploy your apps online
• Troubleshoot coding errors and solve common programming problems

Check Price

ComeauSoftware.com uses affiliate links through which we earn commissions for sales.

---