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

# How to use GetNthRecord basing Nth on a criterion ?

### 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.

• ###### 1. Re: How to use GetNthRecord basing Nth on a criterion ?

your solution to sort the records is fine:

I think there was something wrong when you made the new layout and sort because the GetNthRecord of the current table returns the Nth record of the found set according to how the current table is sorted ( see help file).

One possible reason is that you run the script twice: one in the new layout and one when you are back to the original layout

• ###### 2. Re: How to use GetNthRecord basing Nth on a criterion ?

Make sure that your calculation is defined to be unstored (click storage options).