Calculating dates

[rlbryant]

Hello,

I am new to Filemaker Pro (7) and I am trying to figure out how to calculate a date based on another date. For example, if a "Last Payment" field contains 12/10/2004 how do I automatically fill the "Next Payment" field with 1/10/2005 or 3/10/2005? In other words, how do I add a specified amount of days to the "Last Payment" date value and have FMPro generate a date? I have looked in several texts and have not found any examples like this. Is there a way to do this in Filemaker Pro or is it not possible?

TIA,

rlbryant

[RalphL]

Welcome to the Fourm,

Next Payment = Last Payment + 30 is all you need to do a 30 day period. Change to 30 to the valuie you want. Next Payment is a calculated Date field.

[-Queue-]

Use Date( Month(Last Payment) + X; Day(Last Payment); Year(Last Payment) ) if you want 1/10/05 when X is 1, 2/10/05 when X is 2, etc. That way you don't have to calculate whether to add 28, 29, 30, or 31 days to get the correct number in the Day portion.

[rlbryant]

To RalphL and Queue - thank you for your answers. I am still having trouble, however, getting this to work. Here is what I have as the calculation for the field Date_Next_Payment_for_Classes:

If(Class_Payment_Plan_Type ≠ "Per Session";Date_Next_Payment_for_Classes=Date_Last_Payment_for_Classes + 28; Date_Next_Payment_for_Classes=Date_Last_Payment_for_Classes+70)

In other words, if the payment plan type is not "per session" (meaning the student pays monthly) I add 28 days (4 weeks) to the last payment date, otherwise I add 70 days (10 weeks). But the only result I get in the next payment field is a question mark. Can either of you see what is wrong?

Thank you again very much for your help!

rlbryant

Use Date( Month(Last Payment) + X; Day(Last Payment); Year(Last Payment) ) if you want 1/10/05 when X is 1, 2/10/05 when X is 2, etc. That way you don't have to calculate whether to add 28, 29, 30, or 31 days to get the correct number in the Day portion.

[RalphL]

If(Class_Payment_Plan_Type ≠

I don't understand this and don't think Filemaker does either. It may be the way what sent got delivered.

That statement should be more like this: If(Class_Payment_Plan_Type <> "Per Session"; where the <> is the not equal sign.

[-Queue-]

&#8800; is HTML for 'not equal to'.

"Date_Next_Payment_for_Classes=Date_Last_Payment_for_Classes + 28" is not valid syntax. The calculation itself should be the result for your Date_Next_Payment_for_Classes field and should look like

Case( Class_Payment_Plan_Type <> "Per Session"; Date_Last_Payment_for_Classes + 28; Date_Last_Payment_for_Classes + 70 )

or

Date_Last_Payment_for_Classes + Case( Class_Payment_Plan_Type = "Per Session"; 70; 28 )

or

Date_Last_Payment_for_Classes + 28 + 42 * (Class_Payment_Plan_Type = "Per Session")

if you want to get tricky.

[rlbryant]

Thank you again to RalphL and Queue. It is now working except that whenever a new date is entered for Last Payment, the Next Payment field will not recalculate unless the existing Next Payment value is wiped out before the new Last Payment value is entered. Is there any way to get FMPro to automatically update Next Payment whenever the value of Last Payment is changed (whether a value already exists in Next Payment or not)?

TIA,

rlbryant

&#8800; is HTML for 'not equal to'.

"Date_Next_Payment_for_Classes=Date_Last_Payment_for_Classes + 28" is not valid syntax. The calculation itself should be the result for your Date_Next_Payment_for_Classes field and should look like

Case( Class_Payment_Plan_Type <> "Per Session"; Date_Last_Payment_for_Classes + 28; Date_Last_Payment_for_Classes + 70 )

or

Date_Last_Payment_for_Classes + Case( Class_Payment_Plan_Type = "Per Session"; 70; 28 )

or

Date_Last_Payment_for_Classes + 28 + 42 * (Class_Payment_Plan_Type = "Per Session")

if you want to get tricky.

[-Queue-]

If Next Payment is a calculation date field or a date field with an auto-enter calculation and 'Do not replace existing value for field' deselected, then it should update when Last Payment is changed.

[-Queue-]

I just noticed this is in the Custom Function forum.

Stephen, can you please move this thread to a suitable forum?

[rlbryant]

Thank you very much to Queue and RalphL. My date calculcation is working now.

rlbryant