Ran direct SQL update to bulk update contact names, changes not appearing until editing and saving

Clash Royale CLAN TAG#URR8PPP

up vote
1
down vote

favorite

I'm finishing up a migration to CiviCRM from a homegrown Python application I wrote, and without going into too much detail as to the why, I needed to do a bulk update of all the contact name fields in the civicrm_contact table using a spreadsheet dump from the old application.

I wrote a script that loops over the rows in the spreadsheet and runs an update statement on each record in civicrm_contact table using the external_identifier field to match.

This all seems to have run fine, but when I go to the contact in CiviCRM the old values are still there. However, when I click at the top of the contact screen to put the name fields into edit mode, the new values are in the form fields, and when I hit save the new values appear.

Is there some caching going on such that this will fix itself, or does updating the name fields directly as I did have some other implications/consequences I should be aware of? Thanks.

contacts database sql

share|improve this question

asked 3 hours ago

Matt Woodward

848

add a comment | 

up vote
1
down vote

favorite

I'm finishing up a migration to CiviCRM from a homegrown Python application I wrote, and without going into too much detail as to the why, I needed to do a bulk update of all the contact name fields in the civicrm_contact table using a spreadsheet dump from the old application.

I wrote a script that loops over the rows in the spreadsheet and runs an update statement on each record in civicrm_contact table using the external_identifier field to match.

This all seems to have run fine, but when I go to the contact in CiviCRM the old values are still there. However, when I click at the top of the contact screen to put the name fields into edit mode, the new values are in the form fields, and when I hit save the new values appear.

Is there some caching going on such that this will fix itself, or does updating the name fields directly as I did have some other implications/consequences I should be aware of? Thanks.

contacts database sql

share|improve this question

asked 3 hours ago

Matt Woodward

848

add a comment | 

up vote
1
down vote

favorite

up vote
1
down vote

favorite

I'm finishing up a migration to CiviCRM from a homegrown Python application I wrote, and without going into too much detail as to the why, I needed to do a bulk update of all the contact name fields in the civicrm_contact table using a spreadsheet dump from the old application.

I wrote a script that loops over the rows in the spreadsheet and runs an update statement on each record in civicrm_contact table using the external_identifier field to match.

This all seems to have run fine, but when I go to the contact in CiviCRM the old values are still there. However, when I click at the top of the contact screen to put the name fields into edit mode, the new values are in the form fields, and when I hit save the new values appear.

Is there some caching going on such that this will fix itself, or does updating the name fields directly as I did have some other implications/consequences I should be aware of? Thanks.

contacts database sql

share|improve this question

asked 3 hours ago

Matt Woodward

848

I'm finishing up a migration to CiviCRM from a homegrown Python application I wrote, and without going into too much detail as to the why, I needed to do a bulk update of all the contact name fields in the civicrm_contact table using a spreadsheet dump from the old application.

I wrote a script that loops over the rows in the spreadsheet and runs an update statement on each record in civicrm_contact table using the external_identifier field to match.

This all seems to have run fine, but when I go to the contact in CiviCRM the old values are still there. However, when I click at the top of the contact screen to put the name fields into edit mode, the new values are in the form fields, and when I hit save the new values appear.

Is there some caching going on such that this will fix itself, or does updating the name fields directly as I did have some other implications/consequences I should be aware of? Thanks.

contacts database sql

contacts database sql

share|improve this question

asked 3 hours ago

Matt Woodward

848

share|improve this question

asked 3 hours ago

Matt Woodward

848

share|improve this question

share|improve this question

asked 3 hours ago

Matt Woodward

848

asked 3 hours ago

Matt Woodward

848

asked 3 hours ago

Matt Woodward

848

848

add a comment | 

add a comment | 

1 Answer
1

active

oldest

votes

up vote
2
down vote



accepted

It sounds like you imported the first_name and last_name values - but the value at the top of the record is the display_name. You have two options:

• Use your SQL script to update the display_name accordingly.


• Export ONLY the contact_id of the affected records (or all records). Reimport the contact ID via the "Import Contacts" functionality. All records will be resaved when you import the contact ID and the display_name is recalculated on save.

Incidentally, this is a good if fairly harmless example of why directly updating the database via SQL is usually a bad idea. If this were imported via the API, the display name would have been recalculated. In this case, there was no data loss - but if you had tried updating, e.g. donation amounts, the lack of corresponding changes to your line item and financial transaction tables would be difficult to recover from!

share|improve this answer

answered 1 hour ago

Jon G - Megaphone Tech

24.6k11766

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thank you! This is great information to have. I'll look into the API more since i have some regular updating I need to do from an outside source. Since I'm used to dealing with databases directly I figured I'd do it this way so it's great to know the consequences of this and learn more about how all the pieces fit together. I really appreciate the information.
  – Matt Woodward
  1 hour ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "605"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
noCode: true, onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcivicrm.stackexchange.com%2fquestions%2f27180%2fran-direct-sql-update-to-bulk-update-contact-names-changes-not-appearing-until%23new-answer', 'question_page');

);

Post as a guest

Name Email

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
2
down vote



accepted

It sounds like you imported the first_name and last_name values - but the value at the top of the record is the display_name. You have two options:

• Use your SQL script to update the display_name accordingly.


