Can I disable check for duplicate relations?

[johanv]

Hello,

We are migrating data from a very old custom software package into CiviCRM. The migration is going well, but now I am facing a problem. Maybe you could help me out.

In our installation, we have a custom relationship from individual to organization, called 'has function at'. We created a custom field on this relation, which adds a drop down with about 20 possible functions. You need to select exactly one of those functions.

It is possible however that you have two functions at the same organisation. Let's say individual A has functions F1 and F2 at organisation B. Then we would create two releationships 'has function at' from A to B. One with the custom field value 'F1' and one with the custom field value 'F2'. Sometimes the periods of those 2 relations overlap, sometimes they don't.

This only seems to work in CiviCRM when the start and end date of the relationships differ. Which is strange.

If I add both relationships with today as start date, CiviCRM does not add the second one, it complains about a duplicate relationship. If I add the first one with today as start date, and the second one with yesterday as start date, it does work. So CiviCRM does not care about overlap, but it does not like two relationships at the same time for exactly the same period.

I know that I can make the 'function' field of my relationship multi value, but this makes things more complicated as they should be. Suppose individual A has functions F1 and F2 starting from today. If after a year, A only has function F1, and stops having function F2, it is not straightforward how to fix this.

So I wonder if I can disable this check for duplicate relations. And if not, is the fact that relations can overlap a bug?

Do you have a suggestion about how we can fix this problem?

Thank you!

[Heather O.]

Hi Johanv,

Knowing how CiviCRM handles imports, that does sound like expected behaviour to me. It would treat data with the same relationship type and date as a duplicate (because the data is the same) and if you had a second relationship with a different date, that wouldn't be a duplicate because the data is different.

Have you tried choosing the option for No Duplicate Checking?

No Duplicate Checking: Inserts all valid records without comparing them to existing contact records for possible duplicates.

I've never tried an import like to one you are describing so I'm not sure how that option would treat the data.

Might be worth trying with a small sample and seeing how it handles it.

Hope that helps!

Heather.

[johanv]

Have you tried choosing the option for No Duplicate Checking?

Hi Heather,

Thank you for your suggestion. Our migration does not use the native civicrm-import, but uses the civicrm_migrate module for Drupal (https://drupal.org/project/civicrm_migrate) instead. This module imports records using the CiviCRM API, so there is not really an option 'No Duplicate Checking'.

That said, I don't think unchecking 'No Duplicate Checking' would work, because our problem is not import-specific. If you manually add a relationship with the same type, contacts and dates, you get the error message as well.

[Eileen]

The limitation is definitely hard coded into the system at quite a deep level. I also tend to disagree with it - but you'd probably need to discuss on IRC with Dave Greenberg (dgg)

[johanv]

Thank you Eileen for the feedback.

Maybe there is a good reason to have this limitation. But then I wonder what the recommended way is to implement the concept of 'has a function at'.

I could create a different relationship type for each of the functions. Which would result in about 20 custom relationship types. Not sure whether this is a good idea. It would certainly make the migration more complex.

I will try to contact dgg, and ask for his opinion.

[joanne]

We hit the same problem when our old data was migrated into CiviCRM.  When a check for a duplicate relationship is made the check is at the relationship_type_id level and the custom field is ignored.

Perhaps the custom field functionality for relationships was an add on and the duplicate check wasn't modified at the time?  I certainly think the duplicate check should include custom fields (or there should be a tick-box option to include a particular custom field in the duplicate check when you set up that field.)

Describing our situation and eventual solution may help you to think about alternative ways you could handle your data, but again, alternatives are likely to make the migration more complex.

We run training courses that have to be accredited by the national training authority for post-secondary education.  To ensure the courses remain relevant they have to be revised and re-accredited  every three years and with the re-accreditation comes a new course code. So far we have 18 course codes and the number will increase by 2 each year as the courses are progressively revised.  We need a permanent record of the correct code for every course a contact has been enrolled in, even if they drop out, and of every qualification they receive.

We started off with two relationships of 'enrolled in' and 'qualified in' and with a custom field for the course code and hit the 'duplicate relationship' problem.

We ended up with each course set up as a contact sub-type of  'organisation' .  We only need the same two relationships, but there is no need for the custom field qualifying the relationships.

The existing data had to be restructured and doing something like that would make your migration more complex.

[johanv]

Well. I looked into the CiviCRM source code, and I found where the check for duplicate relationships is implemented. And I created a patch for CiviCRM 4.3 (it should be attached to this post), that calls a new function 'needsCheckDuplicateRelationship($relationship_type_id)'. Only when this function returns true, the check for duplicates is executed.

Of course there are some comments to make:

1. The implementation of needsCheckDuplicateRelationship is rather stupid. Now it checks whether the name_a_b of the relationship type ends with '_NCD' (No Check for Duplicates). Only in that case, 'false' is returned, otherwise 'true'. It would be way better to have a field for this in the civicrm_relationship_type database table, and make it configurable by the UI. But I am new to CiviCRM development, and I don't know yet how to do this.

2. If needsCheckDuplicateRelationship returns false, the whole checkDuplicateRelationship is skipped. Maybe it contains some tests that are important to do. Like e.g. checking whether there is no other relationship with the same relationshipId.

3. I didn't run any unit test. I tried what's on the readme file in the test dir, but I guess I will have to install phpunit. I didn't look into this yet.

4. As said, I am new to CiviCRM development. I probably did a lot of things just wrong.

5. I created a patch against CiviCRM 4.3, because we are using this version ATM. But I guess we'll upgrade soon (we are still developing), then I can recreate it against CiviCRM 4.4.

6. I guess I should find the proper place to create a feature request, post this patch, and ask for feedback about how I can improve it. Which will probably include reading the development guide :-)

