Important Notes:

  • Once SEPA is enabled in The Raiser's Edge, it cannot be disabled.
  • SEPA files are processed using SEPA compliant software.  Contact your bank for further information and assistance.
  • SEPA does not affect United Kingdom at this time. Being GBP based only, UK means of payment are not impacted by SEPA.
  • See SEPA key terms for more information on acronyms used in SEPA documentation.



The mandate ID will be automatically generated when adding new recurring gifts and pledges with the Pay Method of Direct Debit where the mandate date is blank.
This process is for the steps to export those recurring gifts and pledges where the mandate ID is currently blank and then generate a unique mandate ID for each of them and import back into Raiser’s Edge to populate the field.

The Raiser’s Edge requires that each Mandate ID is unique, in order to help you find any specific Direct Debit in this system. The mandate ID that Raiser’s Edge will automatically generate is formatted as followed, to ensure it is unique.
{constituent system ID}M{gift system ID}

Alternatively you may prefer to setup a different format for the mandate IDs, for example an unique number, starting at 1 or some other number , for example Constituent ID.
However, if using this unique number option, you should check the mandate IDs that are currently present on existing direct debits to ensure the numbers you intend to setup are not already in use.
An exception will occur when importing the Mandate ID if it is already in use.
For example, if you have an existing direct debit where the mandate ID is already set as 123, then you should not use that same number in this process.
You may want to consider prefixing your numbering sequence with M or DD to ensure it is unique.


To populate the mandate ID
Before updating records in Raiser’s Edge via Import please ensure you have a backup of the database
 

  1. Create a gift query with the criteria
    Gift Type one of Pledge, Recurring Gift
    AND Gift Status one of Active, Held
    AND Pay Method equals Direct Debit
    AND  Mandate ID blank.

    This last piece of criteria is used to ensure we do not replace the Mandate ID you have already setup for existing recurring gifts and pledges as the existing entry will be honoured and can continue.
    Therefore you only need to populate the mandate ID for those where it is currently blank.

    On the query output include fields that you would find helpful when checking the results are correct.
    Run the query and once you are happy with the results, save and close the query

     
  2. In Export, choose New Export – this will be a Gift export and select Excel format – for example Excel 97 – 2000.
    On the General tab click Include, selected records, and pick up the query created in step 1.
    On the Output tab include the field(s)
    1. Import ID from the list of fields that currently show – this is the import ID of the gift to be updated.
    2. If you intend setting up the Mandate ID in the same format as used by Raiser’s Edge then also include
      1. System Record ID from the list of fields that currently show – this is for the gift
      2. Constituents, Constituent Information, System Record ID – this is for the constituent
           Save the export and click Export Now.

 
  1. Open the exported file in Excel.
    If you intend creating your own unique Mandate ID
    The file will only contain the Gift Import ID.
    In the next column enter the header GFRefNum – this is the Raiser’s Edge header for the Mandate ID field.
    Then enter a unique value in each of the rows for the mandate IDs.
    Select File, Save AS and save as a CSV (comma separated values) and then close the file in Excel.

    If you want to use the system record IDs exported from Raiser’s Edge for the Mandate ID
    The file should include Gift Import ID, followed by gift system record ID followed by constituent system record ID.  The gift fields are listed first, because this was exported via a Gift export.
    If additional fields included then please copy these 3 columns to a new spread sheet.

    We will be updating the Mandate ID with
    {constituent system ID}M{gift system ID}
    However the Mandate ID has a maximum length of 20 characters and as this combination me exceed 20 characters the system IDs should be truncated.
     
    • In column D, which should be the 1st blank column position your cursor in row 2, which is the 1st data row
      Click on the formula tab and select Insert Function and search for the function RIGHT and when this function is selected click OK
      In the box that pops up set Text to C2 (the constituent system ID) and set the number of characters to 9 and click OK.
      If any of the constituent system IDs in column C exceed 9 characters then this will trim off the characters from the left.
       
    • Click into row 2 of the next blank column (column E) and repeat the RIGHT function with B2 and the number of characters this time is 10.
       
    • Click into row 2 of the next blank column (column F), Insert Function and search for CONCATENATE.
      In the Function Arguments screen that pops up set Text 1 to D2, Text 2 to M and Text 3 to E2 and click OK
       
    • Select columns D, E and F and copy then highlight these three columns in the remaining rows that contain data and paste.
      All of these cells should now be populated and row F contains the correct Mandate IT value that you want to use.
      Select Column F and copy then right click in column G and select Paste Special, Paste Values.
      This will show the same values as are listed in Column F, but column G now contains the actual data values rather than a formula.
       
    • Columns B, C, D, E and F can now be deleted so that the only columns that remain are the gift import ID and the column that contains the Mandate ID.
      Set the heading for the Mandate ID to GFRefNum and save as a CSV file and close the file in Excel.


      Note
      The above process can be used with constituent ID if included in the export rather than the constituent system ID, if you prefer. 
      However, if you have constituent IDs that are more than 9 characters, these will be trimmed down to 9 characters by this process.
       
  2. Import the amended file back into Raiser’s Edge.
    In Admin, Import, create a new Gift Import
    On the General tab
    • What do you want to do – Update Existing Records
    • Option – tick Validate Data Only
    • Import File – use the browse button to pick up the CSV file created in Excel.
       

              On The Summary tab tick the option to create an output query of records updated which will be used to populate the Mandate Date later.
              Click Validate Now.
 
              There should be no exceptions.
              If exceptions occur these should be resolved and validate file again to ensure exceptions now show as zero.
              On the General tab remove the tick from Validate Data Only and click Update Now at the bottom of the screen.

 

  1. When complete check the recurring gifts and pledges to ensure you are happy with the results

     
  2. Open the query that was created by the import and include the criteria - 
    •  Mandate Date blank
              This will identify those where the Mandate ID has been updated today but still do not have a Mandate Date.
              Run the query and select File, Save as and save with a new name
  1. In Admin, Globally Change Records, create a new Gift global change and select this newly saved query via the Include button at the top left of the screen.
    Select Mandate Date from the list of Available Fields with the operator Add and enter today’s date.
    Tick the option to overwrite existing values to overwrite blank in the date field with today’s date.
    Click Change Now to complete the process.