Start/End Date Calculation

[hcmbrbj]

I need to figure out a calculation giving a results based on dollars due each month when the results may or may not over lap years. I have a pledge amount of x due either monthly, bi-monthly, quarterly, semi-annually or annually as pledge type. A start date field is established and the due dates are determined by the pledge type, which gives a number of payments. So far I've determined what is due each month from all pledges, but with a pledge overlaps a year it's throw in to the summary as well i.e. Joe pledges $200 a month beginning in Sept. 2009 and running through Aug. of 2010. Sam pledges $100 a month beginning in April of 2009. The desired results for Sept. 2009 would be $300 (which I'm getting in the summary) and the desired results for Aug. 2010 would be $200, but I'm getting $300. Any suggestions would be appreciated.

[TheTominator]

You haven't given much detail on your data model. That would be helpful in providing the context for the desired calculation.

But I can make the following suggestion.

When the pledge is created, you could populate the payments table with the scheduled payments. These would be designated appropriately in a payment_type field.

This would make your reporting on expected pledge payments as easy as your reporting on received payments currently is.

With this system when a pledge payment comes in, you would edit the appropriate scheduled payment record to convert it into a real payment record.

[hcmbrbj]

As it stands now the due date for Date Due one is:

Case (Type Pledge = "monthly"; Date ( Month ( Start Pledge Date ) ; Day ( Start Pledge Date ) ; Year ( Start Pledge Date ) ))

and accordingly for the others based on Pledge Type.

I'd like to have Start Date and End Date fields for the user to complete and get a results that produced results over two or three years according to the pledges made. Currently the monthly sum doesn't have any conditions for year variance i.e. Jan. 2009 pledges are summed with Jan. 2010 and 2011.

[Lee Smith]

See if this recent Thread helps

Link

[hcmbrbj]

That's a Thread I posted which doesn't relate to this. I'm having trouble adding a zip attachment; it's been at it for 20 minutes now. Please contact me off line at [email protected] and I'll send it to you directly if you don't mind taking a look at the file. Thanks.

[Lee Smith]

Unless there is a problem with attachments again, it does happen from time to time, you should be able to add an attachment to your Reply, by following the steps I posted here. Link

[hcmbrbj]

That's exactly what I did. Tried again and gave up after 30 minutes. There must be a problem with the server not accepting downloads. Mine is only 242KB.

[Lee Smith]

Send it to me at (received and posted here)

Copy of file sent to me off list.

Donation_Tracker_v42a.fp7.zip

Edited January 7, 2010 by Guest

[hcmbrbj]

Any suggestions?

[comment]

I suggest you review the thread I linked to in your previous thread, esp. this part:

I think I would start by turning the individual due payments into separate records in a related table