Trying to calculate future dates

[JanetMermaid]

I am trying to use a set of Radio Buttons to determine a future date for customer service, based on the previous date of service. In other words, if the "Weekly" Radio Button is selected, and the last service was 10/7/05, then the calculation should return 10/14/05. If the "Monthly" Radio Button is selected, then the calculation should return 11/7/05.

I have done extensive searches and have found multiple example "templates" that appear to do the above (once properly filled in), but I haven't been able to get any of them to work. It is additionally confusing because the templates are all very different from each other, so I haven't been able to combine the bits of knowledge I pick up from each one to finally get a single calculation result that works. It probably isn't actually as difficult as it seems at the moment.

My fields are Last (for last date of service), Next (where the result should display), and Frequency (with radio buttons for weekly, 6 weeks, monthly, bimonthly, quarterly, other).

I have FileMaker Developer 6.0v4. I appreciate any help anyone can provide.

Edited October 14, 2005 by Guest

[-Queue-]

How about something like

Case( Frequency <> "other" and not IsEmpty(Frequency),

Date( Month(Last) + (Frequency = "monthly") + 2 * (Frequency = "bimonthly") + 3 * (Frequency = "quarterly"), Day(Last) + 7 * (Frequency = "weekly") + 42 * (Frequency = "6 weeks"), Year(Last) )

)

[JanetMermaid]

I tried your suggestion. I'm now getting the Last date value displayed in the Next field, but that's it, no matter what radio button I select. When I manually change the Last date, the Next field updates to the new value.

[-Queue-]

Make sure it is set as a calculation field, not a date field with an auto-enter calculation.

[JanetMermaid]

Yep, Next is a Calculation. Last is a Date. Frequency is a Number with Value List options of those I listed above.

[Lee Smith]

I made a sample from what I could gleam of queue calculation. Maybe it will help you.

Lee

Test_Janet.fp5.zip

[BrentHedden]

Since FM internally stores dates as "days since 01/01/0001", you can perform simple or even complex math on the dates.

For example, if I wanted to find out what the date will be one week from today, I calculate

Status (CurrentDate) + 7

If I want a month -

Status (CurrentDate) + 31

If a year -

Status (CurrentDate) + 365

well, actually the month will hang you up, since each month has a different amount of days.

Make sure your calculation is set to a date result, or you will get a really long number, or even weirder-looking results.

[JanetMermaid]

I understand the updating the date value part, but I can't seem to get the value to change based on the status of a radio button.

I did double check that the Next field's calculation result is Date.

[JanetMermaid]

Here's my latest attempt:

Case(Frequency="Weekly",(Month(Last)&Day(Last)+7&Year(Last)),Frequency="Monthly",(Month(Last)+1&Day(Last)&Year(Last)),Today)

It is displaying Today in the Next field, so at least the Case formula is running, although that's not saying much.

I'm obviously missing something basic, but I just can't see it.

[Breezer]

try make your calculation "unstored"

[-Queue-]

Unstoring the calculation will not help.

There should be no ampersands in the calc. If you want to separate each one using Case, then use

Case(

Frequency = "monthly", Date( Month(Last) + 1, Day(Last), Year(Last) ),

Frequency = "bimonthly", Date( Month(Last) + 2, Day(Last), Year(Last) ),

Frequency = "quarterly", Date( Month(Last) + 3, Day(Last), Year(Last) ),

Frequency = "weekly", Last + 7,

Frequency = "6 weeks", Last + 42

)