I want to bulk update the Employer field for the Individual contacts who have an active relationship of "employee of".
Even though we have many active employee relationships for individuals, the Employer field is blank.
CiviCRM Stack Exchange is a question and answer site for administrators and users of the CiviCRM Constituent Relationship Management software. It only takes a minute to sign up.Sign up to join this community
You can do this directly in the database by running the following update query:
UPDATE civicrm_contact, (civicrm_relationship r LEFT JOIN civicrm_contact c ON c.id = r.contact_id_a) LEFT JOIN civicrm_contact c1 ON c1.id = r.contact_id_b SET c.employer_id = r.contact_id_b, c.organization_name = c1.organization_name WHERE r.relationship_type_id = 5 AND c.contact_type = 'individual' AND c.employer_id IS NULL AND r.is_active = 1;
This will update the employer field for all individual contacts who have an active employee relationship and do not have an employer already set.
This is building on Ben's answer, but should run substantially faster.
UPDATE civicrm_contact employeeContact INNER JOIN civicrm_relationship r ON employeeContact.id = r.contact_id_a INNER JOIN civicrm_contact employerContact ON employerContact.id = r.contact_id_b SET employeeContact.employer_id = r.contact_id_b, employeeContact.organization_name = employerContact.organization_name WHERE r.relationship_type_id = 5 AND employeeContact.contact_type = 'Individual' AND employeeContact.employer_id IS NULL AND r.is_active = 1;
In my use-case I have several qualifying relationships, e.g. "is staff at" and "is intern at", so I changed the
r.relationship_type_id = 5 for
r.relationship_type_id IN (x, y, z). You can get the
x, y, z IDs from the Administer » Customise Data and Screens » Relationship Types table. Just hover your mouse over the View link and look at the
id= URL parameter.
Please note that this code only works if the Individual (employee) is on the A-side of the relationship.
You could use the SQL Tasks extension to automate this.