gageler

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:

CustomFunction.JPG

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

Outcomes