October 11, 2024

Processing bank donations in Salesforce.com

Does your organization receive payments (donations) via bank transfer (wire-transfer)? Do you want to see the transactions inside Salesforce?

Cloud API

Last time we looked at how to integrate salesforce with the popular Darujme.sk portal. Today we will look at another Salesforce feature that can be very useful for (not only) non profit organizations. We will stay with the topic of donations and we will explore how we can import donations made via wire-transfer (bank deposit, sepa payment) into Salesforce.

There are two ways how we can programmatically interact with a bank account.

PSD2

In Europe the Payment_Services_Directive requires all banks to provide API acesss.

However getting access via api is a major undertaking. Banks usually don’t provide a simple “I need readonly access to my account” access. Instead a company needs to have a license issued for universal bank access. Then get a qualified certificate. After all of this the company can ask permissions to access a particular bank account. With another round of checks.

  • Pros: structured and rich data.
  • Cons: difficult to get started. Expensive. We need a security process to keep keys and certificates secure and up-to-date.

Bank notification emails

Just process the bank notification emails! Yay.

  • Pros: easy to get started. No certificates or permissions needed.
  • Cons: Compared to API, we need to process and parse free form text. This is always fragile, because we cannot test all possible variants and the email format is subject to change at any time, without notice. In practice this happens very rarely.

For this blog we shall go the easy route #2

Plan:

  1. We will use the Salesforce Email to Apex function
  2. We will parse the email: text, xhtml and html.

What is Salesforce Email to Apex? It is an extremely useful feature, emails sent to an address can be automatically processed by an apex class.

In a nutshell,

  1. Salesforce will give us a unique email address.
  2. Emails sent to this addressed are passed as parameters to our custom apex class.

First of all our apex class has to implement a specific interface. Make sure we return the right result.

public with sharing class InukoBankEmail implements Messaging.InboundEmailHandler {
     public Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.InboundEnvelope envelope) {
        Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
        result.success = true;
        
		// TODO: actually parse the email and create objects
        
        return result;
    }
}

Next we will configure salesforce to generate a unique email address for our apex class.
We will do this in the Salesforce Setup > Custom Code > Email Services section. (Or just search for Email Services :). Email Services setup .

Click on New Email Service then choose the Apex we've made in the previous step.
Click on New Email Address and copy the email address Salesforce generated for us.

Make sure you lock down the email sender. Since the incomming email will trigger code with database access, we must be careful about who can send us email.

You can also find more information in the great Salesforce documentation.

That’s it, let’s dance, erm I mean let’s parse.

Parsing emails in APEX

Each bank’s notification email will for sure have a different structure. Thus this code is not trying to be universal, rather it should give you a toolkit that you can use to parse your bank’s format.

In the end we will need a different method for every bank. We can switch between them based on the envelope.fromAddress value. It gives us the sender - the bank who sent the notification.

Emails used to be plain-text. These days the cool kids like richly formatted emails, thus html emails are very common.
A bank email can therefore have these parts:

  1. Plain text (optional, only html can be provided)
  2. Html (optional, only text can be provided)
    • Which can be valid xml (xhtml)
    • Or it can be valid html but invalid xml

In the next sections we will look at some APEX code. We shall be using the InuTransaction object we defined in the previous blog post

Plain Text parsing

Plain text parsing is usually very straightforward. The most common format is one data value per text line. Where each line has two parts the field name and value.

Payer Account name: Acme corp
Payer IBAN: FC48 0000 4839 2995 9592
Amount: 34,20 Euro
Date: 24.9.2024
Information text: invoice-39382

To parse an email like this, we shall split the text info lines, then each line by the : separator. We then look for specific field-names and parse the field-value.

For example if we know the field-name is ‘Date’ we will parse the text into a Date object. For the iban field, we will remove all whitespace even inside the iban string. As a bare minimum we might want to trim trailing whitespace from values.

public static void parsePlainTextEmail(String body, String rawBody) {
	Contact defaultContact = getDefaultContact();
    InuTransaction__c t = new InuTransaction__c(contact__c = defaultContact.id);

	String[] parts = body.split('\\n');
    for (String p : parts) {
		String[] line = p.split(':');
		if (line.size()<2)
			continue;
		
		String name = line[0].trim(); // remove starting and trailing whitespace
		String value = line[1].trim();

		if (name == 'Date') {
			String[] dt_parts = value.split('\\.');
			t.Transaction_Date__c = Date.newInstance(integer.valueOf(dt_parts[2]),integer.valueOf(dt_parts[1]),integer.valueOf(dt_parts[0]));
		} else if (name == 'Amount') {
			// remove currency, any whitespace, convert decimal comma to decimal point.
			String num = value.replaceAll('EUR', '').replaceAll('\\s+', '').replaceAll(',','.');
			t.Transaction_Amount__c = Decimal.valueOf(num);
		} else if (name == 'Payer IBAN') {
			String iban = value.replaceAll('\\s+', ''); // remove any whitespace within the IBAN
			t.Payer_iban__c = iban;
			String contactId = findContactByIban(iban);
			if (contactId != null) {
				t.contact__c = contactId;
			}
			String accountId = findAccountByIban(iban);
			if (accountId != null) {
				t.account__c = accountId;
				t.Donor_kind__c = 'company';
			}
		} else if (name == 'Information text') {
			t.Info_text__c = value;
		} else if (name == 'Payer Account name') {
			t.Bank_Account_Name__c = value;
		} else if (name == 'Variabilný symbol') {
			t.Variable_number__c = value;
		} else if (name == 'Špecifický symbol') {
			t.Specific_number__c = value;
		}
		t.Transaction_Status__c = 'successful';
		t.Zdroj_daru__c = 'Účet';
	}

	// Parse failed: keep the whole email, so we can debug.
	if(t.Payer_IBAN__c == null || t.Transaction_Amount__c == null || t.Transaction_Date__c == null) {
		// save the email so we can investigate.
		t.bank_email_body__c = rawBody;
	}
	insert t;
	return true;
}

