[...] while ago I wrote an article about filtering on gifts. I find myself regularly having to reference the knowledgebase link which itself links to an Access [...]
Eric:
October 16th, 2008 at 8:58 pm
Hi,
I’m new to the RE API and I’m realy stuck here:
I would like to filter constituents. The criteria is the bank account (as in the search screen).
This is what I have:
————
Dim oRECS As CRecords = New CRecords
oRECS.Init(oCONN.SessionContext, TopViewFilter_Record.tvf_record_UseFilterObject)
Dim oCONTS As IBBRecords2 = oRECS
Dim oFLT As CFinRelFilter = oCONTS.FilterObject
With oFLT
— filter criteria should come here, but the only property available is:
.ExcludeIDs
End With
You are using the wrong filter object for constituents. You need to use the CRecordsFilter object. The CFinRelFilter is used when you want to filter a collection of bank relationship records i.e. objects of type CFinancials2. However if you bring up the CRecordsFilter you will not find bank account number there.
What you are going to probably have to do is to use a custom where clause something like this:
Dim oRECS As CRecords = New CRecords
sql = ” ID IN (SELECT CONSTIT_ID FROM CONSTITUENT_BANK WHERE ACCOUNT_NO_RAW = ‘” & myBankAccountNumber & “‘”
oRECS.Init(oCONN.SessionContext, TopViewFilter_Record.tvf_record_CustomWhereClause, sql)
This assumes that the account number does not contain any formatting just numbers otherwise you can use the ACCOUNT_NO field instead.
David
Eric:
October 17th, 2008 at 10:09 pm
Hi David,
Thank you for your quick reply.
As of version 7.81 the account numbers are encrypted in the db. Using the CustomWhereClause queries the raw data. Passing clear text as criteria doesn’t return any records.
Is there a way to encrypt the clear text on the fly so the account number passed in the whereclause matches the raw (encrypted) data in the table ?
Or is there an other way to filter constituents using the account no?
Ahhh, yes of course. I forgot about that one. I am at a loss as to what to suggest. It does seem strange that there is a way of searching for a constituent by account number on the search screen but no obvious method in the API.
Eric:
October 19th, 2008 at 6:57 pm
hi David,
Have you any experience with the FormatField function ?
It has the fmtENCRYPTED and fmtENCRYPTED_DES as EFormatDescriptors constants.
I’m trying to encrypt the account no in the customwhereclause string.
[...] collection it only loads gifts with this fund id. See an article I wrote a while back about this working with filters. David __________________ David Zeidman Zeidman Development http://www.zeidman.info Check out my [...]
ej:
July 26th, 2009 at 12:49 pm
batch of gifts load in api examples ?
Does any one have sample vb api code that loads a string of data that contains 1 or more gifts and create gift records for a consituent(s) ?
There are several examples and some discussion on this site (select the batch tag) and on Blackbus and indeed the API help file comes with quite a good (if albeit a little simplistic) example
RE-Decoded » Blog Archive » Gift Type IDs - A Reference:
September 1st, 2008 at 9:30 am
[...] while ago I wrote an article about filtering on gifts. I find myself regularly having to reference the knowledgebase link which itself links to an Access [...]
Eric:
October 16th, 2008 at 8:58 pm
Hi,
I’m new to the RE API and I’m realy stuck here:
I would like to filter constituents. The criteria is the bank account (as in the search screen).
This is what I have:
————
Dim oRECS As CRecords = New CRecords
oRECS.Init(oCONN.SessionContext, TopViewFilter_Record.tvf_record_UseFilterObject)
Dim oCONTS As IBBRecords2 = oRECS
Dim oFLT As CFinRelFilter = oCONTS.FilterObject
With oFLT
— filter criteria should come here, but the only property available is:
.ExcludeIDs
End With
————–
Thanks in advance.
Eric
David Zeidman:
October 17th, 2008 at 8:29 am
Eric,
You are using the wrong filter object for constituents. You need to use the CRecordsFilter object. The CFinRelFilter is used when you want to filter a collection of bank relationship records i.e. objects of type CFinancials2. However if you bring up the CRecordsFilter you will not find bank account number there.
What you are going to probably have to do is to use a custom where clause something like this:
Dim oRECS As CRecords = New CRecords
sql = ” ID IN (SELECT CONSTIT_ID FROM CONSTITUENT_BANK WHERE ACCOUNT_NO_RAW = ‘” & myBankAccountNumber & “‘”
oRECS.Init(oCONN.SessionContext, TopViewFilter_Record.tvf_record_CustomWhereClause, sql)
This assumes that the account number does not contain any formatting just numbers otherwise you can use the ACCOUNT_NO field instead.
David
Eric:
October 17th, 2008 at 10:09 pm
Hi David,
Thank you for your quick reply.
As of version 7.81 the account numbers are encrypted in the db. Using the CustomWhereClause queries the raw data. Passing clear text as criteria doesn’t return any records.
Is there a way to encrypt the clear text on the fly so the account number passed in the whereclause matches the raw (encrypted) data in the table ?
Or is there an other way to filter constituents using the account no?
Eric
David Zeidman:
October 18th, 2008 at 10:13 pm
Ahhh, yes of course. I forgot about that one. I am at a loss as to what to suggest. It does seem strange that there is a way of searching for a constituent by account number on the search screen but no obvious method in the API.
Eric:
October 19th, 2008 at 6:57 pm
hi David,
Have you any experience with the FormatField function ?
It has the fmtENCRYPTED and fmtENCRYPTED_DES as EFormatDescriptors constants.
I’m trying to encrypt the account no in the customwhereclause string.
Eric
David:
October 23rd, 2008 at 4:37 pm
Sorry, I have not used those before.
David
Retrieving all gifts for a particular fund - Blackbaud User Society - Forum:
May 11th, 2009 at 3:25 pm
[...] collection it only loads gifts with this fund id. See an article I wrote a while back about this working with filters. David __________________ David Zeidman Zeidman Development http://www.zeidman.info Check out my [...]
ej:
July 26th, 2009 at 12:49 pm
batch of gifts load in api examples ?
Does any one have sample vb api code that loads a string of data that contains 1 or more gifts and create gift records for a consituent(s) ?
Thank You
David:
July 28th, 2009 at 4:14 pm
There are several examples and some discussion on this site (select the batch tag) and on Blackbus and indeed the API help file comes with quite a good (if albeit a little simplistic) example
David