[Donald Lobo]

I would approach the patch in a slightly different manner. In general, irrespective of the rules, CiviCRM imposes: someone, somewhere will disagree with it and want a more lenient and/or stricter rule. I do think data validaton and consistency checking is important

So that said, and under the assumption that we'll keep our current rules as is, i would implement a hook to allow other modules to basically override our decision and make their own decision. So just before the end of the function checkDuplicateRelationship, i would introduce and call a new hook (would be good to figure out if we can generalize a 'duplicate' hook). Any participating module/extension can override and do their own dupe checking

ping us on irc if you need more help on implementing a hook etc

lobo

[SarahG (FountainTribe)]

johanv - Sorry if this is a bit off-topic, but have you considered creating participant records to represent "is enrolled in" and "is qualified in"    instead of relationships?  You can use those terms ( enrolled in and qualified in) as custom participant roles (in addition to the usual roles).   I use CiviCRM heavily for tracking which courses people have taken ( and the relevant dates) and the "participant" type records work well for this. 

[johanv]

@epg: Thank you for the suggestion, but I don't think this will work in our situation. We have +/- 1000 organisations. An individual can have different functions in different organisations. So we need a connection between individual, organisation and function. If I understand it correctly, participation can connect individuals with functions, but I don't see where the link with the organisation fits in.

@lobo: I like what you said about implementing a hook. I will look into this; I'll keep you updated.

[Eileen]

Oddly this is one piece of code where I would argue the logic should be moved to the form layer :-). It's a particularly nasty bit of code which has been problematic to  work with - & enforces something that, if it has a place, doesn't seem to have a place in the BAO. Actually to my mind it makes sense in the import but not elsewhere.

[johanv]

I created an issue for this discussion on the issue tracker:
http://issues.civicrm.org/jira/browse/CRM-13725

[francescbassas]

We are currently in a similar problem. We have several relationships that combine custom fields and therefore should have not to apply the default duplication rules in these relationships.

For example: we have among others a relationship ('is volunteer of') that may overlap in time, as it has a custom field that defines what program of the organitzation is doing the voluntary. Volunteers can participate in a multiple programs of the organitzation at the same time.

It would be great if this functionality come in future versions of CiviCRM. Meanwhile, we apply a similar solution to the one proposed by johanv, overloading the check for duplicate relationships we need.