0

We are testing out ideas to create a script to disable a relationship for a list of contacts, as that doesn't seem to be an option under Actions from a Search output list.

To get a feel for what we want to do, we have a script that generates an SQL query to disable the relationship for a list of contact_id's. Works fine.

However, this relationship is also tied to an inherited membership. That membership is not deleted, so we wanted to construct a parallel SQL query to delete the corresponding membership.

Can someone explain the database structure that allows us to link the two? In the civicrm_membership record, we see the membership_type_id that would tell us which record(s) are possibilities for the ones to delete. However, our members may have more than one corresponding inherited membership with the same relationship_type_id, because it is common for members to belong to two or more organizations, so they may have 2 or more memberships that are distinct and inherited.

A side fact is that the UI action on an individual view to disable or delete the relationship does ALSO remove the membership, so we know it is possible! We are essentially trying to automate this action for a list of contacts.

Another odd fact is that, if we delete the relationship, but still have the dormant membership, if we add the relationship back, then we have TWO memberships in the database, each related to the same organization. Their civicrm_membership records are identical. (If we do it again, we of course now have 3 ;) ) I was hoping there would be a column which would be NULL except for inherited memberships, where the column would contain the corresponding contact_id for the relationship. The membership END DATE provides a "weak" clue, but it is possible that 2 parent organizations could have the same END DATE.

I noticed a JIRA issue that is similar, https://issues.civicrm.org/jira/browse/CRM-16087. But that was evidently solved.

I also ran the Scheduled Job to Update Membership Status, hoping it would remove inherited memberships when the corresponding relationship is disabled or deleted, but that didn't seem to have effect.

Any help in understanding the full linkage for inherited memberships within the database is appreciated. We've been running CiviCRM for about a year now and are finally digging into some of the internals. Our ultimate goal is to provide an addition to the core or an extension so that the Action of disabling a relationship (and removing any inherited memberships based on that relation) is an available Action when viewing any list of contacts in Civi. It is too painful to disable a list of hundreds manually via the UI, although it works.

running Civi 4.7.22 on WP 4.8.2

Improve this question

2 Answers 2

Reset to default
1

As you are discovering, the interactions between these various features are complicated. It is far preferable / safer / easier / more likely to work to use the API to disable these relationships rather than trying to do it via SQL.

If you disable a relationship via the API and it does not handle the inherited membership then that is a bug ... but I have just tried it via API explorer and it works as expected.

$result = civicrm_api3('Relationship', 'create', array(
  'sequential' => 1,
  'id' => 49,
  'is_active' => 0,
));

If you want to do this as a script you could use the snippet above with cv

If you want to go a step further and add it as a search action then the existing 'Add relationship' actions provide a starting point.

Improve this answer
7
  • Thanks for both the API and cv tips. I was searching around the API documentation and had difficulty figuring out how to do exactly what I wanted. I'll head down that road. Understanding the database details was still useful for us as we haven't done much under the hood to date. Many thanks, Aidan!
    – Karl Rudnick
    Commented Oct 3, 2017 at 11:46
  • One more question. We have been successfully using the API csv Import extension. We were hoping to invoke the API via a list with something like a 3 column csv file, each row containing contact_id_a, contact_id_b, relationship_type_I'd. We often have 20-300 rows of enabling and/or disabling these relationships annually for our organizations (bicycle clubs). Do you know if that capability exists? If not, might it be easy to add to that?
    – Karl Rudnick
    Commented Oct 3, 2017 at 11:58
  • I cannot get this to work. In API Explorer, I constructed this sample snippet. $result = civicrm_api3('Relationship', 'create', array( 'sequential' => 1, 'contact_id_a' => 389, 'contact_id_b' => 384, 'relationship_type_id' => 17, 'is_active' => 0, )); I get an error message: "Duplicate Relationship". The relationship DOES exist with is_active=1. We just want to change that field to 0. I tried "replace" instead of "create" and get the error message: "Mandatory key(s) missing from params array: values" Note that UPDATE is deprecated. It says to use create. We just want to UPDATE.
    – Karl Rudnick
    Commented Oct 4, 2017 at 4:12
  • I'll also add that I tried using the "API csv Import" extension with 4 columns: contact_id_a, contact_id_b, relationship_type_id, is_active set the same way as in the API Eplorer snippet. It will not import any rows where the relationship already exists with the same error: "Duplicate Relationship." I can do this in SQL. What am I missing in the API?
    – Karl Rudnick
    Commented Oct 4, 2017 at 4:19
  • OK. I figured I need 2 steps. (i) $result = civicrm_api3('Relationship', 'get', array( 'sequential' => 1, 'return' => array("id"), 'contact_id_a' => 389, 'contact_id_b' => 384, ; ... Returns id=319. Then execute $result = civicrm_api3('Relationship', 'create', array( 'sequential' => 1, 'id' => 319, 'is_active' => 0, )); This both disables the relationship between 389 and 384, while at the same time making the Membership inactive. Again, many thanks @Aidan.
    – Karl Rudnick
    Commented Oct 4, 2017 at 4:39
1

After trolling around some more, the field I was looking for IS in the membership table, owner_membership_id. That is non-nil for inherited memberships. You can follow that key to the parent organization's membership record, and then from there, determine whether that is the organization for which the membership should be deleted. For maintaining history, you can just cancel the individual's inherited membership instead of deleting so it will show up under past, inactive memberships. If done properly, there will never be more than one inherited, active membership for a related (individual, organization) pair unles the organization subscribes to multiple memberships at once.

Improve this answer

Your Answer

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

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