7

After changing the display settings (/civicrm/admin/setting/preferences/display) for sort_name or display_name, is there a way to regenerate the sort_names and display_names currently in the database to the newly configured format?

3

As mentionned here :

  • You can export the first name, last name and the internal ID
  • Re-import the data by matching the internal ID

This process should regenerate the sort name and display name based on the current settings.

  • 1
    Used this. It works. – jursulak Sep 9 '16 at 12:41
2

I dont think there is one right now, but the way to do it would probably be:

a. Create a a job for this in api/v3/Job.php

b. Do it similar to the address geocoding job. This is primarily because you could have lots of contacts and hence having an offset and a limit will be useful for some orgs. The job could potentially take a long time

c. There probably are functions in the Contact BAO to generate the sort/display name that you can reuse (some refactoring of the code is probably needed here)

1

If you have any familiarity with sql, the update statement is straight forward. Of course, test first in a test environment.

Here is one that would work for individuals with sort name like Smith, John and display name like John Smith.

update civicrm_contact
set    display_name = concat(first_name, " ", last_name),
       sort_name = concat(last_name, ", ", first_name)
where  contact_type = 'Individual'

If you have a lot of contacts and don't want to update them all at once you could add

and id between 1 and 5000 -- or whatever and increment the numbers manually

You'd also might need a statement for organizations and households, but since there is really only one field to work with for them, I'm not sure any change in sort order or display would change these.

  • Note that this won't work in general where display names (and maybe sort names also) are configurable by the admin via tokens. The display name also includes the prefix, suffix by default – Donald Lobo Apr 30 '15 at 0:13
  • Also note that for some Europeans you have to also add the middle_name! – ErikH - CiviCooP Apr 30 '15 at 6:45
1

We now use the following SQL to accomplish the outcome. Still I think it would be better to include this in the Update Greeting scheduled task

# SET DISPLAYNAME (first,middle,last)
UPDATE civicrm_contact
SET    display_name = LTRIM(RTRIM(CONCAT(first_name, " ", middle_name, " ", last_name))),
       sort_name = LTRIM(RTRIM(CONCAT(last_name, ", ", first_name, " ", middle_name)))
WHERE  contact_type = 'Individual'
AND    first_name IS NOT NULL AND middle_name IS NOT NULL AND last_name IS NOT NULL;

# SET DISPLAYNAME (first,last)  
UPDATE civicrm_contact
SET    display_name = LTRIM(RTRIM(CONCAT(first_name, " ", last_name))),
       sort_name = LTRIM(RTRIM(CONCAT(last_name, ", ", first_name)))
WHERE  contact_type = 'Individual'
AND    first_name IS NOT NULL AND middle_name IS NULL AND last_name IS NOT NULL;
1

I came across this issue today. I needed to update the display name after an import (there were reasons for not using the API to create in this instance).

I am using v5.6.1 (latest at the time of writing) and unfortunately the Export and Re-Import did not work for me as I could not choose to match on Contact_ID. It may well be that the import function has changed since the original question, or I just could not find it...

I ended up using the v4 API instead - if you have a collection of records you want to update, use the Contact::Update call. I wanted to update all of my records, so I just used the GUI to specify Contacts of Type 'Individual' where the contact ID was great than 2 (after the admin).

enter image description here

The only issue I had was a timeout somewhere and so I did them in 10,000 batches. I could have done this in PHP but it was a simple fix without coding.

PLEASE NOTE WELL This works well for basic records - if you have contact subtypes and attached custom data, you may find that these are removed during a basic update

  • I can absolutely still see Contact ID as an import field option on dmaster.demo.civicrm.org – petednz - fuzion Nov 7 '18 at 18:02
  • Many thanks for checking - I went to dmaster and tried again but still couldn't see it... So following your comment I absolutely knew it had to be there, I tried other options and have got to the bottom of the mystery of not being able to see the Contact_ID - I had the default of 'Skip', but you need to have 'Update' or 'No duplicate checking'.... Now works perfectly! – user4850288 Nov 8 '18 at 10:35

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.