Extending NPSP Data Import Tool for Cases

The NPSP Data Import Tool is my favorite way to do things automatically. I had the need to add cases based on web signups. Here is my very simple journey to a very satisfying result.

The Need

I had the need to add cases based on web signups. Examples include requesting a speaker, filling in a service project request form, using our contact form to ask for something. These fall outside our automation and require cases. But I wanted to use cases so we know how many of these things come up and how well we do with them.

It was super simple and fully declarative to extend the NPSP Data Import Tool to do this. But first, why do it at all this way?

I want to keep duplicated to a minimum and I want to be safe from NPSP changes in the future. By using the NPSP Data Import Tool scheduled batch features, I can import data and let NPSP worry about matching existing contacts, adding households, and adding people to campaigns. And with my extension, I can easily create cases, too!

Here were my steps to a fully declarative solution

1. Add fields to support cases on NPSP Data Import object.
   • I am using the following fields on the case: Subject, Description, Internal Comments, Type (picklist), Origin (picklist) and Reason (picklist). So I needed each to be on the NPSP Data Import object to specify how the case should be created. I made all the fields on the NPSP Data Import object text fields of sufficient length to minimize maintenance.

   • Case Subject

     Case Description

     Case Internal Comments

     Case Type

     Case Origin

     Case Reason

2. Add columns in my Google Sheet Template
   • I have one Google spreadsheet where I keep all my data that I want to import. This way I just have one template. I started with the import template provided on the Power of Us Hub. But I have added several custom fields to supported objects. This was my first attempt time to be adding a new object.
3. Add support for moving data rows, including my new columns for cases, from the sheet to a new Salesforce NPSP Data Import record using Zapier.com.
   • Zapier.com is a cloud automation tool. I use it to connect all sorts of things like Salesforce, Gmail, Google Sheets, my phone contact list, Trello, Twilio and more. Each Zap is a simple recipe stating that when something happens on one cloud tool, do something on another. Zapier provided a starter package for nonprofits free of charge. I use a higher level of services now after more than a year of Zaps and pay a reasonable and discounted monthly charge, the equivalent of about two hours of manual data entry, but Zapier takes care of several  thousand manipulations per month. For me, it’s a great bargain!
   • I created the Zap so that each time a row is added to my data import spreadsheet, All the data columns are transferred to a new NPSP Data Import record in Salesforce. That’s really all it does.
   • How does data get into the import spreadsheet? Normally it gets there by another Zap from a form submission. Each form requires filling in different columns… some a few and some many.  But this is a story for another blog.
4. Scheduling the record to be imported nightly
   • NPSP Data Import Tool has a great batch import function. It’s covered in the Advanced Admin Guide. It requires setting the batch type on the NPSP Data Import record and creating a NPSP Data Import Batch configuration for each batch type. In the configuration, you can specify what type of matching to use for each record.
   • When the NPSP Data Import tool processes a record, it updated the record with matched or created contacts, accounts, donations and payments and marks the record as successfully processed.
   • Any record unable to be imported, will be marked as a failed import. You can view it later, edit the record data to fix the error, and it will get imported again on the next run.
5. Creating a Process Builder to wait for NPSP Data Import records with my new case fields to be marked as successfully imported during the nightly run.
   • The NPSP Data Import Tool finished up with it’s part of the job, but there is no case record yet. So this is the job of Process Builder! The Advanced Admin Guide covers how to implement APEX classes to post process NPSP Data Import records, but I wanted a declarative method.
   • My Process Builder watches for NPSP Data Import records modified so that the status is newly changed to successful import.
   • If any such records contain case fields, my Process Builder goes to work and creates a new case record using the data on the NPSP Data Import record. It updates the NPSP Data Import record with a lookup to the newly created case.

So that’s it! In less than an hour, I had extended the NPSP Data Import Tool to support creation of new cases!

What’s Next?

Different Schedule?

We plan to create our own schedule to run the import task hourly rather than nightly. During active fundraisers we might do it even more frequently.

More Cases?

• In addition to creating cases, we wanted to put many of the case people onto specific campaigns. This turns out to be super simple. There are already fields on NPSP Data Import object to add a contact to a campaign with a specified status.
• We have several forms that aren’t frequently used but are important enough to track with cases. We’ll be adding those soon by creating a new Zap to go from the signup specific spreadsheet to the master import spreadsheet.
• We want a simple Google Form to enter phone messages that need to turn into cases. This can also work for any snail-mail processed by the team. Zapier can be tied to a Google form submission and will add another row in the proper format to our import spreadsheet.
• We want a simple label system in Gmail that allows me to create a case from any received email.
• We just added SMS texting to SYM. We will automatically process quite a few of them, but any messages not in the format we recognize will result in an email being sent to me. We want to be able to label that email in Gmail and result in a case being created.

Different Objects

• The NPSP Data Import Tool does not support relationships. That’s a drag.  We want to extend it to do so. The challenge is in coming up with a way to link two rows in a spreadsheet. We think we can specific a new field called Relationship Key and put the same key into two lines. Process Builder could use that Key to help pair up completed records and add a relationship between them.
• Tasks. It would be simple to create tasks for any important item using Process Builder.
• Program Enrollment objects. We have some custom objects for intake of donors, volunteers and clients. It would be simple to add these after the contact is created or matched.