Querying installments

Shannon Stepanek:

You can also use the "Cash Flow Report" which will show Installment date by time period (monthly, yearly, etc) and will show what is past due.  However it will not show when a payment was made.

I certainly wish BB would come up with an Installment report which shows what you are asking.  Anyone know if this is logged as a suggestion?

Ian Atwell:
Sorry for the late reply - the notification email was stuck in my spam filter. I did manage to get my arrears report - although it was complicated. There may be an easier way, but this works for me. I can use this export for both arrears and write-offs. I used a "Blackbaud Report Writer Database (MDB)" type of export which creates an Access database. Opening the db gave me an error of a missing a table "MSysAccessObjects" so I created a new access database and simply exported the tables from the db that RE created to the new database. I am an Access guy, so from there I built my queries. You can attach query of all pledges with a balance greater than zero to the export, or run an export to excel of all pledges and their write-off amounts and dates, and create a static query of the gift id's that have a write-off. I was unable to create a query of pledges that have been written off, although it may be possible. On the output tab I chose - Gifts: Gift id, date, amount, balance, and Campaign info. Instalments: date and amount Instalments -> Payment: amount Instalments -> Write-offs: amount Constituent: id and name Write-offs: date and amount (*see below) From that data I can get a list of every instalment, and for each instalment I list how much is due, when it was due, what was paid, and what was written off. I can easily tell if any instalment is fully paid, partially paid, written off, or unpaid. From this I can create a cashflow-like report by month for write-offs, or list any unpaid instalment from a given time period (arrears). I plan on experimenting with linking the tables between the two Access databases for a more permanent solution. I hope that helps. Ian * I added the "Write-offs: date and amount" to the export so I could ignore pledges that were written off long ago, I was only interested in how the recent write-offs were going to affect our cashflow reports that we ran in November for forecasting.

James Andrews:
[quote user="Ian Atwell"] I used a "Blackbaud Report Writer Database (MDB)" type of export which creates an Access database. Opening the db gave me an error of a missing a table "MSysAccessObjects" so I created a new access database and simply exported the tables from the db that RE created to the new database. I am an Access guy, so from there I built my queries.

I plan on experimenting with linking the tables between the two Access databases for a more permanent solution. [/quote] FYI, exporting to Access directly is also an option out of RE, and then you don't get those errors that you get exporting to Report Writer format and opening in Access. If you export to Access 2000 format, you have the option to export separate tables for one-to-many relationships (an option you don't have if you export to Access 2007 format)

Using a split database is something I do all the time for reports that we need to refresh frequently Export to an Access 2000 db, then create a separate Access db and link your tables. Write your queries and reports in the front end db, and you can refresh the back end one from RE anytime, and have up to date data.

I did manage to get my arrears report - although it was complicated. There may be an easier way, but this works for me. I can use this export for both arrears and write-offs.

I used a "Blackbaud Report Writer Database (MDB)" type of export which creates an Access database. Opening the db gave me an error of a missing a table "MSysAccessObjects" so I created a new access database and simply exported the tables from the db that RE created to the new database. I am an Access guy, so from there I built my queries.

James Andrews pointed out there's an easier way, export straight to Access. Not sure how I missed that option...

You can attach query of all pledges with a balance greater than zero to the export, or run an export to excel of all pledges and their write-off amounts and dates, and create a static query of the gift id's that have a write-off. I was unable to create a query of pledges that have been written off, although it may be possible.

On the output tab I chose -

Gifts: Gift id, date, amount, balance, and Campaign info.

Instalments: date and amount

Instalments -> Payment: amount

Instalments -> Write-offs: amount Constituent: id and name

Write-offs: date and amount (*see below)

From that data I can get a list of every instalment, and for each instalment I list how much is due, when it was due, what was paid, and what was written off. I can easily tell if any instalment is fully paid, partially paid, written off, or unpaid.

From this I can create a cashflow-like report by month for write-offs, or list any unpaid instalment from a given time period (arrears).

I plan on experimenting with linking the tables between the two Access databases for a more permanent solution. Thanks for the tip, James.

I hope that helps.

Ian

* I added the "Write-offs: date and amount" to the export so I could ignore pledges that were written off long ago, I was only interested in how the recent write-offs were going to affect our cashflow reports that we ran in November for forecasting.