I’m really excited to be speaking next week at Dreamforce 2016 — my first trip to the conference. If you’re interested in Nonprofit Success Pack maintenance and updates, come watch my session! Staying on Top of Salesforce & NPSP Releases starts at 10 am on Tuesday, October 4.

Importing Notes in Salesforce

Building Note records in Salesforce, using the new notes introduced in Winter ‘16, is challenging for a number of reasons. They come with unusual data-preparation requirements and are tricky to import using the Data Loader. Notes are tricky to create in Apex for largely the same reasons. Failing to follow the requirements for encoding incoming data typically produces notes with all of the line breaks omitted and/or unpredictable and difficult-to-debug exceptions.

To make matters worse, various documentation entries are either incorrect (the API reference on ContentNote specifies to use String.escapeHTML4() to prepare content, which doesn’t work) or overly vague and/or incomplete (the String API documentation on escapeHTML4() and escapeXML(), the ContentNote import instructions).

In summary, here’s what is actually required to insert ContentNotes:

  1. Replace all basic HTML characters (<>"'&) with their corresponding entities (&amp; and friends).
  2. Replace all line breaks with <br> (taking care with Windows CRLF/Linux LF/Mac CR)
  3. Replace &apos; with &#39;.
  4. Do not replace Unicode characters with entities. Other entities, including &apos;, result in an exception. Unicode should be left as the bare characters.
  5. Ensure that the source content is well-formed Unicode/UTF-8 and does not contain non-printable characters.
  6. The title must not be null, zero-length, or consist only of whitespace. The title need not be escaped.

This 4th point is why String.escapeHTML4() doesn’t work for preparing note text: this method replaces Unicode characters with HTML entities, which causes exceptions upon insert. String.escapeXML() is closest to what is needed, but doesn’t handle item 3 above.

In frustration with all the hoops involved, I put together a package that can reliably import and add notes and attachments, both programmatically and in bulk. The package provides a DMRNoteAttachmentImporter class that can be used in Apex code to create note and attachment records either singly or in bulk, as well as a Note Proxy object that can be converted into Notes using a batch process. Notes may be imported to Note Proxy from a CSV file using any data loader (obviating the one-file-per-note requirement imposed by Apex Data Loader).

Attachments are handled alongside Notes, since the machinery for creating them is very similar.

The code is MIT licensed. It will fail if the new notes have not been enabled in your Salesforce instance. It’s been tested with both Apex unit tests (100% coverage) and example files. Bug reports and patches are welcomed.

Be aware that, using the Note Proxy object, you can only import notes of lengths up to the limit of a Long Text Area, 131,072 characters (128KB, assuming 1-byte characters). In my testing, it’s fine to process 32KB notes in batches of 200. Stepping up to 64KB notes (which some spreadsheet applications and data loaders cannot process in CSV format in any case) required a reduction in batch size.

Errors that do occur with ContentNotes usually come in the form of System.UnexpectedExceptions, which cannot be caught or handled. Despite the best efforts of this package, it is still possible to trigger these exceptions by attempting to import notes whose text contains non-printable characters or mangled UTF-8. In a bulk note import process, this will cause the failure of the entire batch with no error message recorded on the note proxies. The error can be diagnosed by examining the Apex Jobs log, where the exception will be displayed. The only workaround is to use very small (or even 1) batch sizes to identify the offending note and manually correct its text.


Private Email Lists using MailChimp and Salesforce

One of the biggest challenges I’ve encountered in working with MailChimp and its MailChimp for Salesforce application is ‘private email lists’. I mean by this email lists that are designed for communication with, for example, major donors, board members, or other small groups of contacts that are defined by established relationships and specific CRM criteria. These groups may change composition regularly as new members are added or as old members drop out of the matching criteria, and no public user interface is required other than the standard opt-out.

MailChimp for Salesforce’s overall integration model is additive. MC Queries in Salesforce identify groups of records that must be included in a mailing list and pass those records into MailChimp, where any new records are appended. The reverse, however, is not the case. If a record ceases to match an MC Query, it is not removed from its corresponding list or groups. MC Queries are also fairly limited in the logic upon which they can match: criteria may only be combined by AND.

For many private email lists, this won’t work: donors stop giving and members rotate into and out of various internal groups with equal alacrity. Updating MailChimp private groups by hand is both error-prone and time-consuming. Here’s the solution I worked out to support these private email lists natively in MailChimp without manual maintenance.

  1. Use formula fields in Salesforce to implement inclusion logic. For example, a courtesy invitation list for donors might be defined as those with a “VIP” checkbox selected or giving in excess of $1,000 either this year or last year. Those three criteria could not be represented in a MailChimp for Salesforce MC Query, which only allows AND logic, but a Salesforce formula field with type Checkbox easily encompasses them.
    • MailChimp’s segment definition logic (see below) does allow for the use of OR-based logic. If you have many lists that are defined exclusively by ORing together different subsets of the same relatively small set of criteria, you may be able to skip this step.
  2. Create a new list in MailChimp to hold the private email lists. (It’s possible to use an existing publication list, but I felt that these communications were different in kind and that a separate list would provide a better user experience). I called mine “Private Announcements and Invitations”.
    • If existing lists are mailed to as a whole (as opposed to groups or segments within the list), you’ll definitely want to create a new list. Your Private Announcements list shouldn’t be mailed to as a whole, since members will remain in the list even when they stop matching your segments.
  3. In MC Setup in Salesforce, ensure that the formula fields (or the basic criteria if you’re implementing the logic in the MailChimp segments) you created to support list logic are mapped to MailChimp for this new list. Mapped fields, unlike list membership per se, are re-synced to MailChimp in a regular process, and Salesforce updates are reflected in the mapped data.
  4. In MailChimp’s settings interface, ensure that all of these fields are not marked Visible. This prevents users from viewing or editing these internal values by accessing MailChimp’s profile update UI.
  5. Build MC Queries to match each of the list-definition formula fields and set them to run on a schedule. The queries don’t need to be (and shouldn’t be) associated with a group or segment.
  6. Use MailChimp’s segment builder to create a segment for each private email list, matching on the value “true” in the mapped Salesforce formula field. (My formulas are all checkboxes, but they don’t need to be).
    • If you opted to use MailChimp logic rather than formula fields, configure your criteria here.
  7. Save each segment as an Auto-Update Segment. Auto-Update Segments do remove members who cease to match their defined criteria.

The end result is a MailChimp list with one Auto-Update Segment per private email list, all of which maintain themselves based on the membership information that is calculated by the formula fields and synced from Salesforce. List members who fail to match criteria do remain members of the containing MailChimp List, but will fall out of segments (the actual private email lists) to which they no longer belong as soon as the containing list syncs.

The time resolution for list updates is between one and 24 hours. MailChimp syncs lists from Salesforce on an hourly basis, which updates all of the synchronized fields. However, the MC Queries which add new members to the lists only run every 24 hours. Hence, you can count on updated information, including changed criteria that will prompt a member to drop off an Auto-Update Segment, to propagate to MailChimp within one hour, but contacts who match list criteria for the first time may not be added for up to 24 hours. Fortunately, you can always force a list sync or a query run from the MC Settings tab in Salesforce.

FileMaker into Salesforce

I’ve been working on a project to integrate a very important legacy database built in FileMaker (‘AM’) into Salesforce. I decided to perform this integration by building a read-only, one-to-one copy of the AM record (the legacy database is not relational) and importing the entire database more or less unchanged. This approach ensures full retention of critical historical information, while allowing me to selectively merge information with Salesforce contacts using Apex, DemandTools, and Apsona.

As I began building the ‘AM Record’ object in Salesforce to represent the legacy records, I realized that the source FileMaker database held even more fields than I thought - over 500. That number exceeds the limit on custom fields on a Salesforce object, and it also would take forever to build by hand.

The obvious solution is a script. Unfortunately, FileMaker makes it difficult to extract information about the database structure in any format that can be manipulated. I didn’t want to export the entire column set and infer typing, both because it’s easy to get wrong and because the database contained roughly 100 calculation and summary fields that did not need to be migrated to Salesforce. (About 80% of these fields were equivalent to Salesforce rollups or formula fields, but weren’t being used or were calculating based on obsolete data. The other 20% were providing values that were part of the user interface in the main FileMaker layout). I wanted to avoid hand-editing the column set in the export file, since I knew I would be iterating the import several times to get it right. I also couldn’t pick and choose in building the export itself, since the export UI does not identify calculation fields or display sample data. I needed something that could be parsed and automated.

It’s possible to generate an XML schema for a FileMaker database - but only in FileMaker Pro Advanced. With the Pro edition, my only option was the “Print” button in the Manage Database interface, yielding a PDF file with columns for Field Name, Field Type, and some additional details on picklist fields and calculations. I fed this PDF into an online PDF-Excel converter. (There are many; I used Nitro). It’s an ugly solution, but after some hand manipulation to stitch the pages back together, remove extra rows, and drop out the columns marked as calculations and summaries, I had something resembling a schema. Final result: 406 fields. Far too many, but reduced enough to fit in one Salesforce object.

The next step was to map the FileMaker structure into Salesforce’s terms. I built a simple Python script to read the FileMaker schema and spit out XML for insertion into an object definition in the Force.com IDE. After iterating several times, I discovered that my “schema” didn’t contain enough information on its own to perform the migration, as well as some interesting features of the Apex Data Loader:

  • “Boolean” columns in FileMaker were actually just Text entries with values of Yes and No.
  • Text columns in FileMaker don’t have length limits, as they do in Salesforce.
  • Some of the Text entries were very long (up to 6KB).
  • Apex Data Loader silently truncates overlong Text entries during import operations.
  • Apex Data Loader does understand “Yes” and “No” as legitimate values for importing Checkbox fields.

I got past these issues by revising my script to do some simple type inference, combining the full data export with my rudimentary FileMaker schema. The final script knows how to select between a Long Text Area and a Text entry based on the maximum entry length, and can tell the difference between a Boolean-valued column that should become a Checkbox and other text entries.

GenerateObject.py is available if anyone happens to be confronting the same problem. It’s not polished, but it works.

Test imports went off with only a couple small hitches: Salesforce bounds the acceptable values for a Date entry more tightly than FileMaker does, revealing a number of data entry errors (“0208” for “2008”), and a single column in FileMaker that was typed for date information but incorrectly contained text. My ad-hoc generation of the schema from FileMaker’s PDF file also inadvertently skipped two fields, which I found and corrected during the process.

Ultimately, I’m pretty satisfied with 85 easily fixable errors out of over 17,000 records. Having added a Salesforce page layout built to closely approximate the original FileMaker layout, I’m ready to perform the final import.

In summary, here’s the procedure that worked:

  1. Create PDF of database spec from the Manage -> Databases window in FileMaker.
  2. Convert PDF to Excel to CSV; manually review and correct errors or remove undesired fields.
  3. Export entire database (all fields) to Excel and convert to CSV. (I encountered numerous errors while attempting to export from FileMaker directly to CSV).
  4. Create stub object in Salesforce and pull metadata using Force.com IDE (or Ant or MavensMate).
  5. Run Python script to generate XML field entries for all the columns in the FileMaker schema.
    • Optionally, run the script once with the verification code uncommented and ensure that all discrepancies between the database export and the schema are deliberate.
    • Ensure that generated Salesforce API names are unique, and correct any duplications.
  6. Copy and paste XML field entries into the stub object definition in Force.com IDE and push to the server.
  7. Use Apex Data Loader to import the exported file into the new object.
    • Automatic mapping worked fine for me, although I manually edited the object’s Name field label to be that of the record ID from FileMaker and removed it from the generated schema.
  8. Fix any import errors.

WFTDA Penalty Codes and Verbal Cues Reference

In the course of working with a new Penalty Tracker for an upcoming mock sanctioned bout, I realized that I could not find a good reference to the WFTDA penalty codes and verbal cues that included 2016’s new penalties (Leaping Contact [my favorite verbal cue] et al).

Here is my stab at the problem: WFTDA Verbal Cues and Penalty Codes Reference

The PDF has two pages, the first ordered by verbal cue and the second by penalty code. Please feel free to use, share, and modify.