Does anyone know what objects exist to allow one to run a VBA loop through all the gifts in a gift batch? We need to dump some information about all the EFT gifts in a batch to a file to send to our bank, as the standard Blackbaud output format doesn't meet our needs. Some sample code would be lurvely, but just the object names would be handy. Regards, David Wanless The Wilderness Society, Australia.

When and how do you want to initiate your loop? You have a BeforePostRecord event in the Active Batch class. This forwards you the actual cGift objects in the batch one by one (no need to loop). Appart from that, I know that batch objects are not exactly supported by BB. William da Silva ---------------- eSimpleIT [Email Removed]

Unfortunately the batch object is not part of the standard supported API. It may be possible to use the dlls associated with the batch objects but this is not supported by Blackbaud and therefore not documented (I look forward to somebody else documenting how to use it!) In the mean time the best way to loop the gifts in a batch is to use a custom where clause to get a collection of gifts that use this batch. Since the batch name is not indexed it was quite slow when I tried this. Dim oGifts As CGifts Dim oGift As CGift Dim sSQL As String sBatchName = "100" Set oGifts = New CGifts sSQL = " BATCH_NUMBER = '" & sBatchName & "'" oGifts.Init REApplication.SessionContext, tvf_Gift_CustomWhereClause, sSQL For Each oGift In oGifts 'Process the gift here oGift.CloseDown Next oGift Hope this helps. Let me know if you need any more explanation. David Zeidman Development http://www.zeidman.info

Dear William and David, Thanks for your responses. David, thanks for the code - it's very clear and was easy to test. However, would your code work only on gifts which have already been committed? Since gifts still sitting in an uncommitted batch are not actually on the constituent's record yet, I'm guessing they're not 'real' CGifts which are query-able in the way you describe. I've run your code and it finds no gifts with the relevant batch number. What we want is to go through the nascent gifts in the batch , before they've been committed, so that we can transmit the file to the bank and then modify or discard the batch before committing if the bank rejects all or part of the file. Essentially we are trying to replicate the existing option on the Tools menu in a gift batch, 'Create EFT Transmission Files', but writing out the data which we want which the standard routine doesn't include (constituent ID and credit card numbers stripped of spaces). William, I've not used the Active Batch class before. I've had a bit of a try today. Where could I find documentation of this class? My RE7Objects.chm file lists it as a property of an REApplicationMT object, but I can't find any documentation on REApplicationMT objects. Any clues on how they work or where to find documentation on them, or any sample code for using them, would be much appreciated. What documents or other resources do you, William and David, use to find VBA objects, sample code etc.? Thanks again both of you for your rapid responses, David.

The ActiveBatch object is part of the VBA module. If you open up the VBA environment (as the Supervisor user) you can see that there are different modules already in place. Search the help file for Active Process Objects and select the entry of the same name. You will see some information about the ActiveBatch object there. However it is better to use the wizard that comes in the VBA environment as it is kept more up to date (there are more events there than in the ActiveBatch object). The help file only describes the events in the ActiveBatch object not those in wizard. In any event I am not sure that it is possible to remove items from the batch. You can make them invalid or you can prevent them from posting but I don't think you can actually remove them from the batch. I have mainly picked up what I know by experimenting with code, using the help file and knowledgebase and speaking with support. David Zeidman Development http://www.zeidman.info

Hi David, Another option which I've used to retrieve non-committed batch information is a read-only SQL query directly on the backend. eg: ---- SELECT dbo.BATCHHEADER.BATCHNUMBER, dbo.RECORDS.CONSTITUENT_ID, dbo.Query_ConstitName(1, dbo.RECORDS.LAST_NAME, dbo.RECORDS.FIRST_NAME, dbo.RECORDS.MIDDLE_NAME, dbo.RECORDS.ORG_NAME, dbo.RECORDS.KEY_INDICATOR, dbo.RECORDS.CONSTITUENT_ID) AS Name, TE_BANKNAME.LONGDESCRIPTION AS BankName, dbo.BANK.BRANCH_NAME, dbo.BANK.SORT_CODE, dbo.CONSTITUENT_BANK.ACCOUNT_NAME, dbo.CONSTITUENT_BANK.ACCOUNT_NO, dbo.BatchGift.Amount, dbo.BatchGift.Ref, dbo.BatchGift.Reference_Number, dbo.BatchGift.TransactionCode, dbo.BatchGift.RowSequence FROM dbo.CONSTITUENT_BANK INNER JOIN dbo.BANK ON dbo.CONSTITUENT_BANK.BRANCH_ID = dbo.BANK.ID INNER JOIN dbo.BatchGift ON dbo.CONSTITUENT_BANK.ID = dbo.BatchGift.Bank INNER JOIN dbo.BATCHHEADER ON dbo.BatchGift.BatchHeaderID = dbo.BATCHHEADER.BATCHID INNER JOIN dbo.RECORDS ON dbo.BatchGift.Constit_Id = dbo.RECORDS.ID INNER JOIN dbo.TABLEENTRIES TE_BANKNAME ON dbo.BANK.BANK = TE_BANKNAME.TABLEENTRIESID WHERE (dbo.BATCHHEADER.BATCHNUMBER = '%') ORDER BY dbo.BatchGift.RowSequence ---- You could create a VBA macro which uses a ADO object to retrieve this information (and any other that is needed) and write to an output file directly. Unfortunately this is one-way only (as under their support agreement, Blackbaud do not allow any manual changes to the the SQL tables), once the bank returns the details you would have to manually update the batch lines 'Rejection Code' to prevent that line from committing. As an aside, I heard a rumour around the European Conference that the Batch modules were going to be exposed sometime this year - obviously the usual mayby/possibly not. Wayne

Hi David, I was going to suggest Wayne's idea. You can easily access the batch data directly through SQL. Adding to Wayne's idea, you could continue your VBA to pull out the "rejection codes" and store them in a table (a new user table in your SQL database for example), and then on the BeforePostRecord event, you can make them exceptions so they do not get posted. PS. If you are adding a table to your SQL DB, create it under a different user that dbo, this way you are sure to avoid conflicts in table names. You will probably get errors in the maintenance scheduled from the BMC, as BB has created their own maintenance procs and do not refer to the object owner. To avoid this, create a new database on the same server and store your "add on" stuff in there. William da Silva ---------------- eSimpleIT [Email Removed]

Thanks for your help everyone. We don't have a beautiful solution but at least we do have a workable one for the time being. We ended up using ActiveBatch and writing a file out as the records were committed. We'd love it if the batch was properly exposed to VBA, and will be hassling Blackbaud to this end, or for them to just fix their credit card output format. :-) Regards, David.

Dear colleagues, Does anyone know whether it is possible to access the 'Include batch for bank depositing?' flag for the current batch in ActiveBatch? We would like to only run our code for producing a credit card output file if this flag is true. (Otherwise it runs for all gift batches, including all our new recurring gift entry batches.) Thanks in advance, David.

Hi David, The flag you are looking for is 'BATCHHEADER_fld_INCLUDEFORDEPOSIT' which is a field of the BatchHeader object. Unfortunately the only events that include the BatchHeader object as a parameter are Batch_BeforeValidateRecord and Batch_BeforeValidateRecord2 which doesn't help you much. If you are able to somehow get the Batch ID or Batchnumber into your sub (I presume you're using Before/AfterPost) then you may be able to load the BatchHeader object directly using Load or LoadByField (although this could be dangerous given that BatchHeader is not officially exposed). Good luck, Wayne