# Basic Recursive (Custom) Function to count matching records

Discussion created by gageler on Jul 19, 2018
Latest reply on Jul 23, 2018 by danshockley

I have a situation where I need to be able to count "next records" (in a sorted set), to determine how many are related based on two fields. I cannot use a relationship, as the two matching fields are only relevant if the records are sorted in a particular (date) order.

I have a pretty inelegant calculation that works:

Continuing Records =

Case (

PersonID = GetNthRecord ( PersonID ; Get ( RecordNumber ) + 10 ) and Target = GetNthRecord ( Target ; Get ( RecordNumber ) + 10 ) ; 10 ;

PersonID = GetNthRecord ( PersonID ; Get ( RecordNumber ) + 9 ) and Target = GetNthRecord ( Target ; Get ( RecordNumber ) + 9 ) ; 9 ;

PersonID = GetNthRecord ( PersonID ; Get ( RecordNumber ) + 8 ) and Target = GetNthRecord ( Target ; Get ( RecordNumber ) + 8 ) ; 8 ;

PersonID = GetNthRecord ( PersonID ; Get ( RecordNumber ) + 7 ) and Target = GetNthRecord ( Target ; Get ( RecordNumber ) + 7 ) ; 7 ;

PersonID = GetNthRecord ( PersonID ; Get ( RecordNumber ) + 6 ) and Target = GetNthRecord ( Target ; Get ( RecordNumber ) + 6 ) ; 6 ;

PersonID = GetNthRecord ( PersonID ; Get ( RecordNumber ) + 5 ) and Target = GetNthRecord ( Target ; Get ( RecordNumber ) + 5 ) ; 5 ;

PersonID = GetNthRecord ( PersonID ; Get ( RecordNumber ) + 4 ) and Target = GetNthRecord ( Target ; Get ( RecordNumber ) + 4 ) ; 4 ;

PersonID = GetNthRecord ( PersonID ; Get ( RecordNumber ) + 3 ) and Target = GetNthRecord ( Target ; Get ( RecordNumber ) + 3 ) ; 3 ;

PersonID = GetNthRecord ( PersonID ; Get ( RecordNumber ) + 2 ) and Target = GetNthRecord ( Target ; Get ( RecordNumber ) + 2 ) ; 2 ;

PersonID = GetNthRecord ( PersonID ; Get ( RecordNumber ) + 1 ) and Target = GetNthRecord ( Target ; Get ( RecordNumber ) + 1 ) ; 1 ;

0 )

The problem is that this works for now, but is unlikely to work then there are far more potentially matching records.

I know the solution is to create a custom function, but am stuck on the logic of recursion.

So far I have:

Let (

\$Count = 0 ;

If (

Field1 ≠ GetNthRecord ( Field1; Get ( RecordNumber ) + \$Count + 1  ) and

Field2 ≠ GetNthRecord ( Field2; Get ( RecordNumber ) + \$Count + 1 ) ;

\$Count  ;

\$Count + 1

) )

I am stuck on how to get the argument to loop, to give me the number of the first unmatching future record.

Any help would be appreciated.

Thanks

Phillip