Renaming phone types in Raiser's Edge - and finding queries that use them

When you integrate Raiser's Edge with Outlook you automatically get several new "phone" types such as: E-mail, E-mail 2, E-mail 3 and Business, Business 2, Business Fax.

If, like us, you already have some phone types then that's annoying. I've found that you can't change the mapping of outlook to RE types, so if you don't want too many phone types you have to convert your existing ones to the Outlook ones. Mapping them is a future suggestion. (Here's something I found recently: if something in the Blackbaud knowledgebase starts with "unable to..." then it will always list something that's a suggestion.)

Converting the phone types on the records is fairly straightforward using "Table Cleanup". The hard thing is (and this applies to any field, not just phone types) finding which queries use the types, so that you can modify them to use the Outlook types. However I've found a way:

First of all you need the code of the thing you're looking for. We have an email type "Email - private" and I want to convert that to "E-mail". So first of all you fire up the SQL query editor and put this in:

SELECT TABLEENTRIESID
FROM TABLEENTRIES
WHERE LONGDESCRIPTION = 'your description'

Make a note of that number - you'll need it.

Then you want to find all your queries that use that field. Now the phone type, like some other fields, can be used in two ways. You could have criteria such as

Phone type equal Email - private

or

Email - private Number not blank

Here's how to find the query names that use the field on the Criteria tab:

SELECT
Q2.NAME "QUERY NAME"
FROM QUERIES2 Q2,
QFILTERFIELDS QFF
left join QFILTERVALUES QFV on QFV.QFILTERFIELDSID = QFF.QFILTERFIELDSID
WHERE
QFF.QUERIESID = Q2.QUERIESID
and (instance = 40nnnn or numericvalue = nnnn)

I've used nnnn to represent the number you found out in the first query statement above. The 40nnnnfor us would be 403027. For some reason the instance has a 40 at the front.

Here's how to find query names that use the field on the output tab:

SELECT
Q2.NAME "QUERY NAME"
FROM
QSELECTFIELDS QSF,
QUERIES2 Q2
WHERE
QSF.QUERIESID = Q2.QUERIESID
and (instance = 40nnnn )
order by q2.name