Sometimes I cannot quite get want I want from Raiser’s Edge using the API. Or if I can it is very slow to get the information. For example when you get a collection of top level records, say constituents, there are filter objects which work well, you can also use the custom where clause which works when there is not a filter parameter. However what do you do when you want a combination of different areas of The Raiser’s Edge all combined into one selection.

I have written custom where clauses that have sub selects that do this. You can write the where clause as:

id in (select constit_id from constituent_bank where account_no = ) & sAccountNo

That works and it will find you the constituent with a certain bank account number. However what if you want constituents who have a bank with a certain zip/post code? Sure you could do a join on the tables but this gets more and more complicated and can impact on performance.

One way around this is to fetch the data directly from the database using ADO. There are advantages and disadvantages to this.

On the downside:

• There may be security concerns in distributing a user name and password (unless you can use Windows authentication).
• If it is not distributed then it will have to be stored in the code which could be problematic when you want to update the password
• There is an added layer of code complexity that you have to managed dealing with ADO connections.

On the upside:

• You will have real flexibility to pull from any table in The Raiser’s Edge and optomise your queries accordingly

There is an alternative to opening your own connection to the database. The CreateDisconnectedADORecordset method will return a recordset for your you based on a SQL string you enter. The code below show how you would get those constituent that have a bank at a certain zip/post code.

    Dim oReservices As New REServices

    Dim oUtilCode As IBBUtilityCode    Dim rs As Recordset

Dim sSQL As String

Dim oConstit As New CRecord

sSQL = "SELECT RECORDS.ID " & _

"FROM CONSTITUENT_BANK INNER JOIN " & _

"BANK ON CONSTITUENT_BANK.BRANCH_ID = BANK.ID INNER JOIN " & _

"RECORDS ON CONSTITUENT_BANK.CONSTIT_ID = RECORDS.ID " & _

"WHERE (BANK.POST_CODE = '94122') "

oReservices.Init SessionContext

    Set oUtilCode = oReservices

'Either with Windows authentication - use your Windows login credentials

Set rs = oUtilCode.CreateDisconnectedADORecordset(sSQL)

'Or with SQL Server authentication - use a SQL Server user name and password

'Set rs = oUtilCode.CreateDisconnectedADORecordset(sSQL, "Username", "Password")

'Then once you have your record set you can loop the record set and load the records

Do While Not rs.EOF

Set oConstit = New CRecord

oConstit.Init SessionContext

oConstit.Load (rs("ID"))

'Do something with the constituent

ProcessConstit oConstit

oConstit.CloseDown

Set oConstit = Nothing

Loop

'and now clean up

If Not rs Is Nothing Then

If rs.State = ADODB.adStateOpen Then

rs.Close

End If

Set rs = Nothing     End If

oReservices.CloseDown   Set oReservices = Nothing

You will have to add the reference: Microsoft ActiveX Data Objects 2.x Libary (x will vary depending on what you currently have installed on your machine).

There is quite a bit more code here than simply using a where clause. However on large databases and with complex queries the savings are considerable. If you were to do the above in a where clause then you would have to write the following:

sSQL = "ID IN (SELECT RECORDS.ID " & _
"FROM CONSTITUENT_BANK INNER JOIN " & _
"BANK ON CONSTITUENT_BANK.BRANCH_ID = BANK.ID INNER JOIN " & _
"RECORDS ON CONSTITUENT_BANK.CONSTIT_ID = RECORDS.ID " & _
"WHERE (BANK.POST_CODE = '94122')) "

This would clearly take a lot longer than using a direct query

Related posts:

1. Custom where clause with Relationships Custom where clauses are  a great way of filtering the...