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.

William
whun450Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

webwyzsystemsCommented:
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. So..in 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:

duedate=Date(Month(get(CurrentDate))+1;-pivotDays;Year(get(CurrentDate)))

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
webwyzsystemsCommented:
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.
0
lesouefCommented:
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.
0
whun450Author Commented:
Thanks for the feednack guys, exactly what I was looking for.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.