Bulk update of relationships

Question

I've a bunch of contacts with one no more needed relationship that I want to bulk delete.

It's a batch of 500 contacts that have 2 relationships and one is outdated, something like volunteer for and helps for. I need to suppress one of the two as it is no more needed. This group is easy to identify but I can't find any quick action to do this. I don't want to hard delete the target relationship in database because it is still needed for some other contacts. What I need is to bulk remove this particular relationship from a certain batch of contacts.

Is there a way to do this in the UI? Or with the API?

Answer 1

I'll assume none of them are case relationships which makes it simpler.

Put the contacts in a group (a regular group not smart). Then you could do something like

UPDATE civicrm_relationship r
INNER JOIN civicrm_group_contact c ON
 (c.contact_id = r.contact_id_a
  AND r.relationship_type_id = the_relationship_type_id
  AND c.group_id = the_group_id_of_the_group_you_just_made
 ) 
SET r.end_date = CURDATE()-1;

Obviously replace the_relationship_type_id and the_group_id_of_the_group_you_just_made with the appropriate id numbers.

Depending on how your relationship type is defined you may need to switch contact_id_b instead of contact_id_a in the query. You can also set is_active=0 if you want.

Since you just created the group just now you don't have to think about status in that query. You can now get rid of the group.

Note if you have extensions that do things when relationships are changed this will bypass them. This also wouldn't work with employer/employee since there's extra stuff that happens when those change.

There is probably a way to do this with API but it might take some trial and error to get the joins for update.