Frequency

[Anuviel]

How would I set up a calculation too give out a frequency?

Every four months from the start date a certain event will occur?

In other words:

If the start date is 1.1.08 and current date is 2.1.08 the field would display 0.

If the start date is 1.1.08 and current date is 4.1.08 the field would display 1.

If the start date is 1.1.08 and current date is 4.25.08 the field would display 0.

If it is too difficult to setup the calculation due to months having different number of days, including the leap year the frequency of every 30 would also be fine.

Thank you.,

[jstaphse]

Why not use:

If (Get (CurrentDate)- StartDate ≥ 120;1;0)

This would give you the 4 month span without worrying about leap year.

Is your need critical to having the exact 4 month marker?

[comment]

It's not that difficult - but you need to clarify how you want to handle the "months having different number of days" issue. That's not a calculation issue, but a business decision. If start date is Jan 31, and frequency is every four months, Filemaker would put the next recurrence on May 31, but the one after that on Oct 1.

That is if Filemaker were calculating the due date - it's a bit more tricky when you want to do it backwards, i.e. check if today's is one of the recurrences. Because Oct 1 is also a recurrence of Feb 1 (also every 4 months), for example. But it's still doable - one only needs to know the rules.

BTW, if I read your date notation correctly, four months from 1.1.08 is 5.1.08, not 4.1.08.

[Mike J]

Hey can we expand on the topic. I'm trying recurrences and frequencies so that I can script events from these questions the user answers. I'm not that worried about business rules because we need to be strict about exact days from a start date.

I'm poor at date math and would ask if someone can throw a example at me how they might tackle recurrences/frequencies to generate several child records for a given record.

Thanks

Mike

[comment]

If the interval* is given in days, then the calculation is very simple - just add the number of days to the starting date, and repeat this for each recurrence.

However, if you want to generate records, you will need a script, not a calculation. Roughly, something like:

...

Go to Layout [ Child ]

Loop

New Record

Set Field [ Child::ParentID ; $parentID ]

Set Field [ Child::Date ; $startDate ]

Exit Loop If [ $recurrences ≤ 0 ]

Set Variable [ $startDate ; $startDate + $interval ]

Set Variable [ $recurrences ; $recurrences - 1 ]

End Loop

...

---

(*) A semantic note: "frequency" is not a good choice of terms here. Frequency is the number of recurrences in a given time unit, e.g. "three times a month". Strictly speaking, the time between recurrences is the "period", but since we're not dealing with wave theory, I prefer the more descriptive "interval".

[Anuviel]

That would give me 1 in my field for any date past the 4 month span. I need the 1 to be the result only on the date the 4 months is up then it would go back to 0.

Edited May 27, 2008 by Guest

[Anuviel]

Well, yes. I want to handle them regardless of business logic. I do not know if this is correct expression to use but I would say I want them to be handled strictly mathematically. So in this Example:

If start date is Jan 31, and frequency is every four months, Filemaker would put the next recurrence on May 31, but the one after that on Oct 1.

I do not want the occurrence on Oct 1st. It should be on September 31st.

So if the start date is 1.1.08 it would be:

04.1.08

08.1.08

12.1.08

On all other dates except those the calculation would return 0 and on those dates it would return 1.

Start date will also move. I do not need to know how many occurrences will happen in a year or 10 years for that matter, I do not need to know before they happen - I only need to know when they happen, the same day so the calculation would display 0 all the time except on those dates as explained above when it would show 0.

So according to the example above 5 years from now , so in 2013 on 4.1.08 the calculation would show 1 and on 4.2.2013 it would show 0.

-------------------------------------------------

Edit:

The Interval could also change. I would probably want to store the Interval length in a global field so that I can modify it depending on the requirements out of my control.

Edited May 27, 2008 by Guest

[comment]

I want to handle them regardless of business logic.

LOL, they cannot be handled regardless of business logic, they must be handled according to SOME business logic - the only question is which. If I understand your example correctly, this should suit you:

