nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Endowed Donor Letters

Elizabeth Rich

Hello Colleagues,

Is anyone using RE to create stewardship letters to endowed fund donors? I am trying to incorporate the corpus of their fund, how much was distributed and to whom. I'm trying to do a Word merge through Export. I would love to see what others are doing.

Thank you.

Find more posts tagged with

RaisersEdge7 Databaseview

Comments

Elizabeth Johnson

Hi Elizabeth Rich‍,

I have set up our RE to run our stewardship letters through a word merge using export.

I hope you find this helpful. I'm curious to see how others might do this as well. This process feels pretty clean to me. We do this once a year and sections with the word formulas doesn't change. We have a few reportees that we report on for a few funds and we simply work that into the same letter. Those end up being three pages.

I have a massive deadline next week so if you have any follow up questions it would be better to wait to list after 5/25/20 to post so it doesn't get buried.

Best,

Elizabeth

Our "Fund Reportees" are added to the funds as a relationship. When a fund reportee passes away it is part of our deceased processing to see if there is another person that should become the reportee, spouse, child, classmate, lawyer, etc.

4afe6b41019ab9f19ddf8f7f847be412-huge-fu

The fund has many attributes to make this work. The financial ones I export using query (I desire the one to many results) each fall and share with our Director of Finance and she populates the corpus numbers for each fund and returns it to me and I import the info back into RE. I only keep the previous year's numbers. I do proof the work before importing and compare starting numbers with the previous year's ending numbers and that the proofing balance comes out as 0.

43f8e0c3eecac81244b770354e1261ff-huge-at

I go over the list of stewards and fund reportees with my director of development and we make any necessary changes and he starts reaching out to the stewards for material to include in his letters.

For the most part, the primary addressees and salutations work for the reportees. I have two that don't so they have a special fund reportee addressee/salutation on their records.

December 4, 2019

Dear <<Fund Reportee>>,

Something timely about what is going on at the school . . . .

Information about the overall endowment and an invitation to give again.

Info specific the category of fund in the letter.

With all of that as background, let me take a moment to fill you in on <<Fund Short Name>>, established in <<Year Established>> And <<purpose>>

Balance as of 6/30/18: $4,649,608

New contributions: $0

Earnings: $230,290

Distributed for use*: ($146,031)

Investment fees: ($27,278)

Fund balance as of 6/30/19: $4,706,589

*subject to board spending policy, not more than 4% on a 12 quarter trailing average

As you know, <<Steward Title>> <<Steward Name>> has primary responsibility for ensuring that these resources are used to best fulfill the fund’s goals. <<Steward Pronoun>> offers this update:

Yadda Yadda Yadda Yadda Yadda Yadda Yadda Badda Bing Badda Boom Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Badda Bing Badda Boom Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Badda Bing Badda Boom Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Yadda Burma Shave

We are grateful for your ongoing interest and hope you will find time to come see the school in action before too long.

Warmly,

Karen Diener

Excellent, thorough response from Elizabeth Johnson‍! I used essentially the same process at an organization years ago, and it worked quite well.

I do remember needing to run two exports and using VLOOKUP or the join function in Access to knit two files together, but I cannot remember the reason exactly. I think that we just couldn't get quite the right amount of information from one export, so we did two separate exports and combined the Excel sheets. We did report the current year plus the previous three years to the donor, so we did have multiple sets of Fund attributes. It all worked pretty well, once we sorted out the process though!

Karen

Elizabeth Rich

Karen Diener‍ I expect it is because you can't get both the donor information and the fund information into the same export. Your response confirms my thoughts on how I need to do this. Thank you and thank you to Elizabeth Johnson‍ for your response, also confirming my thoughts. Do you include the awardee name(s) in your letter?

?

Karen Diener

At that time, I was at a hospital foundation so there were no awardees.

I had occasion to look in to that later while working with a school where there was an awardee assigned. For years, they had been tracking the link to the Fund as an attribute on the student's primary education record - the Attribute was a yes/no, and the Fund ID was in the Comments of the attribute. So I had been considering if they should just rely on that, shift the relationship to the Fund, or use both. I left before the organization got to that point of needing a decision.

