Tracking Monthly Payments

[ghettocottage]

Hi all. I have been working on a solution for tracking monthly payments for various accounts. The problem I was having was that there is a 10 day grace period on payments, so a payment due on September 29 might be paid in November, but should still be associated with the month of September.

I read over quite a few posts. Looked at various calendar solutions, and came up with something that seems to be functional, but I would like some feedback and thoughts. Soren (good man that he is) is looking over the project as a whole, but I am curious what others think of the basic idea here.

At first, I was going to create record for every month on a "Months" table and then just associate payments with that month, but that seemed a bit cumbersome to have to create records for every year.

Then, after the manner of some of the calendars I was looking at, I was going to use global fields to sort of create a monthly view, but this did not solve my problem of keeping a payment associated with a month, when it might be paid in another month.

Finally, I sort of combined the two approaches. Using the global table, one can browse to whatever month needed. When a payment is created in that month, a "Month" record is created as well in my "Months" table, which I use to track payments. This solves having to create records for an entire year by sort of automating the creation of months.

It looks like it will work. I have not put it live yet, as I wanted to get some views on what I am doing. Will this function over the long term? Anyone see any potential problems?

I have attached a file with the basic idea in place.

This is sort of a conglomeration of a few ideas I read in this forum, and one post on Dwayne Wright's blog, and a script from SeedCodes free calendar for changing the months.

MonthlyPayments.zip

Edited August 20, 2009 by Guest

[Søren Dyhr]

You've asked me to make contributions to this in the private section, but where were we debating this originally? The private thread assumed some sort of debate ahead!

--sd

[Peter (duksis3)]

You have payment date, isn't it?

And you have determined date of payment.

So - if difference between the dates is bigger than 10 days, then client must pay extra. If less - payment is in time and is OK.

I suppose that you have some status field - Paid, Delayed or so on.

You need to make simple calculation Status=If (date paid-date due) <10;"Paid";"Delayed").

Going forward you can add field for amount of payed and see +/- in your relationships with client.

And the best is to make mentioned fields and write a script triggerd from field "Amount payed", like this

If Is Empty Date paid, set field Date Payed=...today or special date, else Exit script.

I recommend to add some Custom dialog before changin conten in field Date Payed and Amount Payed.