Problem with Filemaker Date Calculation

Posted on 2008-11-06
Last Modified: 2012-05-05
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.

Question by:whun450
    LVL 10

    Accepted Solution

    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.
    LVL 10

    Expert Comment

    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.
    LVL 28

    Expert Comment

    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.

    Author Comment

    Thanks for the feednack guys, exactly what I was looking for.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
    Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now