A client's accountant has asked if it is possible to produce a report in Adagio Payables that shows the following for each client for a selected range of dates:

Balance at start date
all transactions posted in date range
Balance at end date.

(Come to think of it, the same report would be helpful in Receivables as well.)

Any suggestions?

Ralph:

The difficulty is knowing what the Opening Balance is at some point in the past. I can think of 2 things, neither of which is perfect.

1) If the "End Date" is to always be Today, then you could take "Current Balance" from the Vendor table and add/subtract the transactions since the "Start Date" to calculate the "Opening Balance". The limitation here would be that they will likely want to be able to choose any End Date. Maybe you could calculate back to both the "Start" and "End Dates"?

2) Another option would be to export the Vendor balances to a transaction table in dBASE format at the end of each month. Then you could use Crystal to access the table through a subreport, find the correct record and calculate based on that figure and subsequent transactions. I've never tried this and even though it should work, it wouldn't be my first choice!

The other problem with any date range is that transactions are moved to history at Period End so you could find yourself trying to pull transactions from two different files which does not work. A bad solution to this problem is to never run Period end.

I'm sure an Adagio utility could be created that pulls the transactions into a separate file (somewhat like Adagio OE History) so that current and history are in one place. A Crystal Report to list the transactions with sub-reports to calculate the openning and closing balances would work.

I'm reasonably certain this could be done without breaking the bank.