Changing Phone Types in Raiser's Edge, part 2

Further to my post on changing Phone Types in Raiser's Edge:
I found that Table Cleanup would fail even if phone types weren't duplicated for a given address. So I ended up changing them with various global changes, or creating an import file, changing and reimporting.

Further to this:
I found that Table Cleanup would fail even if phone types weren't duplicated for a given address. So I ended up changing them with various global changes, or creating an import file, changing and reimporting.

I also had problems with queries not finding duplicates. If I looked for

Phone Type one of "Old email 1", "Old email2"

it would fail to find some duplicates. I had to add

OR Phone Type one of "Old email 2", "Old email1"

i.e. put them in a different order

Messy data

I'm in the middle of a project to take data from excel spreadsheets and word documents and get them into a database. As you might expect the data is in a bit of a mess. By mess, I mean that it doesn't obey database rules.

(As an aside I have heard Lotus Notes rejected because it's underlying database wasn't relational. The Lotus Notes format was nearer the way people create data than relational databases - missing values, repeated values.)

For example, one rule of databases is that there is only one copy of a thing. Unless you create a second copy (so that it's handy, but you know you're doing that, and if you need to change it you change it in both places).

In these spreadsheets we have people's names. Sometimes we have their name more than once. But are they the same person? If they are we should only have them in the database once, that's the rule. However we just can't tell.

One thing that makes this harder is that people's names are so slippery. They get abbreviated, they get misspelled. I tweeted today

paulmorriss: is wishing everyone had numbers rather than names. Computers could cope a lot better then.

Two people replied

I am not a number ... I am a free man!

Here's a new quote:

The price of freedom is eternal problems with names

In this particular case I'm trying to match a name in a spreadsheet column with a name included in the filename of a word document. E.g. database has
Joe Bloggs
filename has
Joe Bloggs report.doc

I get a match 70% of the time, which is quite good considering how many ways it could go wrong, e.g. database can have
Josephine (Jo) Bloggs
Joe Bloggs (useful comment)

Filenames can have
Jo report.doc
J. Bloggs report
Joe Blogs report

I could have done all the matching by hand, rather than trying to program it, but given the volume of data that would have been extremely tedious.

Getting going with BT's Web21C

In which our hero spends days downloading and fiddling with stuff just to send a text message.


I've spent a couple of days getting going with BT's Web21C SDK. I started on Windows trying to get Python going, switching to Ubuntu a couple of times in the middle. In the end I gave up, and switched to PHP on Ubuntu. The latter hardly took any time to get working. I had planned to document how I got the python working from my notes, but seeing as I didn't I've just kept my notes here in case it helps someone else. I couldn't believe the hoops I had to go through, modifying source code for python modules, installing lots and lots of software.

Using python:
download python tools
python setup.py
See EasyInstall
Watch where it says it's install it to then add to path:
set path=%path%;c:\program files\python\scripts

easy_install zsi

to run actual programmodify sample, remove previous sys.path.insert and put this before import service
sys.path.insert(0, '../build/lib/btsdk')
says it needs other stuff:

Download (non-light) SSL from http://www.slproweb.com/products/Win32OpenSSL.html
and MSVC runtime from
http://www.microsoft.com/downloads/details.aspx?familyid=9B2DA534-3E03-4391-8A4D-074B9F2BC1BF&displaylang=en

and then run certs-utility.bat

Gave up on WIndows ssl ( openssl ordinal 3288 error message), used Ubuntu.
Then did last step in Python, but need to install M2Crypto,

for which you need .net framework sdk 1.1
and swig (swig.org)
add swig dir to path
copy open ssl includes to python include

error: Setup script exited with error: Python was built with version 7.1 of Visual Studio, and extensions need to be built with the same version of the compiler
, but it isn't installed.
AARGH!
see http://peak.telecommunity.com/DevCenter/PackageNotes
create distutils.cfg file

command gcc failed

(diverted to linux but that failed because couldn't find pyconfig.h when trying to get ZSI installed - which works on windows)

Off to http://sourceware.org/cygwin/
guess that I just need the "developer" package, though could probably do with less as that took ages to install.
added c:\cygwin\bin to the path
replace regexp in distutils\version.py line 100 with this
version_re = re.compile(r'(\d+) \. (\d+) (\. (\d+))? ([ab](\d+))?',
re.VERBOSE)

copy openssl\lib\*.lib to python\scripts

gave up
found this:
(this may be helpful http://www.gooli.org/blog/building-m2crypto-on-windows/)
command given didn't work, but used this
python setup.py build_ext -I\localcopyofincludeDirectoryBecauseBuildingOnANetworkDriveAndYouCan'tSpecifyADifferent drive letter --openssl=c:\progra~1\openssl

python setup.py install (bdist_wininst thought it wasn't running on win32)



copy m2crypto-0.18.2\build\lib.cygwin-1.5.25-i686-2.5\m2crypto to site-packages under python
write program instead of running last line of setup batch file:
import pkg_resources
pkg_resources.require("zsi")
from btsdk import utility
utility.save_key_password()

cygwin complains about crypto dll even though it's there
add c:\cygwin\bin to path
complains about incompatible cygwin1.dll

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

The BT API and the cheeseshop

BT has an API. I like this sentence in the documentation:

If you look at the readme that comes with the Python SDK, you'll also notice a cascading dependency from ZSI to PyXML, which is available from the cheeseshop.

A wiki of Christian networks

More of a bookmark for me, but in case it's useful for you:
A wiki of Christian networks.

Cows and Colonialism

I recently heard a paper which had been delivered previously at the Global Connections Thinking Mission Forum

Here is a summary of the paper given by Rogelio Prieto (summarised by an unknown author), along with another one given by someone else. It's a sustained metaphor on how "the British church (standing here for all Western ‘sending’ countries and their mission agencies) is a COW".

It's best to read the summary, rather than hear my summary of the summary, but here are some quotes to get you interested. (Bear in mind these are the unknown author's quotes from the original talks.)

Indeed if you are in need and as long as you look from a distance, the British church looks very cow-like – big and fat and promising, with irresistibly tender eyes.

If you’re in need and find a cow, you don’t study it...you milk it.