Problem with Filemaker Date Calculation
Posted on 2008-11-06
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.