Karen

Elizabeth Johnson

Hi Elizabeth Rich,‍

No, like Karen Diener‍, we do not include awardee names in the letters, with do a paragraph more of a state of affairs along with some inserts with infographics about financial aid for example. I'm able to get both the fund info and the donor info into the letter from the same export because of the fund relationship. What happens separately for us is the merging of the write-ups from the stewards. We could add them to the records but it is easy enough to cut and paste into the letters. Our volume maybe on a much different scale than yours. We have just shy of sixty funds that we report on annually.

Best,

Elizabeth

Marlene Kemski

Hi all,

I'm very interested in this topic. We produce Endowment Donor Letters annually: One letter per constituent per each endowment fund for which they are an endowment fund contact.

Much like Elizabeth Johnson, we place our 7 fund balances as attributes in the fund record. We run various audit reports to ensure they match the excel from finance. We also have the fund attribute of Yes / No so we could "turn off" those funds that have been changed or cancelled. We only keep the current year balances in the fund attribute. A copy of our letters are kept as reference. Before Fund Relationships existed we use to keep an extra fund attribute with the Constituent's ID. However this didn't cover the situation that more than one constituent could be an endowment fund contact for the same endowment. So when Fund relationships feature was added to RE we start to use it to connect each Constituent that is an endowment fund contact to the appropriate endowment fund. This was better, however, it seems like this feature falls short when it is time to report on the endowments to the constituent. I can query and export the constituents with fund relationships. I can query and export the funds with fund relationships. But as Karen Diener and Elizabeth Rich mentioned, there is no way to select from the Constituent thru the relationship and obtain fund and fund attribute data using query and export. There is also no way to do this going from Fund to Constituent via fund relationship. So to take the Fund data and then the Constituents for Individual and the Constituents for Organizations and do the manual vlookup was a lot of manual labour that just seemed should be unnecessary and open to human error. We have many endowment funds. Last year we did the vlookup method.

This year I have written several Fund Audit reports within RE using query, exports, and crystal report. I have asked our DBA to directly read from the database using SQL and packaging it within Crystal to produce 4 outputs:

Constituent Individuals and each fund for which they are a contact (excel).

Constituent Organizations and each fund for which they are a contact (excel).

Constituent Individuals and each fund for which they are a contact (letter).

Constituent Organizations and each fund for which they are a contact (letter).

The excel files allow staff to quickly check various fields and notify others of required updates.  The letters are to be produced once all data has been reviewed, approved, and corrected. There is no vlookup necessary. The only draw back I see is that any changes to the letters would need to be requested and then modified by the programmer in the Crystal report. But this would be once a year.



We really don't like implementing a solution "outside" of RE as it doesn't make sense to pay for a great software and then have to work around it. We are able to implement this solution right now as we are using Raiser's Edge and are hosted locally. We can't do this if/when we move to NXT which will be remotely hosted.

If anyone of you have found a way to use Query, Export, Crystal without having to resort to a DBA using SQL behind the scene to complete the ability to report Constituent and Fund data via Fund Relationships, I'd be very happy to hear from you.

Marlene Kemski

Systems Analyst

University of Windsor

Elizabeth Rich

Hi Elizabeth Johnson‍,

Would you please share a screen shot of the export you use for your letters? How do you get the donor's address in your letter? Thank you.

Elizabeth Johnson

Hi Elizabeth Rich‍ I should be able to get you that screenshot this weekend. Still buried but wanted you to know I so respect that you waited until after 5/25 to ask!

I hope this finds you healthy and safe.

Best,

Elizabeth

Elizabeth Rich

Elizabeth Johnson‍ We Advancement Services types are good at following rules. ?

Elizabeth Johnson

Hello Elizabeth Rich‍,

Here are your screenshots

I'm not sure why these are looking so small you can get to the original sized format here or better yet - just click on the images.

f0958e411457bb1d0b4b5006ebc0524c-huge-qu

8175429972c9b17539c47dbd292d9a5e-huge-ex

Thanks for your patience

Elizabeth

Elizabeth Rich

Thank you!