Process for determining gifts to funds from donors other than the fund trustee/manager

Kevin Mathe · April 11

Hello!

We're trying to determine the best way to report gifts to funds by others in a timely fashion. This would be pulling a list of gifts made to funds with active trustees (relationship) by people other than the trustee.

My first thought uses multiple queries - a fund or constituent query to determine the funds in question along with contact info for the trustees and a gift query to determine gifts to those funds. The two reports would then be compared to remove any gifts to the funds by the trustees. The remainder should be gifts by others.

Is this the only way to do this, or is there a more elegant solution? Ideally this would be a recurring task (e.g. Kevin, the following made gifts to your scholarship over the past month).

Dariel Dixon · April 11

Welcome to the community @Kevin Mathe. I feel you're overcomplicating the issue. Creating a gift query and just outputting the constituent information would be a pretty easy way to pull that information. If you feel that this will be a regular thing, I would also use a criteria or CONS ID Not One Of [trustee cons ID] and save them for the specific funds.

Alex Wong · April 11

@Kevin Mathe
this is not hard as long as your structure is clear within the system.

“funds with active trustees (relationship)” Please clarify what you mean by this, is it a fund relationship?

there is no need to manual “remove” gifts in my opinion, just need to know how your stuff is coded, then query for them

Kevin Mathe · April 14

@Alex Wong Thank you for your reply. Fund trustee is a fund relationship for funds that have external originators that receive reports, are contacts with any questions, etc. These trustees have changed over time, and funds that I’m looking to report would change over time as new funds are added and/or trustees no longer being active.

To identify these funds, I was looking for funds with the specific fund relationship and the date to field as blank. That should indicate a current relationship.

Where I am struggling is the query bringing in funds that aren’t needing reported due to gifts split between multiple funds and narrowing down the gifts to those by people other than trustees (outside of a manual process).

Let’s say there are 500 funds I’m looking for when I run the report today. I could have it so gifts by people without the trustee relationship are ignored; however, I only want them ignored if the trustee is giving to their own fund. If I am the trustee on the Kevin Mathe Scholarship but also give to the John Doe Scholarship (of which I am not the trustee) I want the output to ignore my gift to my fund but include my gift to the John Doe Scholarship.

Alex Wong · April 17

@Kevin Mathe
While Fund Relationship is direclty available in Gift Query as critieria and output, since you say there can be more than one fund relationship (some “active” some not), you will need to run 2 queries: Fund query and a Gift query.

Fund Query
- filter on all fund that has an “active” fund relationship (Date To is blank)
  - since there are 2 sections for relationship (individual and org), you will need to do some OR condition with paranthesis
- output the fund id, individual and org relationship Specific Record

d8dcd6161563add5813486ac2b2fd3de-huge-im

Gift Query
- filter: do your normal filtering for gift
- output, other than your normal output field, make sure to include the Constituent > Specific Record field and the Fund ID field

in excel, you can create a column in the gift query with a formula that looks for if Fund ID AND Specific Record is the same as the Fund Query's output. Then filter on these and delete the rows. Done, no need to manually look.

I am making an assumption you are familiar with handling of gift to gift split 1 to many relationship when outputing split info (fund split). I am also assuming that you are familiar with the excel formula COUNTIFS, let me know if you need additional help with them

Kevin Mathe · April 17

Thank you for your suggestions! I will implement them and see how it goes.

Process for determining gifts to funds from donors other than the fund trustee/manager

Comments

Categories