Author Topic: Current employer not updating; a fix . . . almost (Read 1292 times)

wftl · July 11, 2009, 10:21:40 am

Here's the scoop, in a previous release of the package, imports failed to associate 'Employee of" as 'current employer' in the contact database. This has since been fixed, but for one of my customers, there were a few hundred errant records to fix. Doing them all individually was just a crazy idea so it was off to command line MySQL for the fix.

There are two tables that matter here, civicrm_contact and civicrm_relationship. The record in civicrm_contact has a field called id which matches the contact_id_a field in the civicrm_relationship table. Of note is a field called 'employer_id' in civicrm_contact which matches civicrm_relationship field contact_id_b when "relationship_type = 4". So far so good. Trouble is, the problem records have civicrm_contact field employer_id as NULL even when appearing in the relationship table as having a valid employer relationship.

What you need to do is find those contacts with null employer_id who are nevertheless listed in the civicrm_relationship table and having an employer, then take that employer id (contact_id_b) and update the contact record accordingly. You can do that will the following SQL statement.

Update civicrm_contact c, civicrm_relationship r
set c.employer_id = r.contact_id_b
where c.id = r.contact_id_a
and r.relationship_type_id = 4
and c.employer_id is NULL

Works like a charm. Almost.

If you then go into a contact record and Edit that record, you'll see the current employer listed as expected, but it's not until I click 'Save' that is shows up on the contact summary screen. Obviously, I am still missing a piece. Can anyone help with the final piece to this puzzle?

--
Marcel (Writer and Free Thinker at Large) Gagné
Note: This massagee wos nat speel or gramer-checkered.

Donald Lobo · July 11, 2009, 10:51:33 am

you also need to store the display_name of r.contact_id_b in c.organization_name

we cache this value in that column to optimize other processes (export and potentially search)

lobo

wftl · July 13, 2009, 07:27:47 am

Hey there, Donald.

Thanks for the reply. Looking at the data, this is in fact the issue. I am trying to come up with the right SQL syntax to do this but I'm obviously missing something. Care to assist?

-- Marcel

wftl · July 13, 2009, 10:02:02 am

Okay, I think I've nailed it. I started out by creating a table with nothing but the id and organization name.

create table org_name (organization_name varchar(128), org_id int(10) unsigned);

Then, I populate the table

insert into org_name (org_id,organization_name)
select id,display_name from civicrm_contact
where contact_type = "Organization" and display_name is not NULL;

The final step was to update the civicrm_contact table using information from my new table.

update civicrm_contact c, org_name o
set c.organization_name = o.organization_name
where c.employer_id = o.org_id and c.organization_name is NULL;

I don't know whether my solution was the most elegant, but it appears to have worked well.

--
Marcel (Writer and Free Thinker at Large) Gagné
Note: This massagee wos nat speel or gramer-checkered.

CiviCRM Community Forums (archive)

News:

Author Topic: Current employer not updating; a fix . . . almost (Read 1292 times)

wftl

Current employer not updating; a fix . . . almost

Donald Lobo

Re: Current employer not updating; a fix . . . almost

wftl

Re: Current employer not updating; a fix . . . almost

wftl

Re: Current employer not updating; a fix . . . almost (SOLVED)