Sort it out! Importing bank details into Raiser’s Edge™ when required fields are missing
If you’re in the UK, you’re probably familiar with the challenge of importing Bank details for your direct debit donors. When receiving payments toward existing recurring gifts, you may only have the Bank Sort Code, but Raiser’s Edge and ImportOmatic need the Bank Name and Branch Name too. Inevitably, you end up with exceptions during your import because Raiser’s Edge cannot tell whether the bank you are importing is new or existing.
Recently, when I was working with a team from the UK, they only had the Sort Code in their file—no Bank Name or Branch name. So, we came up with a great way to avoid most of these Bank Name/Branch Name issues. The answer was surprisingly simple – use a dictionary!
We created two virtual fields to copy the Sort Code, then used a dictionary translating to Bank Name and Branch Name, respectively. In the example below, the Sort Code is listed in Column C in my data file:
That’s the easy part, right? The tough part is actually building those dictionaries! Don’t worry, I’ve got an easy solution there, too.
When you open your Financial Institutions grid in Raiser’s Edge Configuration, you can right-click to export it:
Then, in Excel, you just need to reorganize the columns with the Sort Code immediately to the right of bank/branch to copy and paste the values into a new dictionary.
Bank Name:
Branch Name:
Be prepared—these lists will be LONG! Our lists contained about 20,000 entries per dictionary. To help mitigate this issue, you may want to clean up your Financial Institution list first, using the built-in cleanup tool in Raiser’s Edge:
One last tip – you may find that your Sort Codes lose their leading zeros in Excel. Check out this forum post to see how to correct the values on their way into RE. Just remember, you’ll need to also add these “bad” Sort Codes to the dictionaries’ values to match on because the Copy Field function in Virtual Fields copies the original value in your data file.
Good luck sorting it out! As always, if you would like further guidance, our team is always here to help!
