Fixing null sort and display names

Question

In one of the systems we support there are some contacts with NULL display and sort names. This means they don't show up in searches. How do we fix these?

Answer 1

Make sure you back up first.

The display name field for the site concerned only contained first_name, last_name and prefixes. I assume in some case you might also want to append suffixes?

trim and replace are used to remove additional whitespace in cases where their was a null first or last name.

UPDATE civicrm_contact AS c LEFT JOIN civicrm_option_value AS ov ON c.prefix_id = ov.value AND ov.option_group_id = 6 SET c.display_name = trim(replace(concat(ov.label, " ", c.first_name, " ", c.last_name), "  ", " ")) WHERE c.contact_type = 'Individual' and c.display_name is null and (c.first_name is not null or c.last_name is not null);

Check if any contacts have no first or last name - but an email.

SELECT e.email FROM civicrm_contact as c LEFT JOIN civicrm_email as e on c.id = e.contact_id and e.is_primary WHERE c.contact_type = 'Individual' and c.display_name is null and c.first_name is null and c.last_name is null and e.email is not null;

in this case there was none so no need to do a further update.

The same for organizations

UPDATE civicrm_contact as c SET c.display_name = c.organization_name WHERE c.contact_type = 'Organization' and c.display_name is null and organization_name is not null;

Then you want to do the sort name

The select

SELECT c.id, CASE when c.last_name is not null and c.first_name is not null then concat(c.last_name, ", ", c.first_name) when c.last_name is null and c.first_name is not null then c.first_name when c.last_name is not null and c.first_name is null then c.last_name when c.last_name is null and c.first_name is null then e.email END from civicrm_contact as c left join civicrm_email as e on c.id = e.contact_id and e.is_primary where c.contact_type = 'Individual' and c.sort_name is null and (c.first_name is not null or c.last_name is not null or e.email is not null);

and the update

 UPDATE civicrm_contact as c set c.sort_name = CASE when c.last_name is not null and c.first_name is not null then concat(c.last_name, ", ", c.first_name) when c.last_name is null and c.first_name is not null then c.first_name when c.last_name is not null and c.first_name is null then c.last_name END where c.contact_type = 'Individual' and c.sort_name is null and (c.first_name is not null or c.last_name is not null);

Here I used case to resolve issue with multiple cases with both names, only first name only last name.

And finally organizations

SELECT count(c.id) from civicrm_contact as c where c.contact_type = 'Organization' and c.sort_name = null;