• Export ONLY the contact_id of the affected records (or all records). Reimport the contact ID via the "Import Contacts" functionality. All records will be resaved when you import the contact ID and the display_name is recalculated on save.

Incidentally, this is a good if fairly harmless example of why directly updating the database via SQL is usually a bad idea. If this were imported via the API, the display name would have been recalculated. In this case, there was no data loss - but if you had tried updating, e.g. donation amounts, the lack of corresponding changes to your line item and financial transaction tables would be difficult to recover from!

share|improve this answer

answered 1 hour ago

Jon G - Megaphone Tech

24.6k11766

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thank you! This is great information to have. I'll look into the API more since i have some regular updating I need to do from an outside source. Since I'm used to dealing with databases directly I figured I'd do it this way so it's great to know the consequences of this and learn more about how all the pieces fit together. I really appreciate the information.
  – Matt Woodward
  1 hour ago
  

  
  
  
  

add a comment | 

up vote
2
down vote



accepted

It sounds like you imported the first_name and last_name values - but the value at the top of the record is the display_name. You have two options:

• Use your SQL script to update the display_name accordingly.


• Export ONLY the contact_id of the affected records (or all records). Reimport the contact ID via the "Import Contacts" functionality. All records will be resaved when you import the contact ID and the display_name is recalculated on save.

Incidentally, this is a good if fairly harmless example of why directly updating the database via SQL is usually a bad idea. If this were imported via the API, the display name would have been recalculated. In this case, there was no data loss - but if you had tried updating, e.g. donation amounts, the lack of corresponding changes to your line item and financial transaction tables would be difficult to recover from!

share|improve this answer

answered 1 hour ago

Jon G - Megaphone Tech

24.6k11766

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thank you! This is great information to have. I'll look into the API more since i have some regular updating I need to do from an outside source. Since I'm used to dealing with databases directly I figured I'd do it this way so it's great to know the consequences of this and learn more about how all the pieces fit together. I really appreciate the information.
  – Matt Woodward
  1 hour ago
  

  
  
  
  

add a comment | 

up vote
2
down vote



accepted

up vote
2
down vote



accepted

It sounds like you imported the first_name and last_name values - but the value at the top of the record is the display_name. You have two options:

• Use your SQL script to update the display_name accordingly.


• Export ONLY the contact_id of the affected records (or all records). Reimport the contact ID via the "Import Contacts" functionality. All records will be resaved when you import the contact ID and the display_name is recalculated on save.

Incidentally, this is a good if fairly harmless example of why directly updating the database via SQL is usually a bad idea. If this were imported via the API, the display name would have been recalculated. In this case, there was no data loss - but if you had tried updating, e.g. donation amounts, the lack of corresponding changes to your line item and financial transaction tables would be difficult to recover from!

share|improve this answer

answered 1 hour ago

Jon G - Megaphone Tech

24.6k11766

It sounds like you imported the first_name and last_name values - but the value at the top of the record is the display_name. You have two options:

• Use your SQL script to update the display_name accordingly.


• Export ONLY the contact_id of the affected records (or all records). Reimport the contact ID via the "Import Contacts" functionality. All records will be resaved when you import the contact ID and the display_name is recalculated on save.

Incidentally, this is a good if fairly harmless example of why directly updating the database via SQL is usually a bad idea. If this were imported via the API, the display name would have been recalculated. In this case, there was no data loss - but if you had tried updating, e.g. donation amounts, the lack of corresponding changes to your line item and financial transaction tables would be difficult to recover from!

share|improve this answer

answered 1 hour ago

Jon G - Megaphone Tech

24.6k11766

share|improve this answer

share|improve this answer

answered 1 hour ago

Jon G - Megaphone Tech

24.6k11766

answered 1 hour ago

Jon G - Megaphone Tech

24.6k11766

answered 1 hour ago

Jon G - Megaphone Tech

24.6k11766

24.6k11766

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thank you! This is great information to have. I'll look into the API more since i have some regular updating I need to do from an outside source. Since I'm used to dealing with databases directly I figured I'd do it this way so it's great to know the consequences of this and learn more about how all the pieces fit together. I really appreciate the information.
  – Matt Woodward
  1 hour ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thank you! This is great information to have. I'll look into the API more since i have some regular updating I need to do from an outside source. Since I'm used to dealing with databases directly I figured I'd do it this way so it's great to know the consequences of this and learn more about how all the pieces fit together. I really appreciate the information.
  – Matt Woodward
  1 hour ago
  

  
  
  
  

Thank you! This is great information to have. I'll look into the API more since i have some regular updating I need to do from an outside source. Since I'm used to dealing with databases directly I figured I'd do it this way so it's great to know the consequences of this and learn more about how all the pieces fit together. I really appreciate the information.
– Matt Woodward
1 hour ago

Thank you! This is great information to have. I'll look into the API more since i have some regular updating I need to do from an outside source. Since I'm used to dealing with databases directly I figured I'd do it this way so it's great to know the consequences of this and learn more about how all the pieces fit together. I really appreciate the information.
– Matt Woodward
1 hour ago

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcivicrm.stackexchange.com%2fquestions%2f27180%2fran-direct-sql-update-to-bulk-update-contact-names-changes-not-appearing-until%23new-answer', 'question_page');

);

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Name Email

Name

Email

Name Email

Name

Email