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?
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.
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)
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.
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;
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).
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