Batch Update the current relationship in CiviCRM

Question

I need your help. I need to disable a current relationship for 4000 records in the import process without using SQL command line.

There is no field for set active/inactive or expiry date of the current relationship in basic import process in CiviCRM.

Then I found this extension here https://civicrm.org/extensions/api-csv-import-gui,I've installed the extension and tried to inactive the current relationships. I used contact_id_a and contact_id_b and relationship_type id and set the is_active to 0 in csv file. But this extension gives me an error "Error with entity "create"! (Duplicate Relationship)". I'm going to update this relationship but this extension tries to create a relationship and fails because this relationship is already existed. Do you have any idea how to solve this?

Or do you have any other solution for this?

CiviCRM version is 5.13.5. And the relationship type is between two organization.

Edit: My requirement is:

Currently I have around 5000 records with a type of relationship in CiviCRM (organization to organization -one to many). Some of these records (relationship, name, email,...) have been updated in another system (A government system) and they gave the current list to me to update our current records in CiviCRM. I've updated contact details successfully but stuck in updating relationship, Because the CiviCRM basic import creates a new relationship not overwrite the existing one. If a relationship between 2 organization has changed in new file I need to disable the one that we have in civicrm and import the new one. So in future I know which records used to be in relationship but not any more. There is no option in CiviCRM import to do this (I couldn't find any). So I was thinking to find a way to disable the current one in CiviCRM and import new list. So if the relationship is no longer available in the new file, this has been disabled already in CiviCRM and if there is still a relationship between two records, it enables that again when I'm importing.

Thanks in advance

Answer 1

I used contact_id_a and contact_id_b and relationship_type id and set the is_active to 0 in csv file. But this extension gives me an error "Error with entity "create"! (Duplicate Relationship)".

Seems like you have relationship_type_id but not relationship id in the CSV file? If yes, I think you just need to add Relationship id to the columns.

The id is used by civicrm to update the records in the database instead of creating a new one.

Hence, try adding a new column in CSV and then proceed with the import.

Answer 2

Just answering the question in the comments about how to export the relationship_id. One way is to run a Civi relationship report from Reports | Create Reports from Templates and include the Rel ID column: Then from Actions select Export as CSV.