Answers.Body: <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>$result = civicrm_api3('Relationship', 'create', array(
    'sequential' =&gt; 1,
    'id' =&gt; 49,
    'is_active' =&gt; 0,
  ));
  </code></pre>
  
  <p>If you want to do this as a script you could use the snippet above with <a href="https://github.com/totten/cv" rel="nofollow noreferrer">cv</a></p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-03T11:46:47.077Z
Answers.Text: 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!
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-03T11:58:37.933Z
Answers.Text: 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?
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-04T04:12:06.620Z
Answers.Text: 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.
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-04T04:19:45.557Z
Answers.Text: 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?
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-04T04:39:30.057Z
Answers.Text: 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.
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-04T18:56:35.183Z
Answers.Text: One last comment here on the utility of API csv Import extension to do the work here. I figured I could create a 2-column CSV file with columns relationship_id, is_active, once I have a list of relevant relationship_id's, which seems you need either an SQL query OR use of api('Relationship','get',...). THIS DOESN'T WORK - the API csv Import extension *requires* contact_id_a, contact_id_b, relationship_type_id and it won't import with that added because of duplicate relationships - it tries to create an entirely new relationship record rather than just update is_active. error="Duplicate..."
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-07T17:26:24.080Z
Answers.Text: If you want to update the existing relationship by API, you need to specify the id.  You can do a Get first specifying the contact_id's and the relationship_type and then an update.  If the csv import extension is requiring contact_id's then you can't use it to update.  You could fairly easily create a script to read your csv, then do the get/update for each line.  If you want to create a separate question about using the csv & api I'll add an answer there but no space in these comments!
Answers.UserDisplayName: Aidan
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-03T08:07:14.237Z
Answers.LastActivityDate: 2017-10-03T08:17:02.690Z
Answers.LastEditDate: 2017-10-03T08:17:02.690Z
Answers.UserDisplayName: Aidan
Answers.Body: <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-02T20:20:15.550Z
Answers.LastActivityDate: 2017-10-02T20:37:31.917Z
Answers.LastEditDate: 2017-10-02T20:37:31.917Z
Answers.UserDisplayName: Karl Rudnick
Body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I noticed a JIRA issue that is similar, <a href="https://issues.civicrm.org/jira/browse/CRM-16087" rel="nofollow noreferrer">https://issues.civicrm.org/jira/browse/CRM-16087</a>. But that was evidently solved.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>running Civi 4.7.22 on WP 4.8.2</p>
  
Tags: <civimember><relationships>
Title: HOWTO remove inherited membership when corresponding relationship is disabled or deleted?
ThreadText: # HOWTO remove inherited membership when corresponding relationship is disabled or deleted?
  
  (Asked by: username_0 on 2017-10-02)
  
  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
  
  **Tags:** civimember, relationships
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2017-10-03. Score: 1 | Accepted answer
  
  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.
  
  > **Comments:**
  > **username_0**: 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, username_1!  
  > **username_0**: 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?  
  > **username_0**: 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.  
  > **username_0**: 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?  
  > **username_0**: 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 @username_1.  
  > **username_0**: One last comment here on the utility of API csv Import extension to do the work here. I figured I could create a 2-column CSV file with columns relationship_id, is_active, once I have a list of relevant relationship_id's, which seems you need either an SQL query OR use of api('Relationship','get',...). THIS DOESN'T WORK - the API csv Import extension *requires* contact_id_a, contact_id_b, relationship_type_id and it won't import with that added because of duplicate relationships - it tries to create an entirely new relationship record rather than just update is_active. error="Duplicate..."  
  > **username_1**: If you want to update the existing relationship by API, you need to specify the id.  You can do a Get first specifying the contact_id's and the relationship_type and then an update.  If the csv import extension is requiring contact_id's then you can't use it to update.  You could fairly easily create a script to read your csv, then do the get/update for each line.  If you want to create a separate question about using the csv & api I'll add an answer there but no space in these comments!  
  
  ---
  
  ### Answer by username_0 on 2017-10-02. Score: 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.
  
  
  ---
  
  