Query on outstanding pledge balances

Amy Disch

I am trying to set up a query to see which constituents have an outstanding pledge balance, but I don't want to include employees who are making gifts via payroll deductions. I'm pulling the list for the purpose of sending out end of year pledge reminder letters.

My query is set up as such:

Criteria: Gift pledge balance greater than zero

Output includes name, preferred address, gift pledge balance, gift amount, gift date, gift type, fund description, lastest payment amount and date of latest payment.

My result list includes employees making payroll deductions, which I don't want, so my thought was to exclude a batch number that contains E (our payroll gift batches are all named with a sequence of numbers follwed by E to distinguish them), however including gift batch as a criteria would only reflect the original gift (the pledge) and not the pledge payments. I don't see a "lastest payment gift batch" field or the like, but perhaps I'm missing something. Many of the pledges in our system were entered as one offs, and don't have a batch number associated with them.

Any suggestions would be much appreciated!

JoAnn Strommen

Amy Disch:

I am trying to set up a query to see which constituents have an outstanding pledge balance, but I don't want to include employees who are making gifts via payroll deductions. I'm pulling the list for the purpose of sending out end of year pledge reminder letters.

My query is set up as such:

Criteria: Gift pledge balance greater than zero

Output includes name, preferred address, gift pledge balance, gift amount, gift date, gift type, fund description, lastest payment amount and date of latest payment.

My result list includes employees making payroll deductions, which I don't want, so my thought was to exclude a batch number that contains E (our payroll gift batches are all named with a sequence of numbers follwed by E to distinguish them), however including gift batch as a criteria would only reflect the original gift (the pledge) and not the pledge payments. I don't see a "lastest payment gift batch" field or the like, but perhaps I'm missing something. Many of the pledges in our system were entered as one offs, and don't have a batch number associated with them.

Any suggestions would be much appreciated!

Amy,

1. 
   
   Do you have any field for payroll that is different than other gifts?  We use gift subtype of payroll deduction. 
   
   
2. 
   
   Do you have staff that you want to send bills to that are not on payroll deduction?  If not, can you query all the staff pledges and merge queries with a SUB operator? Or do you have a constituency code, gift code or attribute that you can use to identify your staff.  These are just some of the ways I could filter out staff.
   
   
3. 
   
   Do the staff pledges have the "Send reminders" box checked?  If not, add that criteria to your query. 
   
   
4. 
   
   If you don't have any field to segment out your staff, what about payment frequency?  Can you exclude 'bi-weekly' or what every your payroll frequency is (this assumes you set up accurate payment schedules for your payroll gifts)?
   
   
5. 
   
   If you have the last payroll date you might be able to do "last payment/gift" not equal to and use that date.  But this would also exclude anyone who happened to make payment that date and would not exclude any staff who did not have a deduction that pay period.
   
   

Just some ideas.  If you can't filter them out by criteria, I'd look at SUB merging the query above and a query of the staff.

 

Michael Sherman

JoAnn Strommen:

Amy,

1. 
   
   Do you have any field for payroll that is different than other gifts?  We use gift subtype of payroll deduction. 
   
   
2. 
   
   Do you have staff that you want to send bills to that are not on payroll deduction?  If not, can you query all the staff pledges and merge queries with a SUB operator? Or do you have a constituency code, gift code or attribute that you can use to identify your staff.  These are just some of the ways I could filter out staff.
   
   
3. 
   
   Do the staff pledges have the "Send reminders" box checked?  If not, add that criteria to your query. 
   
   
4. 
   
   If you don't have any field to segment out your staff, what about payment frequency?  Can you exclude 'bi-weekly' or what every your payroll frequency is (this assumes you set up accurate payment schedules for your payroll gifts)?
   
   
5. 
   
   If you have the last payroll date you might be able to do "last payment/gift" not equal to and use that date.  But this would also exclude anyone who happened to make payment that date and would not exclude any staff who did not have a deduction that pay period.
   
   

Just some ideas.  If you can't filter them out by criteria, I'd look at SUB merging the query above and a query of the staff.

 

One more to add to JoAnn's well thought out list to try. (Sorry, I'm using Chrome and BB won't let me format in Chrome) 6. Use criteria "Batch number" does not contain "E".