Endowed Donor Letters

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

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

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

 

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

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.





Thanks for your patience

Elizabeth