2 Replies Latest reply on Mar 21, 2011 9:30 AM by philmodjunk

    How to use GetNthRecord basing Nth on a criterion ?

    wladdy

      Title

      How to use GetNthRecord basing Nth on a criterion ?

      Post

      In a table called ‘Shows’ , records have three basic fields:

      • ‘ShowStart’ (timestamp)
      • ‘ShowEnd’ (timestamp)
      • ‘ShowType’ (an integer from 1 to 9)

      Shows are always sorted by increasing ShowStart and there is no overlap (ie: no show starts before the previous one has ended).

      Thanks to PhilModJunk in another thread, I learned the best way to calculate the time between the end of one show and the beginning of the next one:

      TimeToNextShow = ( GetNthRecord (ShowStart ; Get (RecordNumber) + 1 ) – ShowEnd

      Now, I am trying to calculate the time between the end of one show to the beginning of the next one of the same type.  The problem is that I do not know how to change ‘Get(RecordNumber)+1’ into something like ‘Get the number of the next record with an equal ShowType’.

      So far, I found the following workaround, which uses embedded Ifs to replace the ‘1’ in ‘Get(RecordNumber)+1’ with a number designating how many records away is the next event of the same type.

      TimeToNextShowOfSameType =

      GetNthRecord(ShowStart;Get(RecordNumber)+

      If (GetNthRecord(ShowType;Get(RecordNumber)+1)=ShowType ; 1 ;

      If (GetNthRecord(ShowType;Get(RecordNumber)+2)=ShowType ; 2 ;

      If (GetNthRecord(ShowType;Get(RecordNumber)+3)=ShowType ; 3 ;

      If (GetNthRecord(ShowType;Get(RecordNumber)+4)=ShowType ; 4 ;

      If (GetNthRecord(ShowType;Get(RecordNumber)+5)=ShowType ; 5; 6 )))))

      ) – ShowEnd

      As clumsy as it is, this monster works as long as two shows of the same type are not more than five steps away. In my attempts to find a more elegant solution, I have also tried creating a new layout with ‘ShowType’ and then ‘ShowStart’ as the sort criteria. I thought that it would enable me to keep using Get(RecordNumber)+1, but I am discovering to my surprise that the new sort order does not change the contents of the calculated field! In any case, it would create other problems with summary fields not mentioned in this discussion.

      I’m sure that there is a best practice to be used here, and I would greatly appreciate being put in the right direction to find it. Thanks in advance. W.