4

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.

2

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.

| improve this answer | |
1

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.

| improve this answer | |

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.