Trying to run a query that excludes paid pledges (or at least notes them)

Valerie Weddell

The out of the box 'Pledge Payments due next month' is great, except that occassionally we have someone pay early, so they no longer have an outstanding pledge, however this OOB query doesn't remove the paid pledge payment. I'm trying to figure out how to remove them from my output, or at least note that it was paid (I'm a firm believer that more information is better than less so if it means multiple columns, that's fine) any suggestions??



Thank you!

Alan French

I can't see a canned report with the name 'Pledge Payments due next month', is it in the "Pledge and Recurring Gift Reports" subsection?



We created a custom report that does something similar. Instalment balance is available in Export (but not Query), and Crystal can suppress any records where the instalment balance is 0.



Intrigued to see if anyone has a more straightforward solution as I couldn't find a way to do this by query alone!

Sherrel Battles

Can you base the report on a Query that is "Pledge Balance Greater than $0.00?"

Valerie Weddell

Within the Queries it is a sample. The installment balance would be terrific, but I'm not seeing it in the Query output options. I can get a full outstanding balance on the entire pledge amount, but not on the specific installment amount. 

Valerie Weddell

Sherrel Battles:

Can you base the report on a Query that is "Pledge Balance Greater than $0.00?"

I was hoping this might work as well, but there seems to be a disconnect between the paid pledge gift and the original pledge. Although there's a link on the gift record, it doesn't actually indicate within the gift that it has paid off a portion of a pledge. On the original pledge it now shows a $0 balance for this installment, but I can't seem to link it all together so the query understands there is a payment. Sorry, this is likely confusing, but I'm trying to talk myself through this as I type. 

Alan French

Is it viable for you to create an export using your existing query? It would still include pledge instalments that have been paid, but if you included instalment balance in the outputs you could then export to Excel and manually remove any with a zero balance (or build a report in Crystal that will do that automatically)



"Pledge Balance Greater than $0.00" wouldn't work: if you had a pledge of $100 with 2 instalments of $50 each, where the first instalment was due next month but had been paid in advance, the pledge would still appear in the results because the remaining balance of the entire pledge is $50 even though it is not due yet.

Valerie Weddell

In an ideal world we will be using this query within a dashboard to drive staff to act on ourstanding/remind of upcoming, so really trying to keep it all as one query. 

Christine Cooke 2

How about this:

Gift Type equals Pledge

AND Gift Pledge Balance greater than or equal to $.01

AND Installment Amount Due greater than or equal to $.01

Valerie Weddell

Christine Cooke:

How about this:

Gift Type equals Pledge

AND Gift Pledge Balance greater than or equal to $.01

AND Installment Amount Due greater than or equal to $.01

This is exactly what we are trying to do, but really we need a Gift Installment Balance greater than or equal to $.01 - which unfortunately doesn't seem to exist. as there would still likely be an outstanding pledge balance for the full gift. 



Thanks

Cathleen Mai

Have you tried this?

Pledge Staus report (under Pledge and recurring gift reports), on the tab gift types check "include Pledges with outstanding balances only" - or show every pledge with its balance for full information.

Cathleen Mai

And if you really need to have it as a query- you could create it on the general tab of the report

Cathleen Mai

PS: Now that I am reading the earlier posts.. it seems you need it to be a query to put it on the dashboard? I am not using the Dashboard. Instead I do use Appomatic (Omatic software) which gives you the option to drag and drop reports, queries and pretty much everything else on the desktop/ main screen. It is free software and a great tool for me and the rest of the staff.  Hope this helps.

Elaine Tucker

In a gift query, there is Installment - Installment Amount Due and Installment Due Date, if you put those in your query, and then run the report based on that query with the parameters of due date = next month - would that give you want you want?

JoAnn Strommen

Okay - it may be in a reply post but I scanned the thread real quickly and didn't see it.  Too many emails after being out 1 day to read all thoroughly.  So sorry if this is a duplication of another post...



But, have you considered using Mail > Reminders > and generating an output query?  This will automatically remove any payments committed in RE from amount due.  You can output pledge, installment etc.  We use this one a lot.  (If you haven't used it before, Run Date needs to be the date you want to show pledges due as of  ex: end of October or end of calendar year. 



Just seems easier to me than tons of query criteria.

Valerie Weddell

Elaine Tucker:

In a gift query, there is Installment - Installment Amount Due and Installment Due Date, if you put those in your query, and then run the report based on that query with the parameters of due date = next month - would that give you want you want?

Unforutnately it doesn't separate out what has already been paid. So if someone pays in May for a June installment, they still show up on the query for June. But thank you!

Valerie Weddell

Cathleen Mai:

PS: Now that I am reading the earlier posts.. it seems you need it to be a query to put it on the dashboard? I am not using the Dashboard. Instead I do use Appomatic (Omatic software) which gives you the option to drag and drop reports, queries and pretty much everything else on the desktop/ main screen. It is free software and a great tool for me and the rest of the staff.  Hope this helps.

Interesting sounding software - thanks for the feedback!

Valerie Weddell

JoAnn Strommen:

Okay - it may be in a reply post but I scanned the thread real quickly and didn't see it.  Too many emails after being out 1 day to read all thoroughly.  So sorry if this is a duplication of another post...



But, have you considered using Mail > Reminders > and generating an output query?  This will automatically remove any payments committed in RE from amount due.  You can output pledge, installment etc.  We use this one a lot.  (If you haven't used it before, Run Date needs to be the date you want to show pledges due as of  ex: end of October or end of calendar year. 



Just seems easier to me than tons of query criteria.

Thanks, will definitely give this a try!