1

Got a problem with how someone (I suspect) has gone in to CiviCRM » Administer CiviCRM » Option Groups » Phone Type Options and thought "hey, we don't need a dropdown item of "phone" because that's kinda dumb" and summarily deleted it.

Now all the phone numbers whose type is set to "phone" (landline) are missing that part of the designation when it comes to displaying a contact record as this phone number type is essentially un-hooked (no pun intended) or disconnected (okay, couldn't resist that time). Also, you can't designate it in new/updating phone numbers in the records as the drop-down phone type is no longer available.

A separate (technically savvy this time) staffer thought it would help if he added "phone" in as an option (without taking a briefing from me on the issue) which now has ID #7 associated with it and still hasn't restored the effectively lost data in the slightest.

This, as I suspected, requires a deep dive in to the database to fix the damage and restore the phone type options and values. I've had a look and discovered that phone_type_id is the item we are looking for and that the phone (landlines) were using a value of 1. I can find where there are tables such as civicrm_option_group and civicrm_option_values but my greater sense is that I'm more likely to bollox something at this stage as I really know little of how the database is organised and what everything really means.

Does anyone have some handy instructions to effectively restore the 'phone type options' data to what they originally were? Much appreciated.

Yes, we have backups but it was about a week before the issue was discovered and other updating and transaction work has happened in the interim.

  • Before diving into the database itself, do you know when this happened? Can you restore a back up? Have you simply tried re-adding the option? I will warn you that trying to repair data in MySQL can cause more damage without surgical and experienced precision. How many contacts do you have? It might be safe to set up a dummy version of your CRM from a back up (in a different server) and then export the data and re-import it to update contact records, after setting up the phone options again. – Christia Apr 18 '18 at 21:01
1

If you have logging on then take a look in the log_ tables related to the above - you will then be able to a/ see who did what when, and b/ have a template that may help you to restore the missing options

  • 1
    I've gone in and taken a look. Unfortunately the extended logging wasn't switched on by the person responsible for the install and setup of the CiviCRM at the time. I didn't know there was this extra level of logging or I would have switched it on. Things will definitely change in this regard. – Andrew Smith Apr 13 '18 at 0:17
1

You're on the right track. I'm assuming you currently have a mix of records with 1 and 7 so the steps would be

  1. Take another backup first (as always just in case).
  2. On the admin edit screen for the phone option change the Value field from 7 to 1.
  3. Update the recently entered phones in the database with:

    UPDATE civicrm_phone SET phone_type_id=1 WHERE phone_type_id=7;

  • (sorry for delay) I've gone in and added "phone" with a value of 1, but it hasn't worked in terms of restoring the old data. The phone type doesn't repopulate in the membership record view for records where we know that the number has been entered as a phone (landline not mobile). I've just gone to delete a test phone type and the UI says: "WARNING: Deleting this option will result in the loss of all Phone Type related records which use the option. This may mean the loss of a substantial amount of data, and the action cannot be undone. Do you want to continue?" Looks serious. – Andrew Smith Apr 13 '18 at 0:25
  • Can you look at the output of SELECT ov.value, ov.label, count(p.id) FROM civicrm_phone p LEFT JOIN civicrm_option_group og ON og.name = 'phone_type' LEFT JOIN civicrm_option_value ov ON (ov.option_group_id = og.id AND ov.value = p.phone_type_id) GROUP BY ov.value, ov.label; to get a summary table of what's actually in your database? – Demerit Apr 13 '18 at 13:15
  • The warning on delete that you see is normal. Everybody sees that warning. – Demerit Apr 13 '18 at 13:16

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.