i faced off against microsoft word mail merge, and…

… i e-merged victorious!

(har har har)

One of the most energizing (and some times aggravating) aspects of being a polyamorous spreadsheet whisperer (borrowing the term from my pal Emily) is running up against limitations in more than one system at the same time, and then finding new routes out of the maize using creativity, elbow grease, and the power of community.

I’m never JUST working in Google Sheets, or Salesforce, or Airtable, or or or [insert platform name here]. I’m taking those platforms and pushing/expanding/imagining what they can accomplish beyond “face value.” I’m moving data between those platforms. I’m tinkering, curating, experimenting. I’m downloading custom apps, custom code, or brilliant ideas from blogs and forums to make these technology platforms be compatible with my goals. Most importantly, I know I’m not alone! You are working on tackling these issues too, each in our own way!

Here’s a taste of what my morning was like, moving data from Salesforce to Conga Composer [third party platform] to Microsoft Word.

the goal

Download a document with Salesforce data organized into a template for a contract. The contract contains several tables of data, like payments, deliverables, etc. We need to selectively hide one table’s worth of data if the table is empty.

We use a third-party application called Conga Composer which organizes our data and populates a template with it. However, the template was breaking when I tried to selectively show/hide certain tables. We don’t want the table header to show up if there are no rows below it!

the result

Here’s a pic of the winning combination of MS Word Mail Merge logic and Conga Composer merge tables!

1. {{TableStart…}} refers to the dataset that is merged in through Conga
2. The highlighted bits refer to MS Word logic. In human words, if “BLAH FIELD” is “blank”, show “nothing.” Otherwise, if “BLAH FIELD” is “not blank”, show “this entire table, populated with data!”
3. If you are trying this at home, refer to the video linked below…. you CANNOT (i repeat cannot!!!!!) type in the bold butterfly brackets because they are a special format in MS Word that must be entered using special keystroke combinations or dialogue boxes that are outside the scope of this blogpost.
4. Some of the merge fields are “whited out” below for privacy reasons. If you were building something similar, you would replace the white out parts with your own text or merge fields.

Basically, this template will include the table if there are rows in the table, and it will hide the table if there are no rows in the table. Success!

the path not taken

In our data model, we have grants … some grants have conditions and some do not. Conditions are the table that we want to show/hide in the template.

One way to solve this problem was to use a roll-up field to count how many conditions were related to the grant in question. If the grant has 0 conditions, use template X. If the grant has 1+ conditions, use template Y. Conga Composer is smart enough to select templates based on a Salesforce formula field (which is something I use regularly!) but the downside of this option is that I would have to create redundant copies of ALL of my contract templates (and I already maintain 9 of them. Maintaining 18 is just too much!)

There’s GOT to be a way to show/hide the data INSIDE of the templates we are already using!

the obstacles

Limitations in Conga Composer

Conga Composer has the capability of selectively showing/hiding data in various circumstances. However…. after much searching and troubleshooting, I saw this alert … and my stomach plummeted a bit.

Lesson learned – Conga Composer can hide a table of data based on a certain value, but it can’t hide data based on whether there IS or IS NOT data. So we can’t strike those empty tables, darn it!!!!!!!

If there’s a better way to do this, I couldn’t for the life of me figure it out (please tell me if you know!), so I had to look outside the box/my comfort zone. Stay with me!

Finding another path

I know now that MS Word can do quite a lot with conditional statements in mail merge, and I’m curious enough to try to learn this technology because I think it could prove to be very useful in the future!

I thanked my lucky lil stars for finding this tutorial which was solving EXACTLY THE SAME PROBLEM AS ME!!!!! I learned that Microsoft Word is more capable (or differently capable?) of hiding data than Conga Composer is. However, I am more skilled at quirks in Conga Composer than Microsoft Word mail merge operations, which gets pretty technical pretty quickly. Unfortunately, the errors I was getting in my MS Word logic did not give me enough info to be able to troubleshoot effectively. (Google was a lost cause, womp womp!)

