2

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?

3
  • Possibly - could you please edit your question to give more details? Are you deleting all relationships of a certain type? All relationships on certain contacts? Etc. Aug 3, 2021 at 15:45
  • thanks Jon. Is it more understandable now? Aug 4, 2021 at 7:51
  • Hi Guillame, So you have 500 contacts. each with a volunteer for and a help for relationship. with another contact. You no longer need "helps for" in this context, and want to remove the relationship from those 500 contacts. As far as I can see there isn't a way in the ui Search result actions allow you to add relationships not remove them. And relationships aren't configurable within profiles for a batch update. I don't use the API much myself, so don't know after that. Aug 4, 2021 at 21:50

1 Answer 1

2

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.

1
  • 1
    Thanks Dave, this was a speed way of updating the relationship though a bit different from what I was thinking of. Did the job! Aug 5, 2021 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.