XHTML parsing

If your bank send a nicely formatted xhtml emails (test it, unfortunately we stumbled on false promises before), we can use the apex xml parser. We load the html body of the email, and then look for specific nodes.

In our experience the information fields will be put inside a table. Our approach is to look for all TR nodes and the TD elements within. We expect values in TD to be primitive or only lightly formatted (spans with colors that kind of thing).

We will recursively look at each xml node. If it is a TR (table row) node, the for each child we will get the text and add it to the parsed list.

The getNodeText helper will deal with the case where a TD contains more than text. If it has children we will extract and concatenate the

public class Row {
	public List<string> cells;
}
    
private static String convertXmlToText(String xmlText) {
	Dom.Document doc = new Dom.Document();
	doc.load(xmlText);
	Dom.XMLNode root = doc.getRootElement();
	List<Row> rows = new List<Row>();
	parseXml(root, rows, '');

	// We convert the table rows into lines and cells into label:value pairs.
	// You can also just use the values directly of course. 
	String result = '';
	for(Row r : rows) {
		if(r.cells.size() >= 2) {
			result += r.cells[0] +':' + r.cells[1] +'\n';
		}
	}
	return result;
}
private static void parseXml(Dom.XMLNode node, List<Row> rows, String pad) {
	List<Dom.XMLNode> children = node.getChildElements();
	
	if(children.isEmpty()){
		return;
	}
	
	Boolean isRow = node.getName() == 'tr';
	
	Row r = null;
	if (isRow) {
		r = new Row();
		r.cells = new List<string>();
		rows.add(r);
	}
	
	for(Dom.XMLNode child : children){
		
		String nodeName = child.getName();
		if (nodeName == 'td') {
			String tdText = getXmlText(child);
			r.cells.add(tdText);
		} else {
			parseXml(child, rows, pad+'  ');
		}
	}
}
    
private static String getXmlText(Dom.XMLNode node) {
	List<Dom.XMLNode> children = node.getChildElements();
	if(children.isEmpty()){
		return node.getText();
	}

	String r = '';
	for(Dom.XMLNode child : children){
		r += getXmlText(child);
	}
	return r;
}

General HTML parsing

If your bank only sends you HTML notification emails and they are not valid XML, then this section is for you.

To parse HTML we need an HTML parser. Duh. However there is no HTML parses in APEX:( . If the format of your bank email is to strange, we recommend a Azure function or some other service where you can run Python and use the BeautifulSoup library for HTML parsing.

But, chances are high, the email can be parsed with Regex. Generic HTML parsing with Regex is a big no-no.
Thankfully, we are not parsing just any HTML, instead we are looking for specific things inside the HTML.

Since our bank email has all the information we need inside a table, we will look for tr and td elements and extract the text within them. Keep in mind that your bank email might look different, therefore you might be looking for some other elements.

The code below, will take all td elements and produce a list of label:value pairs. With the assumption, that there are exactly two td elements.
If the text within the td looks like another element (starts with <), we assume these are span elements and we will take the text within them and simply concatenate.

private static String convertHtmlToText(String htmlText)
	Pattern tdPattern = Pattern.compile('<td[^>]*?>(.*?)</td>');
	Matcher tdMatcher = tdPattern.matcher(htmlText);

	Pattern spanPattern = Pattern.compile('<span[^>]*?>(.*?)</span>');
        
	String sb = '';
	Boolean isLabelCell = true;
        
	while(tdMatcher.find()) {
		Integer groupCount = tdMatcher.groupCount() + 1; // 0 is the full match

		for(Integer index = 1; index < groupCount; index++) {

			String z = tdMatcher.group(index);
			if (z.subString(0, 1) == '<') { // text is <span ... parse again
				String sp = '';
				Matcher m2 = spanPattern.matcher(z);
				while (m2.find()) {
					String spanText = m2.group(1);
					sp += spanText.replaceAll('&nbsp;','');
				}
				z = sp;
			}
			sb += z;
			if (!isLabelCell) { // == isValueCell
				sb += '\n';
			}
			isLabelCell = !isLabelCell; // first td is label, second is value. Alternate
		}
	}
        
    return sb;

Wrap

Whether you are a non-profit organization or a for-profit one, integrating payment notifications is very useful. It closes the loop of workflows, providing a way to clearly mark an activity as finished. It also enables automation, allowing us to first segment our donors/customers based on the amount and frequency of payments. And then launch subsequent activities based on that.

Email Services or Email to Apex, is a great way to implement such a feature. We get instant feedback. And there is no need to pay, setup and secure an additional service running elsewhere.

Happy hacking!

Continue reading

Fundraising tools for non-profits

September 27, 2024
Fundraising tools for non-profits

Are you running or working for a non-profit?
Do you struggle with managing donors, donations and communication?
Let's look at how digital tools can simplify your life.

Salesforce.com REST API for data manipulation

Úsmev (Smile) app from Inuko

May 07, 2024
Úsmev (Smile) app from Inuko

In previous blogs, we introduced the applications we created for non-governmental organizations or non-profits. Among them, the most outstanding were our apps for the organization Cesta von, which helps marginalized communities throughout Slovakia to get out of the trap of poverty and to ensure a better life for future generations.

©2022-2025 Inuko s.r.o
Privacy PolicyCloud
ENSKCZ
ICO 54507006
VAT SK2121695400
Vajanskeho 5
Senec 90301
Slovakia EU

contact@inuko.net