Conditional acknowledgment letters - Documentation coming?

Hi Ben,

That one got pulled due to some errors. However, there is going to be a Skills Lab during the Blackbaud Developers' Conference June 7-9 lead by one of our Community Members Matthew Thacker that covers a different approach to it. You can find more details about the Developers' Conference and Skills Labs at this announcement: https://community.blackbaud.com/blogs/69/8358

I am also very interested in seeing your code! We have created a simplified merge using Queue and CSV downloads via power automate as a stop gap measure, but our previous acknowledgment process heavily relied on the letter code. The simplified process we've devised is just not working and I really want to build this out better.

I've attached a ZIP of the flow to this post. Sorry in advance for the length of this post, but I wanted to try to answer the questions I figured would come up.

I also want to reiterate again @Glen Hutson ‘s suggestion to attend the Developer’s Conference, and in particular Matthew Thacker's session on conditional acknowledgements. It is not unlikely that he has a more elegant way of achieving the same goal that might work better for you (and I am a huge fan of testing lots of different solutions---if nothing else, it helps me think of these problems in different ways, often teaching me a new trick along the way)

1. This is set to run nightly and will pull all unacknowledged gifts for the last 5 days (I did not include all gift types, so you may want to update the criteria in the “List Gifts” item)

2. The first condition ("are there any gifts to process") is set to pull a lot of info that is pertinent to our data schema. We make lots of use of attributes. You'll need to make sure that the info that you need on your letters is captured in here somewhere, and this is where a lot of the customization for your process will happen.

3. Nested within that first condition is yet another condition (Letter code not Null). You may or may not need to keep this condition---our flow is that gifts that enter the auto process are coded with a letter, and those completed by specific staff (major donor, foundation relations) will be completed outside this process, and so gift processing does not assign them a letter. Again, your process may be different.

4. Also on that “Letter code not Null” condition are more items for letter merge fields (payment type, tribute information).

5. Perhaps most importantly, “Letter code not null” is also where the switch action is stored--this is where the different letters are created. For each letter code, you'll need a Word template that is saved in a sharepoint folder. Switch needs to include:

• a compose action to name the file
• the word template
• An action to save the completed letter to Sharepoint
• a data append to increase the number of the variable that counts letters for Encodian.

My process also includes the action of saving the letter to the NXT gift record (both a “get file properties” and “create gift attachment” item)

**my process is to have envelopes and letters print to the same file, because they print on the same printer. You could create two documents here (letter, envelope) if you needed to**

Again, my process includes Encodian, for which you'll need an account. The free account has-- I think--50 runs/month, which is more than sufficient for this nightly process.

“merged” files from Encodian are saved to Sharepoint in this process (so there is a single letter saved, and a document that has the full run of letters for printing).

Finally, my process includes a post to a Teams channel with either a message that there are no letters, or a message with a link to the merged doc. Gift processing reviews each letter and customizes as needed.

…and as a bonus, there is a final condition at the end for tribute cards. You can delete this safely if you don't send tribute cards as a part of your regular process.

This all takes trial and error to make the process meet your needs. I'm happy to help as I can, so feel free to reach out!

Bulkovernightacknowledgements_20220513135450.zip

Nicole Szydlo:

This is amazing. I only started to dig in! What is your rough volume of gifts that move through this process (compared to the note you shared about personal letters from a specific staff member outside the process). I was also intrigued by the conditions around payment type, tax, daf, etc. - is this because you aren't able to achieve some of that in the letter templates themselves? I have struggled trying to combine the old-fashioned conditional merging tools in word with these new ways of having to create the templates to play nice with power automate. Were those extra conditions a work around for this? I'd love to see one of your letter templates that gets used with this flow if you were able to share.

Once again, this is just awesome. I'm so grateful for your willingness to share.

I'm really happy this helps!

Our volume varies throughout the business cycle--I'd guess we have a high of 100 letters in a day down to a low of a few. Most of our letters go through this process: major donors and some special requests/relationships are managed through our donor relations team (who draft their own letters), and anything related to grant funding is handled by our grants team. So a handful are ushered to a different process, but not many. And, of course, every letter is reviewed and personalized as needed, and every letter is signed by our executive director…because every donor is their own campaign

As for payments, that has to do with the way we write the letter (our acknowledgement is also our receipt--I know some disagree with that process). So, we will have a sentence saying “Thank you for your gift of $100 (check #123)…” or something similar. The payments conditions are used to complete that sentence. And for DAFs, we use that checkbox to determine if the tax statement from the bottom of the letter. We also have a “check source” attribute to name the DAF, as we apply hard credits to the donor instead of their giving fund (again, this may be a controversial practice, but it is a hill we will die on over here )

Here's a sample letter--all placeholder text except for the first paragraph, where most of the template fields are: Sample Template.docx

@Erin McKenney
you do not neccessarily need that many word document template for doing merge, it really depends how different is one template from another.

So for example, if you have 2 letter code, one for regular cash donation and one for regular cash donation with tribute info, which the only difference between the 2 is maybe 1 extra line of text that say “in honor of XXXXX”, you do not need 2 template, instead just have 1 template that has the field for this line of text, which in your flow you can leave “empty" when populating the word document, or populate it with “in honor of xxxx” based on condition

example expression to demo this:

if(equals(lettercode, lettercodewithtribute), concat('in honor of', nameoftribute), ‘’)

same with ALL other template type, if it is minor differences, you can use same template.