In export for a recurring gift it is not possible to pull out the bank details specific to that gift. You can only pull the name of the bank or all the banks on the constituent’s record. It is not possible to pull the account number and sort code. This is really important when there are bank issues with recurring gifts that need to be sorted out.

Using the VBA module it is possible to output these values. Firstly we set up a gift export with four fields. Import Id and three VBA user fields. These are found in the list of field and can be added as any other field.

In the VBA module we add the following code:

Public Sub GiftBank(orow As IBBExportRow) 
On Error GoTo Err_GiftBank 

    Dim giftId As String
    Dim oGift As CGift
    Dim bankId As String
    Dim oBank As CFinancial2
    If orow.BOF Then
    ElseIf orow.EOF Then
    Else 

        giftId = orow.Field(1)
        Set oGift = New CGift
        oGift.Init SessionContext
        oGift.LoadByField gufIMPORT_ID, giftId 

        bankId = oGift.Fields(GIFT_fld_ConstituentBankId)
        oGift.CloseDown
        Set oGift = Nothing 

        If bankId <> "" Then
             Set oBank = New CFinancial2
             oBank.Init SessionContext
             oBank.Load bankId 

             orow.Field(2) = oBank.Fields(FINANCIAL2_fld_SORT_CODE)
             orow.Field(3) = oBank.Fields(FINANCIAL2_fld_ACCOUNT_NAME)
             orow.Field(4) = oBank.Fields(FINANCIAL2_fld_ACCOUNT_NO)
             oBank.CloseDown 

             Set oBank = Nothing
        End If 

End If 

Cleanup_GiftBank: 

     If Not oGift Is Nothing Then
        oGift.CloseDown
        Set oGift = Nothing
    End If 

     If Not oBank Is Nothing Then
        oBank.CloseDown
        Set bank = Nothing
    End If 

    Exit Sub
Err_GiftBank: 

     MsgBox Err.Description
     GoTo Cleanup_GiftBank 

End Sub

It is important the fields are in the correct order, i.e. there should be three VBA User Fields in columns 2,3 and 4 or the export otherwise this will not work.

There are three stages when the code is entered; at the beginning before any rows are read (orow.BOF), for each row and at the end once all the rows have been read (orow.EOF). We have to capture the oRow.BOF and oRow.EOF so that we don’t try to process a row at this point. Clearly you can do other things at this time such as initialise external databases or write summary data to a file if this were part of your process.

Just because you have a VBA user field in the export does not mean that you have to actually display anything in the field. In other examples I have done some processing at this point based on the values in the export. For example it is possible to mark membership cards as having been issued based on an export that is used to produce the membership welcome pack letters.

No related posts.