Hello, Tessiturians!
I have been given a long spreadsheet of constituent number and telephone number known to be disconnected or wrong. Our telemarketing group ran their leads through a 3rd party data warehouse to improve and speed their efforts, and this list is a by-product. Clearly I’d like to update our constituent files as quickly as possible, but doing this by hand will be slow and prone to error. I’m looking for a “scrubber” utility that can find the constituent, then the offending phone number (however many times found) and delete this data. The utility should create some sort of audit trail so that I’ll know if and where there might be errors. Deleting the phone number will in most cases cause the GET-FIX: Phone! flag to be shown in the Constituents header for possible manual additions by our phone staff. Ultimately we will ask MBS to append phone numbers where nonexistant when we next send our file out for NCOA. If such a thing exists for telephone numbers, can it or something similar work for email addresses as well? Anyone have or know of such a thing? I’m somewhat afraid that it does exist but that our limited technological expertise here will prevent us from using it!
Jon
I’ve never done this, but all phone numbers live in T_PHONE, so it would be pretty easy to write queries deleting all iterations of a specific phone number per customer. Perhaps, though, you could consider creating an archive phone type and instead of deleting the numbers, changing all of them to the archive type. This would allow you to ignore the numbers for telemarketing, but still allow you to find the account when searching by phone number.
Andrea Crain, the DBA at Chicago Shakespeare Theater, shared a nice trick some time ago (in 2005) where you can use Excel to build a series of statements based on values in the table. So, if the customer number is in A1 and the phone number in B1, you could type the following into C1:
="delete T_PHONE where customer_no = "&A1&" and phone = '"&B1&"'"
Hit Enter and the formula produces the following (assuming a customer number of 1111111 and a phone number of 3058541643):
delete T_PHONE where customer_no = 1111111 and phone = '3058541643'
You copy the formula in C1 down through all rows where there is customer/phone data, then copy the column of delete statements into a query window and hit Execute. You’d first have to strip the formatting from the phone numbers so that they appear as a string of digits with no hyphens, periods, spaces, or other formatting.
Of course, you do this in Test first, to make sure that you get the results you want.
The audit trail already exists; any changes you make to phone numbers will show up on the Audit screen under the Transactions tab. If you delete a phone number via Query Analyzer, the audit shows the user id as “dbo.”
Lucie