1 Reply Latest reply on Jan 18, 2010 10:34 AM by philmodjunk

    Repeating field to track visits and forecast next visit

    Blueworld4

      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 

       

       

        • 1. Re: Repeating field to track visits and forecast next visit
          philmodjunk
            

          First to fix an obvious problem that appears in both calculations: 1/1/1900 is not a date in either example. It's a division expression: 1 divided by 1 divided by 1900. If you are going to use a date in a calculation and treat it as a date, you should use the date function: Date(1 ; 1 ; 1900 ) The original calculation probably worked for you as it seems to use the date to check for empty fields.

           

          I know you said "I'd quite like to avoid another related table for just review dates ", but I think you'll come out way ahead in the long run to define a table of related records for this. You'd need to use import records and/or some scripts to move the existing data into the new table, but the results are likely to be much, much easier to work with.