Problem with Filemaker Date Calculation

Hello all, I'm having trouble with a calculation that I need some help in (FM 9 Advanced). I have 4 fields:

A date field called "subscribed date" (ex. 7/31/2008)
A number field called "Payment cycle in days" (ex. 30)
A date calulation field called "payment date" (ex. 8/30/2008)that adds subscribed date and payment date
A calulation field called " next payment date" that has a case statemnt that adds 30 days to show the next payment date(ex. 9/30/2008)(case statement is a few paragraphs below)

So lets say I subscribe on 7/31/2008. My payment date is 30 days so the calculation field comes up as 8/30/2008. So far so good.

Now here's my problem, I want the date to change on "next payment date" when the calculated date is here. So on 8/30/2008 I want to add 30 days so that the next due date is 9/29/2008 and so on.

As i said above,"next payment date" is a calculated field with a case statement that updates the new date but it only adds one month and then stops. I don't know how to loop this. I was using a calculated field but I can use a script that runs on open if I need to. The case statement for "next payment date" looks like this:

Case (Get(CurrentDate) > Next Payment Date; Next Payment Date + Payment Days in Cycle;
Get(CurrentDate) d Next Payment Date; Next Payment Date

My overall goal is that if I have a due date that is 7/30/2008 but I don't open the program for several months, when the program opens I want to have the correct next due date like 10/28/2008.

Hopefully I was able to convey my question and I appreciate everyones help.

Who is Participating?
webwyzsystemsConnect With a Mentor Commented:
I think the easiest way would be to create a field "pivotDays" that is simply a calculation of the number of days from due date to end of the month. your example:
Due Date: 7/30/2008.
Number of days in July = 31.
pivotDays=31 - 30 = 1
So, a recurring payment would be 1 day short of the end of the month.

The due date calculation would be:


This technique uses a little trick...if you pass -1 to the DAYS parameter of the Date function, you always get the last day of the previous month. So, we go -pivotDays, and we always get the same number of days from end of month.

Use a script to insert or set the due date upon opening of the solution. You can use the above automatically, but the calculation cannot be indexed. So - could be a lengthy wait in systems with large numbers of records.
oops - forgot to mention...the reason I used pivot days, is because if you just add a static number like 30 or 31 or 28 to the original due date...then slowly over months...the due date gets all screwy.
We won't go wrong by always putting the due date the same number of days from end of month. The due date might float around by 2 or 3 days, but never more than that.
this trick is necessary if you intend to use 28,29,30 or 31st as due date, otherwise adding a month, not 30 days, to the orignal date is enough.
whun450Author Commented:
Thanks for the feednack guys, exactly what I was looking for.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.