Export Constituents based on Query and list the Fund

Jessica Smith 4 · October 2015

Hello all! I need some help. I am trying to do an export for our annual report. I need to list constituents who gave to specific funds in our fiscal year. I don't need to know the date, how much, or anything like that. Just if they gave any amount to that Fund last year.

I created a constituent query with the Fund ID>One of>all the funds I want in this list. And Gift Date>between>7/1/14 and 6/30/15. It did what I expected.

Now I want to do an export and just list each donor if they gave to one of those funds and the name of the fund.

Ex: John Smith Basketball

Eve Jones Soccer

John Smith Soccer

That is where I get 815 results instead of the roughly 90 I am expecting based on the query. It is exporting every gift to those funds last year instead of just the name of the donor and the fund.

I don't really want to have to do a separate export for each fund or manually go through an Excel doc and clean it up.

Any suggestions?

JoAnn Strommen · October 2015

Are you using a constituent export or a gift export? If it's a constituent export each constituent will pull once. You'll need to pull additional gifts to see the additional gifts made - may be to same fund or a different one.. My first guess is multiple gifts for why the numbers are different.

If you are doing a gift export each gift will pull once. If they did lump sum gifts, vs. monthly or mulitple gifts this could work for you.

Alan French · October 2015

Hi Jessica,

I guess you're exporting from within the query, rather than creating a constituent export and then using your query as the selection criteria? Unfortunately you will get this sort of duplication when exporting results straight from the query.

There are probably better ways of doing it within RE, however for now you could use Excel's "remove duplicates" tool which will leave you with just the unique entries.

Jessica Smith 4 · October 2015

JoAnn Strommen:

Are you using a constituent export or a gift export? If it's a constituent export each constituent will pull once. You'll need to pull additional gifts to see the additional gifts made - may be to same fund or a different one.. My first guess is multiple gifts for why the numbers are different.

If you are doing a gift export each gift will pull once. If they did lump sum gifts, vs. monthly or mulitple gifts this could work for you.

I am using a constituent export because I need to see the people, not the gifts. If they gave once or 100 times, if they gave a million or a penny, doesn't matter. I just need to know who gave to that fund last year.

Jessica Smith 4 · October 2015

Alan French:

Hi Jessica,

I guess you're exporting from within the query, rather than creating a constituent export and then using your query as the selection criteria? Unfortunately you will get this sort of duplication when exporting results straight from the query.

There are probably better ways of doing it within RE, however for now you could use Excel's "remove duplicates" tool which will leave you with just the unique entries.

No, I am doing a query then going to export and creating the export with my query as selection criteria.

Jen Claudy · October 2015

This is because you are adding a Gift Field to the Output, so RE is returning all Gifts that fit your criteria.

Create a Constituent Export, based on your existing Query. Under Gifts (on the Output Tab for the Export), select Fund and then set parameters in the Gift Criteria box, including the number of possible Funds a donor might have given to (at the top of the General Tab) and which Funds to include (on the Filters Tab). When you export the data, you should have what you want...

John Heizer · October 2015

This will be tricky because there's a disconnect between the Query (which determines the Constituents) and the Export (which then knows nothing about which Gifts you're refering to in that Constituent Query).

This may be one of those rare instances where you want to export directly from the Query.

A Constituent Export to Excel format should give you only one line for each Constituent. When you select to Export the Gift information it should ask you how many Gifts you want to Export and will export a column for each of the Gifts, not an additional line/row for each Gift.

If you're trying to do a Gift Export based on the Constituent Query, then you'll potentially get ALL Gifts associated with the Constituent's in the Query.

One solution would be to do a separate Constituent Query and Export for each of the Funds. That way you know you're getting a one-to-one line up.

Christine Cooke 2 · October 2015

Why not use a Donor Category Report sorted by Alpha and utlize the Filters to choose just the particular Fund you are looking for. You can do one Report for each Fund. It can be saved as a query and exported that way. Or the more straightforward is to export the Donor Category Report itself from Preview.

James Andrews · October 2015

I would suggest you use a gift query and export to excel, and use a pivot table to list the names by fund.

I guess you could do a pivot report within RE, also. Base it on a gift query, not a constituent one. You'll still get the constituents' names.

Elaine Tucker · October 2015

I may be missing a critical point, or making it too easy, but could you just run the Gift Detail Report and export to Word and adjust as needed? You can include the Fund name in the report so it would show up as needed in the listing. You can also set the name formats so if you wanted to inlcude the Spouse, you can select that option. So you would then have the option of:

John Smith Basketball

Eve Jones Soccer

John Smith Soccer

or

John Smith Basketball (has no spouse)

Eve & John Jones Soccer (has spouse)

The John Smith Company Soccer (Prefers to have Company in the donor listing)

Kathryn Hurchla 2 · October 2015

Try adding the fund criteria again (a second time) to the bottom of your query criteria (i.e. Fund ID>One of>all the funds I want in this list.). Then export directly from the query output using the disc icon (include fields you need for name and fund). That may control the output, and may work even in a constituent query rather than gift query in your scenario.

It's not intuitive and seems unnecessary, but I've been surprised with this pleasantly before and if it works it can save you additional steps. Try it, and as usual with new processes make sure to audit your results by pulling it another way mentioned.

If that doesn't work, I'd add a filter by fund and date to your export, using the filter by fields or queries options (vary depending on whether you use a gift or constituent export).

Hope I'm not repeating any replies. And hi to James whose name brought me here to this post--his advice is excellent too as usual...

Faith Murray · October 2015

After you run your query, go to export. Create Constituent Export. For output, select Constituent Information-Name, and then:

Gift -- Funds -- Description.

In resulting pop-up box, select:

General tab -- "For each Constituent, enter the number of Gifts to export" -- enter: 1

General tab -- select appropriate date for fiscal/calendar year

General tab -- select start amount for minimum gift range

Filters tab -- Funds -- "Selected" -- select only the types of funds you wish to include

Gift Types tab -- select only those gift types you wish to include

This should give you one entry per Constituent, with just their name and the name of one fund in your Fund selection to which they have donated the previous year. There shouldn't be any duplicate lines. Even if you choose to output 2 Gifts Funds on the General tab, instead of one, it should export them as additional columns, not rows, on your spreadsheet. Should look like:

Debra Smith          Soccer

Bob Hope            Soccer          Football

Antonio Banderas Football         Basketball

Good luck to you!

Export Constituents based on Query and list the Fund

Comments

Categories