Let ( [ 

diff = Month ( Get (CurrentDate) ) + 12 * Year ( Get (CurrentDate) ) - Month ( StartDate ) - 12 * Year ( StartDate ) ; 

add = Div ( diff ; Interval ) * Interval ; 

newDate = Date ( Month ( StartDate ) + add ; Day ( StartDate ) ; Year ( StartDate ) ) ;

limit = Date ( Month ( StartDate ) + add + 1 ; 0 ; Year ( StartDate ) ) 

] ;

( 

Get (CurrentDate) = Min ( newDate ; limit ) 

)

This assumes Interval is always given as the number of months between recurrences.

So if the start date is 1.1.08 it would be:

04.1.08

08.1.08

12.1.08

OK, this logic I definitely do NOT understand.

[Anuviel]

Thanks, that was what I was looking for. Now if I wanted to change the Interval to days instead of months which parts would I modify? Cannot quite seem to find them...

Awesome Calculation - much appreciated.

* In reply to:

So if the start date is 1.1.08 it would be:

04.1.08

08.1.08

12.1.08

OK, this logic I definitely do NOT understand.

Ok so I believe the example above you would call three month interval? Just wanting to establish the correct way of counting.

[comment]

if I wanted to change the Interval to days instead of months which parts would I modify?

Just about all of them. It would be an entirely different calculation (a much simpler one), so if your interval can be specified in varying units, you would need something like:

Case ( 

IntervalUnits = "days" ; 

not Mod ( Get (CurrentDate) - StartDate ; Interval ) ;



IntervalUnits = "months" ; 

< the calc above > 

)

so I believe the example above you would call three month interval?

I don't know what to call it - perhaps a "rubber interval"? It starts with three months, then jumps to four.

[Anuviel]

Heh - you are right. My mistake, counted / calculated that wrongly. I see what the problem was.

Thanks for the help, appreciate it.

[Mike J]

The wave theory, such that described in physics

Interval

Period

Frequency

ugh. I hated this part of physics. Who knew it would come back an bite me in the @$$.

Thanks

M

Edited May 31, 2008 by Guest
I'm a dumb@$$

[comment]

Well, I said we're NOT dealing with wave theory here. However, it's often useful to THINK of cyclical phenomena in terms of waves. Here's an example of using a phase-shift to shorten this:

not ( DayOfWeek ( date ) = 7 or DayOfWeek ( date ) = 1 )

into:

DayOfWeek ( date - 1 ) < 6

See also:

http://www.fmforums.com/forum/showtopic.php?tid/190174/post/267220/#267220

[Mike J]

Thats right just misread it and thought..not this and went with it.

So you know we deal with interval and periods and mix and match them to find the ideal parameters

We describe it as Q3DX4 where the action happen every three days with 4 intervals. There is a period between these usually a week. This is a common dosing schedule for an anticancer drug. How would I us a custom function/script to all users to use

Action (better vocabulary for this I'm sure)

Interval

Period

Start date

Recurrence ([NextStartDate] in case they want all of the above to happen again)

End date (?)

I need to take apart your function to understand it.

Thanks

M

[comment]

I am not sure I follow this ("interval" and "period" are the same to me). It seems that "Q3DX4" would mean:

interval = 3 days

number of recurrences = 4

Given a start date of say June 1, the script I have posted above would create 4 records dated:

• June 1

• June 4

• June 7

• June 10

To repeat the entire cycle several times, you would need to supply another set of interval and number of recurrences. The script would use two nested loops: the outer loop would calculate the starting date for each cycle, and the inner loop the actual dates of each cycle.

[Mike J]

interval = 3 days

number of recurrences = 4

Yes you would be correct. I guess I was saying that if I had...

interval = 3 days

number of recurrences = 4

Number of [Days/Weeks/Months]* between Recurrences = 7

Number of Cycles** =3

Start Date = June 1

The result:

[color:blue]Cycle 1

[color:red]

• June1
• June 4
• June 7
• June 10

Days Between Recurrence = 7

[color:blue]Cycle 2

[color:red]

• June 18
• June 21
• June 24
• June 27

Days Between Recurrence = 7

[color:blue]Cycle 3

[color:red]

• June 30
• July 3
• July 6
• July 9

So the nested loops would count down the recurrence for each cycle/interval.

Let me think it through and test. I may come back for more help/send example for assistance.

Thanks

M

*UNITS= user would select there recurrence interval.

**Cycle = How many times all of this occurs.

Edited June 1, 2008 by Guest

[comment]

This is actually quite simple*, once you calculate the cycleInterval as:

eventInterval * ( eventRecurrences - 1 ) + cyclePause + 1

(cycleInterval is the time between cycle starts).

Using your example:

startDate = June 1

eventInterval = 3 days

eventRecurrences = 4 times

cyclePause = 7 days

cycleInterval = 3 * ( 4 - 1 ) + 7 + 1 = 17

cycleRecurrences = 3 times

Now, if your script uses a $counter that starts at 0 and increases by 1 at each iteration, the date of the current iteration's event can be computed as:

startDate + Div ( $counter ; eventRecurrences ) * cycleInterval + Mod ( $counter ; eventRecurrences ) * eventInterval

BTW, I believe your third cycle is incorrect and should really be:

• July 5

• July 8

• July 11

• July 14

---

(*) It's simple when the time units are consistent. Weeks are easily transformed into days - months are not.

[Mike J]

Hey Comment,

Figured it out. Got both loops working with perfection.

I have a question about Months Units

I'm using 30 as my multiplier if the user selects Months as there Interval between recurrences.

Is this correct?

Weeks would be 7

Years would be 365

Thanks

Mike

[Mike J]

My Script:

Set Variable [ $Start Date; Value:GUI_M::g_Start Date ] 

Set Variable [ $Interval; Value:GUI_M::g_Treatments interval ] 

Set Variable [ $Interval Units; Value:GUI_M::g_Cycle_Interval_Units ] 

Set Variable [ $Recurrence; Value:GUI_M::g_Treatments recurrence ] 

Set Variable [ $Recurrence Counter; Value:GUI_M::g_Treatments recurrence ] 

Set Variable [ $Cycle Interval; Value:GUI_M::g_Cycle_Interval ] 

Set Variable [ $Cycle Interval Units; Value:GUI_M::g_Cycle_Interval_Units ] 

Set Variable [ $Cycle Recurrence; Value:GUI_M::g_Cycle_Recurrence ] 

Set Variable [ $Mouse ID; Value:GUI_M::v_Selected_MICE ] 

Go to Layout [ “Events” (Events) ] 

Loop 

Loop 

Loop 

New Record/Request 

Set Field [ Events:_Date; $Start Date ] 

Set Field [ Events::__kf_Mouse ID; Let([ 

xID= LeftValues ($Mouse ID;1); 

nID=Substitute (xID; "¶";""); 

ID=Substitute (nID; "x";"")];ID) ] 

Set Field [ Events::_Type; "Treatment" ] 

Set Variable [ $Start Date; Value:Case ( 

GUI_M::g_Treatments interval_units = "Days"; $Start Date +GUI_M::g_Treatments interval; 

GUI_M::g_Treatments interval_units = "Weeks"; $Start Date +(7*GUI_M::g_Treatments interval); 

GUI_M::g_Treatments interval_units = "Months"; $Start Date +(30*GUI_M::g_Treatments interval)) ] 

Set Variable [ $Recurrence Counter; Value:$Recurrence Counter -1 ] 

Exit Loop If [ $Recurrence Counter  ≤ 0 ] 

End Loop 

Set Variable [ $Start Date; Value:Case ( 

GUI_M::g_Cycle_Interval_Units = "Days"; $Start Date +GUI_M::g_Cycle_Interval; 

GUI_M::g_Cycle_Interval_Units = "Weeks"; $Start Date +(7*GUI_M::g_Cycle_Interval); 

GUI_M::g_Cycle_Interval_Units = "Months"; $Start Date +(30*GUI_M::g_Cycle_Interval)) ] 

Set Variable [ $Cycle Recurrence; Value:$Cycle Recurrence -1 ] 

Set Variable [ $Recurrence Counter; Value:GUI_M::g_Treatments recurrence ] 

Exit Loop If [ $Cycle Recurrence  ≤ 0 ] 

End Loop 

Set Variable [ $Cycle Recurrence; Value:GUI_M::g_Cycle_Recurrence ] 

Set Variable [ $Recurrence Counter; Value:GUI_M::g_Treatments recurrence ] 

Set Variable [ $Mouse ID; Value:RightValues ( $Mouse ID ; ValueCount ($Mouse ID)-1 ) ] 

Exit Loop If [ $Mouse ID  ≤ 0 ] 

End Loop 

Go to Layout [ original layout ] 

I have to look at your calc to figure it out. Is that a recurrence. I'm not so good at them.

[comment]

I'm using 30 as my multiplier if the user selects Months as there Interval between recurrences.

Is this correct?

It's entirely up to you - if you want it to be correct, then it's correct. There's no 'correct' answer here (except "there" should be "their" ).

I suppose if user selects a cycle interval of 3 months, it makes more sense to renew the treatment every 90 days - as opposed to 89 days in some cases and up to 92 days in others. OTOH, users might wonder why a treatment that began on July 1 is renewed "3 months later" on Sep 29...

[Mike J]

How would I make that calc right then for months.

[comment]

I don't know what's "right". I just said there's no correct answer. Is there a difference between 'right' and 'correct'?

[LaRetta]

Mike, you need your Management to decide which example (that Michael gave) will be your rule for defining a 'month.' And get it in writing so down the road, when customers squawk or your sales team objects to the period, everyone will understand that Management made the decision on what is correct to THEM and that will stand. Once the law is known, you can write the calculation to fit that rule.

[Mike J]

That's funny, My wife says something similar.

"Would you rather be right or happy?"

Fortunately I'm a little flexible in the business rules since I work in research. Most treatment regimes work on the 30 day period.

I was interested in learning the way to handle it though, if I were held to more strict rules.

Thanks

M

[comment]

I think all practical methods have already been mentioned in this thread:

1. Filemaker's native way:

Date ( Month ( startDate ) + n ; Day ( startDate ) ; Year ( startDate ) )

2. Filemaker's native way with a limit:

Let ( [

newDate = Date ( Month ( startDate ) + n ; Day ( startDate ) ; Year ( startDate ) ) ;

limit = Date ( Month ( startDate ) + n + 1 ; 0 ; Year ( startDate ) )

] ;

Min ( newDate ; limit )

)

3. Just add 30 days for each month:

startDate + n * 30

Alternatively, use the average number of days in a calendar month (30.436875).

None of these methods is more correct or strict than any of the others. Each one produces "illogical" results in some circumstances. Of course, the results are only illogical when judged from within a logic other than the one applied in the calculation - but they still look weird to the average human mind.

See also:

http://www.fmforums.com/forum/showpost.php?post/263667/