First and Last Gift on an Appeal

Julie Partridge

 Can anybody help please?  As part of a general tidy-up I want to add a start and end date to a large number (650) of inactive appeals records.  I want to use the first and last gift date to do this.  I can then import this detail onto the appeal records.  However, I can't find any way of pulling these dates out by appeal only by constituent. 

Jana Gay 2

Julie Partridge:

 Can anybody help please?  As part of a general tidy-up I want to add a start and end date to a large number (650) of inactive appeals records.  I want to use the first and last gift date to do this.  I can then import this detail onto the appeal records.  However, I can't find any way of pulling these dates out by appeal only by constituent. 

You can create a Gift Query based on the Appeal ID, with Date being the only output, then sort by date. This will give you the date range (First gift to last gift). Just change the Appeal ID to quickly get the dates. Yay! Cleanup is so fun! [:D]

Julie Partridge

Jana Gay:

You can create a Gift Query based on the Appeal ID, with Date being the only output, then sort by date. This will give you the date range (First gift to last gift). Just change the Appeal ID to quickly get the dates. Yay! Cleanup is so fun! [:D]

 Thanks Jana.  That's really useful, I was hoping that there was a way of pulling off only the first and last gift which would have made the whole process much easier and quicker.  I should have known better!  With over 600 appeals this isn't going to be a quick job! [:(]  Thanks again

JoAnn Strommen

Julie Partridge:

 Can anybody help please?  As part of a general tidy-up I want to add a start and end date to a large number (650) of inactive appeals records.  I want to use the first and last gift date to do this.  I can then import this detail onto the appeal records.  However, I can't find any way of pulling these dates out by appeal only by constituent. 

Julie - am trying to get what you're looking for.  You have inactive appeal ABC and you want to know the date of the first and last gifts to it?  Is that right? 

You can get this info - please reply if that's what you need and I'll explain where to go.

JoAnn Strommen

JoAnn Strommen:

Julie - am trying to get what you're looking for.  You have inactive appeal ABC and you want to know the date of the first and last gifts to it?  Is that right? 

You can get this info - please reply if that's what you need and I'll explain where to go.

Sorry - couldn't finish earlier the whole response due to a commitment away from office.  Anyway...

Go to Records > Appeal and select one of the appeals that you need gift info on.  Click on the View menu at the top of appeal record.  Here you have a couple options. 

Cleanest to me is View > Summaries > Appeal Summary.  General tab usually defaults here to all gift dates.  Unless you need to add other filters, you can just click Finish.  In the pop-up window, if View is not set for First/Latest/Greatest choose that option.  Once that is your selected view it will show you date of first gift, amount, type and constituent.  Next line is latest or last gift. 

Selecting View > Gifts will generate a list of all gifts to the appeal sorted by date if for some reason you want to see that.  To get first / last you just have to scroll though list of gifts from top to bottom. 

Handy feature is after you've viewed this appeal, you can use arrows at top of appeal record to go to next one, so you don't have to go back and choose every time.

And you're right there on appeal records so you can just enter dates there.

There's also the First/Latest/Greatest report but that would be a huge pain to run 600 times.  Just clicking through this way seems better.

Hope this helps it that is what you are looking for.

James Andrews

Julie Partridge:

 Can anybody help please?  As part of a general tidy-up I want to add a start and end date to a large number (650) of inactive appeals records.  I want to use the first and last gift date to do this.  I can then import this detail onto the appeal records.  However, I can't find any way of pulling these dates out by appeal only by constituent. 

I would probably do the following. It's going about things a little backward, but it should work.

1. Create a query of the appeals you're going to change.

2. Create a gift export. Include all records. Go to output and right click on the "New Gift Export" field that's in the output area and choose 'criteria.' Click on the filters tab. In Appeals, choose 'Selected', click on the query radio button, and put your appeal query in.

3. In your output, put the appeal ID , appeal import ID, and gift date. That's really all you need. Export to a text file, or if you'd like, export directly to Access, because that's what we're doing next.

4. Get your resulting file into Access, and create a query. Pick out the import ID, the appeal ID, and the gift date twice. Click the totals button up top, and choose Max for one of the gift dates, and Min for the other. Leave the AppealID and ImportID as Group By. (alternately use SQL; this is fairly easy to type in).

That will give you exactly what you need - appeal import ID, start date, end date. You can basically import it as is if you rename the fields.

Julie Partridge

James Andrews:
I would probably do the following. It's going about things a little backward, but it should work.

1. Create a query of the appeals you're going to change.

2. Create a gift export. Include all records. Go to output and right click on the "New Gift Export" field that's in the output area and choose 'criteria.' Click on the filters tab. In Appeals, choose 'Selected', click on the query radio button, and put your appeal query in.

3. In your output, put the appeal ID , appeal import ID, and gift date. That's really all you need. Export to a text file, or if you'd like, export directly to Access, because that's what we're doing next.

4. Get your resulting file into Access, and create a query. Pick out the import ID, the appeal ID, and the gift date twice. Click the totals button up top, and choose Max for one of the gift dates, and Min for the other. Leave the AppealID and ImportID as Group By. (alternately use SQL; this is fairly easy to type in).

That will give you exactly what you need - appeal import ID, start date, end date. You can basically import it as is if you rename the fields.

 Thanks very much to everybody, and I think to save having to go through over 600 appeal records one at a time, I'll try the Access route.  Thanks [:)]

James Andrews

Julie Partridge:

 Thanks very much to everybody, and I think to save having to go through over 600 appeal records one at a time, I'll try the Access route.  Thanks [:)]

I just realized that you don't really need to have the Appeal ID in your export at all, unless you need it to double check things. I mean, that's why I said to put it in there, but you don't need it for the technical aspect of things.