7

I have made an organisation / individual relationship type as an a_b relationship while it should be a b_a relationship to be consistent with the existing relationships like employer of which always have a as individual (child) and b as organisation (parent).

There are allready a lot of contacts that have this relationship in the database.

I tried to change the direction of the relationship type at civicrm/admin/reltype?action=update&id=12&reset=1 and then swap the data of the contact_id_a and contact_id_b columns in the database for that relation type id.

So what is the best way to change the direction (swap individual/organisation) of a relationship? And what else has an influence on this except for the database tables of civicrm_relationship and civicrm_relationship_type?

  • Hans - what do you actually gain from doing this, other than 'consistency'? There may not be any logic/code that means the way you have it is 'wrong' – petednz - fuzion Sep 30 '15 at 1:53
  • If you use Drupal views you have to specify the relationship as a views relation and there you have to choose between an A or a B relationship. So if "employer of" is in a different direction then "organisation of" you cannot use 1 view to get them both in 1 column. – Hans Rossel Sep 30 '15 at 6:20
  • ok thanks, though maybe you can get them as a second column and then concatenate those. btw we have just had to do this for more complex reason ie previous developers had set some memberships to inherit b>a and others a>b using the same relationship which was kind of nuts. I can probably pass over the query if you want to give it a go. let me know – petednz - fuzion Sep 30 '15 at 9:30
5

I agree with ErikH, but if you don't feel comfortable updating the database, you can...

  • create a new relationship type with the opposite order (b_a vs a_b)

  • do an advanced search, selecting only individuals with that
    relationship

  • select the action to "export" the contacts
  • select two fields (1)the internal contact_id and (2) the relationship type field with the internal_contact_id and export (verify that you did this correctly and the ids match as expected!)

  • use civicrm import to import the csv file to new relationship type created in the first step.

May more steps, but if you don't know how or want to mess with the database, this will work.

  • If your relationship has many to one (i.e one individual is connected to many organizations) then the export will only pick one of the organizations, so you will want to do the advanced search on the organization not the individual. – pkeogan - BackOffice Thinking Sep 29 '15 at 21:13
4

You will probably have to update the tables directly, and as you have figured out you will have to change civicrm_relationship. And of course try in a test environment first.

You will have to make sure the relationship direction is changed in the civicrm interface (civicrm/admin/reltype?reset=1), and then update the civicrm_relationship table in the database.

You can duplicate the civicrm_relationship table as civicrm_relationship2 and then use this second table to swap the a and b columns, in this example for relationship_type_id 5.

UPDATE civicrm_relationship, civicrm_relationship2
SET civicrm_relationship.contact_id_a = civicrm_relationship2.contact_id_b, civicrm_relationship.contact_id_b = civicrm_relationship2.contact_id_a
WHERE civicrm_relationship.id = civicrm_relationship2.id AND civicrm_relationship.relationship_type_id = 5
  • 1
    Pretty sure one of those above should say civicrm_relationship_type – petednz - fuzion Sep 30 '15 at 1:50
  • pretty sure too, sorry about the typo! – ErikH - CiviCooP Oct 2 '15 at 9:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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