Deleting a specific phone number from hundreds of records

Daniel Noga 2

I have an interesting data cleanup pickle (then again, when don't I?) involving phone numbers.



At some point prior to my working here, for reasons I do not understand, somebody started entering phone numbers into RE: as "000-000-0000." Placeholder for a blank number is my best guess, but I don't get why they wouldn't have just been deleted. Now I a running the Manage Duplicate Constituents Plugin and I'm getting lots of silly matches to weed through because of this.  Anyway...



There are 278 of these in the main Constituent records (haven't even checked the relationship records yet). I've looked around the knowledgebase for ways to tackle this, but most of what I find involves using Global Change, and that won't work. I've been down this road before, so before I waste a day trying the two or three approaches I have in mind only to find that none of them work due to a single minute detail, I thought I would bug you all with questions instead.



Approach 1: Global Change/Delete: I don't think Global Delete will work, because I am not going after a Phone Type. I have the unhappy fortune of targeting an exact string of characters that's been peppered throughout the system. However, it might be possible to use Global Change to assign a temporary Phone Type to all of these records, then go this route. Can I do that?



Approach 2: Change through Import / Delete Phones Plugin

I hear that the Delete Phones Plugin is like a neutron bomb for blank phone records in our systems. Is this correct? The documentation says, "The Delete Phones plug-in globally deletes phone numbers by phone type or deletes phone types for which there is no corresponding phone number." That doesn't sound like actually weeding out individual records, though, but just eliminating unused phone types. But if the tool can handle the individual blank records, then I can use import, put a caret ^ into the fields with all the 000-000-0000 phone numbers (I shake my head every time I think about this, I swear) to render them blank, then get rid of the blank numbers.



Or, I could spend an hour in Zen mode deleting these one by one from a Query output window.



 

Jen Claudy

It's been a while since I used the Delete Phones Plug In, but I have used it (for a Phone Type, not null field).  I exported data, I think probably from a Query, used Access (but Excel would probably work just as well) to find the numbers I wanted to delete, changed the Phone Type to "DELETE", imported back to RE, and then run the Plug In.  It deletes the phone records and then I believe you go delete that Phone Type from Config when you're done with it (pretty sure on this, because I'm sure I did this in phases for a couple of different scenarios to clean up).  I think there's a Phone Import ID to use...but when I did this last, Phones were on Addresses.



I suggest creating the DELETE Phone Type, manually change a couple of records, and run the Plug In, then, once you have an understanding of how it works, you can do the rest of the project.  As always, be sure you have a good backup of your database (and it wouldn't hurt to take screenshots of other phones on the test records, just in case).  From what I remember, the Plug In tells you it might take a long time, but I don't think that was the case.

Anne Herbert

I just did this recently in cleaning our database. I export all the phones and their phone type (including the phone and addr import id's if you are pre 7.94) then import back in a new type called REMOVE or DELETEME and then use the Delete Phone plugin to get rid of the REMOVE/DELETEME type you just imported.  I use excel to sort by and find the invalid numbers. Works perfectly, since you have to delete by phone type.

Daniel Noga 2

Thank you both kindly! Importing can be a tricky area, and it is very helpful to have the voice of experience to help in planning.



I've changed the Phone Type for the errant numbers to DELETE and blanked them out, so at least now the numbers won't be causing records to pull into the Duplicate Constituent Management Tool by accident. Tomorrow, after IT has installed the Delete Phone Numbers plugin, I'll finish the rest.



 

Candace Chesler

I just cleaned up email addresses last week using this same process Jennifer descrbed.  Works very well.  We are running RE 7.94 and have fewer than 9,000 records with email addresses.  Went quickly.

Good luck!

Jen Claudy

Because it may be relevant...now or in the near future.  This is from the RE:7.95 Release Notes that come up in RE when you first sign in after upgrading:

Import

• Existing “Constituent Phone” imports have been renamed to “Constituent Phone Legacy.” You can still run them, but you cannot create new “Constituent Phone Legacy” imports. Instead, a new “Constituent Phone” import is now available. The new import does not require address import ID, and the Phone is primary? and Phone inactive fields have been added to all applicable import types.

Daniel Noga 2

Jennifer Claudy:

Because it may be relevant...now or in the near future.  This is from the RE:7.95 Release Notes that come up in RE when you first sign in after upgrading:

Import

• Existing “Constituent Phone” imports have been renamed to “Constituent Phone Legacy.” You can still run them, but you cannot create new “Constituent Phone Legacy” imports. Instead, a new “Constituent Phone” import is now available. The new import does not require address import ID, and the Phone is primary? and Phone inactive fields have been added to all applicable import types.

 

Thanks Jennifer! For now, I am stuck with 7.93 and have heard that we won't be upgrading because (so I've been told, but am not certain) 7.94 and later do not support PE integration and we use that. I wonder if the changes to the way phone numbers are handled had anything to do with that? The last I heard, IT wants to resolve this by converting from RE:, PE and several other programs to Altru. The future is uncertain, and a potential conversion means data cleanup is a top priority.

Jen Claudy

If you're stuck with 7.93, then this doesn't affect you.  But it is because of the changes to how Phones are stored in RE.