Hi, Just got a trick from BB on a case I had opened. This might be common knowledge, but it was news to me, so I thought I would share... You can open a set of records based on an SQL query (CustomWhereClause). Here is the sample BB provided me... Public Sub LoopConstituents() Dim oRecs As CRecords Set oRecs = New CRecords Dim oRec As CRecord Dim sSQL As String sSQL = "ID IN (SELECT RECORDSID FROM PARTICIPANTS WHERE PARTICIPATION = 1241)" oRecs.Init REApplication.SessionContext, tvf_record_CustomWhereClause, sSQL For Each oRec In oRecs msgbox oRec.Fields (RECORDS_fld_FULL_NAME ) Next oRec oRecs.CloseDown Set oRecs = Nothing Set oRec = Nothing End Sub William da Silva ---------------- eSimpleIT [Email Removed]

I've found this very useful as well; the select statement runs against the RECORDS table in the database so any field in that table can be used to filter records. One issue to note however in this as well as all of the examples Blackbaud supplies is that if you are dealing with a reasonable sized dataset (say 10,000 records or more) after your select statement you will have memory issues. The "for each .. in" syntax creates a new cRecord object in memory for each record rather than recycling the old. Because each open cRecord uses up ~50k of memory looping through 20,000 records can take up over a gig of system memory. The solution is simple, just make sure that you closedown the cRecord before moving on. Private Sub ShowLargeLoopSyntax() Dim oRecord As CRecord Dim oRecords As CRecords Dim MatchConstit As Float Dim sTemp As String sTemp = "Birthplace is null" Set oRecords = New CRecords oRecords.Init REAPI.SessionContext, tvf_record_CustomWhereClause, sTemp, bReadOnly:=False If oRecords.Count > 0 Then For Each oRecord In oRecords oRecord.Fields(RECORDS_fld_PRIMARY_SALUTATION) = Name.Text oRecord.Save oRecord.CloseDown Next End If oRecords.CloseDown Set oRecords = Nothing Set oRecord = Nothing End Sub Hope this helps

Memory problems can prove annoying. To add to Gabe's comment, you can actually use more than just the fields in the records table. From what I understand, the CustomWhereClause is just that, a custom where clause. Imagine that the base for the whole .init with the custom where is basically a ... SELECT * FROM Records Add to that a WHERE with whatever you want. Since the FROM clause only includes the Records table, you can specify those fields directly (like your example "Birthplace is null" which would result in SELECT * FROM Records WHERE Birthplace is null If you want to use an other table, place it in an IN clause. For example, "ID IN (SELECT RECORDSID FROM ConstituentCodes WHERE ConstituentCode = 1234)" (field names might be wrong, I don't feel like looking up the real names...) This would create... SELECT * FROM Records WHERE ID IN (SELECT RECORDSID FROM ConstituentCodes WHERE ConstituentCode = 1234) I have not tried with other objects, but I assume it works the same (cGift, cAction...) William da Silva ---------------- eSimpleIT [Email Removed]

Using a sub select in the SQL Where Clause is very useful and can be used in most of the other top level records. However to my annoyance it cannot be used for participants!! Don't ask me why but it was not created for that object (at least not in the version I am using) Also you have to be careful with subselects as the performance of the load can be severely affected if you are sub selecting on a large table with criteria from a non-indexed field! In these cases I will often just use a direct SQL query into the database to retrieve an ADODB recordset and then load the record directly. The SQL is much more efficient but the code is obviously more complicated. David Zeidman Development http://www.zeidman.info