AnsweredAssumed Answered

Repeating field to track visits and forecast next visit

Question asked by Blueworld4 on Jan 17, 2010
Latest reply on Jan 18, 2010 by philmodjunk

Title

Repeating field to track visits and forecast next visit

Post

I have an old database (9 years+) to update which seems to be programmed inefficiently.  I understand the issues with repeating fields but feel they may be appropriate.  Currently the database tracks various functions for learners at a small college. One of the related tables just looks after learner paperwork and contains contact details plus the dates of all key documents most of which occur once per learner.

 

One document which tracks formal review meetings, occurs up to 45 times and is currently held in 45 unique fields then forecast in 45 other fields which each have Case calculation which is very slow if the learner has a lot of review meetings.  Essentially the current Case calculation looks for a previous review meeting and adds 56 days unless the learner has left in which case the calculation will return the final due date as the leave date (all learners have an exit review) and from that point forward a blank field.  If the leave date is greater than the last review date plus 56 (due date) it will also return the due date plus in the following field the Leave date (exit review).

 

 

The current Case calc is for review visit 3:

 

 

Case ( 

 

reviewactuallongdate02  > 1/1/1900 and IsEmpty ( Most Recent Leave Date ) ; reviewactuallongdate02 + 56 ;

 

(Most Recent Leave Date < reviewactuallongdate02 + 56) and Most Recent Leave Date > reviewactuallongdate02 ; Most Recent Leave Date ;

 

(Most Recent Leave Date > reviewactuallongdate02 + 56) and reviewactuallongdate02 > 1/1/1900 ; reviewactuallongdate02 + 56 ;

 

reviewactuallongdate02 > Most Recent Leave Date and Most Recent Leave Date > 1/1/1900 ; "" ;

 

reviewactuallongdate02 = Most Recent Leave Date and Most Recent Leave Date  > 1/1/1900 ; "" ;

 

IsEmpty (reviewactuallongdate02) and Most Recent Leave Date > reviewactuallongdatedue02  and reviewactuallongdatedue02 > 1/1/1900 ; Most Recent Leave Date;

 

reviewactuallongdate03 > Most Recent Leave Date and Most Recent Leave Date > 1/1/1900 ; Most Recent Leave Date ;

 

reviewactuallongdate03 = Most Recent Leave Date and Most Recent Leave Date  > 1/1/1900 ; Most Recent Leave Date ;

 

 

"" )

 

I have tried two related repeating fields but I'm only able to get them do to very basic calculations and I'm new to this problem and needless to say the calculation below didn't work:

 

Case ( 

 

Extend (start date) > 1/1/1900 and (IsEmpty(Extend(Most Recent Leave Date))); GetRepetition ( reviewlongdatedue ; 1 ) = Extend (start date) + 14;

 

GetRepetition(reviewlongdate; -1)+56 > 1/1/1900 and (IsEmpty(Extend(Most Recent Leave Date))) ; reviewlongdate +56 ;

 

Extend(Most Recent Leave Date )< GetRepetition(reviewlongdate; -1) and Extend(Most Recent Leave Date) > reviewlongdate ; Extend(Most Recent Leave Date);

 

Extend(Most Recent Leave Date) > GetRepetition(reviewlongdate; -1) +56 and GetRepetition(reviewlongdate; -1) +56 > 1/1/1900 ; GetRepetition(reviewlongdate; -1) +56;

 

GetRepetition(reviewlongdate; -1) +56 > Extend(Most Recent Leave Date) and Extend(Most Recent Leave Date) > 1/1/1900 ; "" ;

 

GetRepetition(reviewlongdate; -1) +56 = Extend(Most Recent Leave Date) and Extend(Most Recent Leave Date) > 1/1/1900 ; Extend(Most Recent Leave Date) ;

 

 

"") 

 

 

I'd quite like to avoid another related table for just review dates as I have 3800+ old records each with between 15 and 45 review dates.  I also need to be able to analyse these old records to produce stats telling me how many hit the 56 day target. Then I will need to look at how many days +or- the target and look at percentages early or late etc.  Any advice on Repeating fields would be gratefully received. There doesn't seem to be a great deal of information out there. 

 

Regards Steve 

 

OSX 10.6

Filemaker 10 Advanced

Filemaker Server 10 Advanced 

 

 

Outcomes