Need to manipulate Salesforce.com data quickly?

Would you rather use the tools and language you know?

We too, let's get it done.

The problem

One of the most infuriating things about Salesforce.com is the inability to filter based on child records.
Our client needed to add Contacts to Mailchimp. To get started only a subset - those who made donations via the Darujme platform. Donations are tracked as a custom Salesforce object that is connected to the contact object - the person who made the donation.

The Salesforce - Mailchimp integration, only allows the standard list-like filters on the primary object and parent references (for contact that would account for example).

So we need to find all the types of the donations and add them onto the contact. If you imagine the donations object as a child node in a tree below the contact, you can see why this would be called “Rollup” the type.

Solution #1 Rollup field

We can add a rollup field to the contact object, one per donation type.
To do that, we go into the Fields & Relationships and add a new rollup field.
Then we select the donations object (called transaction here).
Finally we choose the COUNT Roll-Up Type and setup filter criteria on the donations object, to count only donations of one type.

Another interesting alternative is to choose the creation date and MAX Rollup-type. This way we can check if a donation of this type was made and also when. If we need to find recent donors, this information will be handy.

Solution #2 Use the API Luke

If we have many donation types, one field per type might be impractical. Or if we need to do the rollup operation only once, rollups might be an overkill, since we don't need them to be recomputed.

The Salesforce.com REST API is perfectly suited for data manipulation and transformation. Compared to Apex - the native SF programming language - the setup is trivial and we can use whatever language we want!

Before we can read and write data via the Rest API, we have to first authenticate and authorise our app.
This can be either quite complicated (for proper OAuth) or you could open a security hole into your system (with legacy password auth).

So for a quick and dirty solution we shall use Postman for doing the authentication for us and we’ll copy the instance_url and Access Token.
In your SF, go to setting and enable cors for postman.

Then follow the Postman setup here.

Armed with the access token - our key - we can now read and write SF data.

We shall do this with javascript and nodejs.

First let’s read all donations of the ‘Darujme’ type and collect all the contacts that have a donation of this type.
Note that salesforce will not return all donations in one request, instead we need to “page” through the results. We will accomplish this with the nextRecordsUrl and the done field.
The done field is set to true, if there are no more results. Otherwise we make another request to the nextRecordsUrl (nicely provided to us by SF) and continue in the loop.

const getContacts = async () => {

    const myHeaders = new Headers();
    myHeaders.append("Content-Type", "application/json");
    myHeaders.append("Authorization", "Bearer " + ACCESS_TOKEN);

    const requestOptions = {
        method: "GET",
        headers: myHeaders,
        redirect: "follow"
    };

    const soql = "SELECT id, name, donation_type__c, contact__c, contact__r.name FROM donation__c where donation_type__c = 'Darujme'";

    let url = ENDPOINT + "/services/data/v61.0/query/?q=" + soql;

    const contacts = {};
    while(true) {
        const resp = await fetch(url, requestOptions)
        const r = await resp.json();
        if (r.nextRecordsUrl)
            url = URL + r.nextRecordsUrl;

        for (const rec of r.records) {
            if (!contacts[rec.contact__c]) {
                contacts[rec.contact__c] = 1;
                //console.log("ADDED: "+ rec.contact__r.Name)
            }
        }

        if (r.done)
            break;
    }
    return contacts;

Now that we collected all the contacts that donated through the Darujme platform, we can proceed to update the contact records.

The most straightforward way is to send an update request for each contact. And this will work (we checked), but it will be sloooow.

const updateContactsOneByOne = async (contacts) => {

    const myHeaders = new Headers();
    myHeaders.append("Content-Type", "application/json");
    myHeaders.append("Authorization", "Bearer " + ACCESS_TOKEN);

    // One by one -> super slow
    for (const cid of Object.keys(contacts)) {
        const raw = JSON.stringify({
            "donation_type__c": "Darujme"
        });

        const requestOptions = {
            method: "PATCH",
            headers: myHeaders,
            body: raw,
            redirect: "follow"
        };

        const r = await fetch(URL + "/services/data/v61.0/sobjects/Contact/" + cid, requestOptions);
        console.log(cid + "=>" + r.status);
    }
}

Instead, when doing large batch changes we need to use the batch (composite) api (duh:).

const updateContactsBatch = async (contacts) => {

    const myHeaders = new Headers();
    myHeaders.append("Content-Type", "application/json");
    myHeaders.append("Authorization", "Bearer " + ACCESS_TOKEN);

    // 200 at the time -> super fast (super less slow:( )
    const ids = Object.keys(contacts).slice();
    let b = 1;
    while (ids.length > 0) {
        const batch = ids.splice(0, 200);

        const raw = {
            "allOrNone": false,
            "records": batch.map(x => ({
                "attributes": { "type": "Contact" },
                id: x,
                "donation_type__c": "Darujme"
            }))
        };
        const requestOptions = {
            method: "PATCH",
            headers: myHeaders,
            body: raw,
            redirect: "follow"
        };

        const r = await fetch(ENDPOINT + "/services/data/v61.0/composite/sobjects/", requestOptions);
        const j = await r.json();
        console.log("##" + b); b++;
        for (const rr of j) {
            console.log(rr.id + "=>" + rr.success + (rr.errors && rr.errors.length ? " ERR:" + JSON.stringify(rr.error || "") : ""));
        }
    }
}

The code is a little more complicated. First we splice the first 200 (at most) contacts into a batch. Then we send the batch in an mass update request.
We continue the loop while there are contacts left to update (the splice operation removes the elements from the array).

That’s it! (Of course we have previously added a custom field onto the Contact).

We have manually “rolled up” the donation type onto the parent contact.

REST assured

Like with everything else in life, information has to be regularly maintained, otherwise it will turn into a big mess.
Having a powerful API that can help us read and write data with ease, with our choice of language, environment and tools is a must.

Thankfully the Salesforce.com REST API fits the bill nicely.

We hope the code above was useful in your data endeavours.

Don’t hesitate to get in touch if you spotted some bugs, or you would like our help with data-massages.

Happy hacking!