Hi all,
Devo has requested a process they can use to create itemized tax letters for FY2019. They want to be able to print and mail a letter that includes a message from our Director of Development and then has a table of each contribution a patron has made in a year (eg row 1 is a gift on 1/5/2019 to the Annual Production Fund, row 2 is a Gala ticket purchased on 8/9, etc.). I am able to use an output set to export all of the data I want to an Excel spreadsheet, but I am absolutely unable to come up with any process by which to take that data and put it into a single letter per patron by way of a mail merge in Word. I am wondering if anyone else has any similar processes that they are willing to share as I'm at my wit's end trying to get this to work. I've considered using a flattening technique in SQL but I'm nervous since that only really works well if you want a fixed number of contributions, and I don't want to have to re-code the process based on how many gifts the top donor in a year has given every single year.
We did this kind of thing when I was at BAM.
I found this YouTube video about using a “Directory”. This appears to be a newer Mail Merge feature.
Here is one about using NextIF
If I remember correctly when I was at BAM we used the NextIF method.
There is also a third party plugin. I’ve never used this. Go with due caution.
https://chrismenardtraining.com/post/many-to-one-mail-merge-in-microsoft-word
Let us know how you get on with this. This can be done with MS Word Mail Merge. However this is an advanced technique.
Good luck.
I just saw in the TNEW newsletter this morning that there's a new utility for this kind of thing:Need to generate year-end receipts? Introducing the Constituent Contribution Summary Receipt UtilityProvide a receipt that shows your constituents all the contributions they gave in a specific year with the new Constituent Contribution Summary Receipt Utility. This free utility can be used to pull a constituent’s giving history for any timeframe you specify, such as a quarter or a fiscal year. Receipts include a unique tax receipt number and PDF copy, as required by Canadian law. I'm very intrigued by this utility -- it might be exactly what you need as well! It suggests opening a ticket if you want the utility
Tom, thank you for this. I did find those two videos, but for some reason seeing it again helped me understand the step I was missing when applying it to the more complex template I'm attempting to make. For future forum uses, NextIf won't prevent future merge fields from printing, it just will print the same record again since it didn't advance, so I need to add a check on the proceeding lines to make sure the row has changed.
Between this and the new utility mentioned below I think I should be well on my way!
I ended up downloading this https://www.tessituranetwork.com/en/Files/Shared-Reports/Year-End-Letters-Report and editing the report template and proc to fit our needs. For example where Tulsa pulled fund, we wanted to pull campaign. And we got rid of the fair market value field. There were a few other edits as well. I can share our final product with you if you like. Would you be willing to share your final product once you have it all worked out? I am always looking for more than one route to an end goal.
Ashley
Ashley, I have some users in our consortium who would greatly appreciate your final product if you're willing to share with someone who has nothing to offer in trade...? Thanks in advance!
Done
Hope it helps!