Repeating Field Calculations

[blueworld4]

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

[Søren Dyhr]

The current Case calc is for review visit 3:

But even that shouldn't be working correctly, it's urgent that dates not is put in simply as written, since a typecast will occur and what is written as 1/1/1900 is translated as 1 divided by 1 divided by 1900 ... I would urge you to use GetAsDate( or Date(1;1;1900)

--sd

[blueworld4]

Many thanks for getting back to me. I have updated the Case field and it appears to be working more quickly so definitely that error was slowing things down. Do you think that replacing the 45 case fields with one repeating field will boost performance further? If so, do you think it is possible to write it as a repeating field to save time on both programming time and user time. Because filemaker doesn't provide a progress bar, users are force quitting thinking that the database has crashed.

[Søren Dyhr]

Do you think that replacing the 45 case fields with one repeating field will boost performance further?

Not really ... it's the layout pulling the rendering anyway - only chance is if some sort of chained dependency on previous stored results could be established - but your structure isn't facilitating this.

--sd