Despite the roadblocks, I was seeing small signs of success that this was POSSIBLE if I could only troubleshoot the issues that I was facing I saw that the data made it into MS Word, but I couldn’t format it properly. I saw that my conditional logic was valid, but it wasn’t typed in correctly. This didn’t tell me how to get UNSTUCK but it made me optimistic enough to decide that it wasn’t a lost cause. Plus, the guy in the video had figured it out, so why couldn’t I?

1. Error #1 in Microsoft Word: “Error! To many picture switches defined”
   I still don’t know the root cause of this error. I continued to receive it when I deleted all of my picture switches… and then when I got my mail merge up and running [and added the picture switches back in!] the error was no longer a problem. Head scratcher!
   
2. Error #2 in Microsoft Word: “Error! Missing test condition”
   I’m pretty sure that this issue had to do with the very strict syntax rules for entering merge data in MS Word mail merge templates. Unfortunately, I didn’t have the background experience to be able to accurately identify the issue (it could have been as simple as a space bar out of place).
   
   I got past error 1 and 2 by starting over as many times as I needed to, and trying to closely follow the very generous formatting description in this link.
   
3. Issue #3 (not quite an error) issues with toggling my merge fields open/closed
   As whittled closer and closer to a viable template, I noticed that my data merged into my IF statement, however, the whole formulation was showing in my template, when it should only show the result. I eventually resolved this issue by learning how to “toggle off” all of my merge fields by clicking Alt+F9 [windows].
   
4. Getting frustrated
   I noticed toward the end of Sam VS Template that I was getting frustrated, making mistakes, leaving too many docs open on my computer, and generally being sloppy. I think this throttled some of my creative, optimistic energy and slowed me down when I was really, really close to figuring it out! A better idea (if I had taken my own advice) would have been to take a break or ask a friend for help. But hey, I’m only human, and sometimes advice is hard to follow!

so what?

If you’ve made it this far… THANK YOU! You deserve an ice cream sundae with sprinkles 🙂

So, why is this problem + solution relevant for grassroots problem solvers and change makers?

Well, two ideas come to mind for me.

1. Process
   Sharing my process, including all of the ways that I got stuck, is cathartic for me and elevates the invisible labor of working on tech solutions for progressive orgs. If we don’t talk about this work, we will continue to underresource our orgs and movements when the opposite is most sorely needed. If we pretend that it’s always easy or glamorous, we risk isolating those of us who are struggling the most. This type of writing is vulnerable for me because I got REALLY STUCK multiple times and that can be kinda hard to admit. If you’ve been there, I know you can relate. Let’s not hide from the hard stuff, okay?
   
2. Outcome
   I’ll admit it, I’m a bit of a mail merge aficionado. I think being able to export data into usable formats, AND being able to automate tedious tasks (like sending out donor tax letters all at once instead of one at a time, snoorrrreeeeee) is super duper worthwhile! Gone are the days of customizing letters one at a time. Instead, why not export the whole lot of them? With mailing labels to boot? (You can always add a personalization before stuffing the envelope… I think there are ways to automate that too, but truth be told I have not encountered them yet).
   
   Using conditional logic can give letters, contracts, or other templates a custom/personalized touch without having to (1) manually edit or (2) maintain a ridiculous number of templates. Wouldn’t it be awesome to have a thank you note template that said “thank you for your gifts this year – and last year too!” (but only include the “last year too” part for people who REALLY CONTRIBUTED LAST YEAR?!?! This is the kind of stuff I’m dreaming of… using tech to enhance relationship stewardship while reducing unnecessary (?) labor, so that we can put our heart work into other avenues of social change.

Maybe you aren’t jazzed or concerned about showing/hiding tables in a complex mail merge (and that’s ok!) but I hope I’ve persuaded you even a little bit that these skills can be transferred to problems that can make a heckuva difference in our administrative lives, and translate into relationships that make social movements possible. That’s what makes the hard mornings worth it 🙂