Unable to merge Contacts and Cases with Case custom data , raises error - DB Error: already exists (applies to CiviCRM 5.16.0 - CiviCRM 5.18.alpha1)

added ~242 git:civicrm-core regression sig:bug triaged labels

I am not sure whether this a regression, I do think so as it is tested in version 5.16. If it is not a regression bug feel free to remove the regression label.

thanks @jaapjansma & great to have you back triaging !

@justinfreeman does civicrm_value_product_information extend the civicrm_case entity

@DaveD maybe on interest

I'm going to be daring and comment before looking at the code but just some thoughts that come to mind looking at the error: What civicase used to do during merges was create a new copy of the custom data which has its own problems but avoids this one. But originally cases didn't have custom fields. So either this has been around since when custom fields were added for cases, or something has changed regarding making copies of custom data when merges happen.

Footnote, maybe not relevant to the issue but for interest: Cases originally didn't have custom fields because the "spirit of civicase" was that everything was tracked through activities because in the real world all case property changes are the result of something that happened, i.e. an activity. A loose analogy might be that you track contributions separately instead of just updating a single "amount donated" field on the contact record.

does civicrm_value_product_information extend the civicrm_case entity

@eileen yes, this bug is reproducible when there are custom fields assigned to the Case entity.

@DaveD I'll see if I can find out if merging Cases with custom fields ever worked and if so the timeframe.

We've certainly been using custom fields in CiviCase for at least 2 years in various projects, but have never needed to merge Cases, so would not have hit this bug.

removed regression label

Removed regression flag based on discussion

I can confirm it does NOT happen on 5.14.1, so it's not too old.

added regression label

It can have it's label back then :-)

Seems related: https://github.com/civicrm/civicrm-core/pull/14213#issuecomment-501294875

T'was a whole 2 months ago so slipped my mind.

Seems like copyGeneric is duplicating the code that's already in BAO/Case, so need to reconcile.

changed title from Unable to merge Contacts and Cases, raises error - DB Error: already exists (applies to CiviCRM 5.16.0 - CiviCRM 5.18.alpha1) to Unable to merge Contacts and Cases with Case custom data , raises error - DB Error: already exists (applies to CiviCRM 5.16.0 - CiviCRM 5.18.alpha1)

@justinfreeman @DaveD does removing this chunk of code fix

        // CRM-11662 Copy Case custom data
        $extends = array('case');
        $groupTree = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, NULL, $extends);
        if ($groupTree) {
          foreach ($groupTree as $groupID => $group) {
            $table[$groupTree[$groupID]['table_name']] = array('entity_id');
            foreach ($group['fields'] as $fieldID => $field) {
              $table[$groupTree[$groupID]['table_name']][] = $groupTree[$groupID]['fields'][$fieldID]['column_name'];
            }
          }

          foreach ($table as $tableName => $tableColumns) {
            $insert = 'INSERT INTO ' . $tableName . ' (' . implode(', ', $tableColumns) . ') ';
            $tableColumns[0] = $mainCaseId;
            $select = 'SELECT ' . implode(', ', $tableColumns);
            $from = ' FROM ' . $tableName;
            $where = " WHERE {$tableName}.entity_id = {$otherCaseId}";
            $query = $insert . $select . $from . $where;
            $dao = CRM_Core_DAO::executeQuery($query);
          }
        }

Bonus since it's removing code I'd probably let it squeak through on UI testing & no unit test

Thanks @eileen will give that a test later today. Much appreciated!

@eileen have tested and confirmed this works. Case is merged to the target contact and custom fields on the Case remain intact.

Now for some code archeology...

Here's the git blame: https://github.com/civicrm/civicrm-core/blame/ca231c6fe26cdacbd80d31ab9b237ffc518384c4/CRM/Case/BAO/Case.php#L2087

PR is from Jul 20, 2013 - CRM-11662 Add missing logic to copy case custom fields in CRM_Case_BAO_C... #1204 (closed) https://github.com/civicrm/civicrm-core/pull/1204 https://github.com/civicrm/civicrm-core/commit/8bd862834d6b8dacb4fa06b1873fca8c92024b15

CRM-11662 Merge Case does not transfer custom case fields to new copy of case (2013-07-19 23:34) https://issues.civicrm.org/jira/browse/CRM-11662

This comment is still relevant today...

Coleman Watts added a comment - 2013-05-13 16:40 Thanks dexter for investigating and finding a fix for activities. Looking forward to seeing what you find for case custom fields.

Looking at how cases are handled by dedupe it seems to be fairly anomalous. For pretty much every other object in the db (relationships, addresses, phones, activities, contributions, etc) it is a very simple sql update that swaps the 'contact_id' field in that object. Cases on the other hand will actually rebuild the entire case, copy the activities from the old case to the new one, and create a bunch of new activities as well. I don't understand why that is necessary. Does anyone know what the reason for this is and why we can't just swap the case contact id? That would solve this problem and simplify the code.

Sigh - we have such a legacy of bad coding decisions in our code. I feel like the code base is improving but there are still grenades everywhere.

If you put up a PR against 5.17 then @DaveD would you be able to review it?

We are going to be really happy with removing this decision - because it never made sense to us (and Francis always complained about it).

Does anyone know what the reason for this is and why we can't just swap the case contact id? That would solve this problem and simplify the code.

Just got word back from the customer on this one too. The problem started appearing between CiviCRM 5.14.1 and CiviCRM 5.15.1 (inclusive).

PR submitted here https://github.com/civicrm/civicrm-core/pull/15051

Thanks I'll give it a whirl.

The original answer to the question of why not just swap contact ids was because of the civicase requirement for an audit trail/HIPPA compliance. But there are multiple ways of implementing such a trail and it doesn't have to be to make copies, but at the time that's what it did. I've always found merges to be a PITA for multiple reasons - the case id changes, reporting periods change, you end up with multiple copies of singleton activities on one case, sometimes people merge the wrong way and there's no way to undo, etc. Someday maybe it could more like standard accounting, where you make journal entries instead. But I'm getting off topic...

mentioned in commit 890ca27d

closed via commit 890ca27d

mentioned in commit 29c9e5f3

changed milestone to %5.16.3

assigned to @justinfreeman

Unable to merge Contacts and Cases with Case custom data , raises error - DB Error: already exists (applies to CiviCRM 5.16.0 - CiviCRM 5.18.alpha1)

Child items 0

Activity