FindRecordUp(Criteria, fieldName, start)
Let(c=start;Case (GetNthRecord ( fieldName ; c )=Criteria ; Get ( RecordNumber );FindRecordUp ( Criteria ; fieldName ; c - 1 )))
This looks dubious – aesthetically, as well as functionally; also, it would be a good idea to start with the optimistic case: maybe the current record is the one you're looking for …
The problem: Since you use start as a record pointer, you need to return that value as result when you find a match – not Get ( RecordNumber ), which is the record number of the record the calculation is executed.
And as always with a recursive function, define an exit condition: pointer = found count. Also, start with 1 and increment, so pointer = record number. (If you do it in reverse, you need to re-calculate the pointer value into a record number …)
Altogether, this would look like …
FindRecordUp ( criterion ; fieldName ; pointer ) = // where pointer should be 1; or use a $var as iterator
fieldName = criterion ; Get ( RecordNumber ) ;
GetNthRecord ( fieldName ; pointer ) = criterion ; pointer ;
pointer < Get ( FoundCount ) ; FindRecordUp ( criterion ; fieldName ; pointer + 1 ) // this is the "looping" (iterative) part …
or just plain a better way to do it
There probably is one, but since I don't really understand what you're doing, I've no suggestions to make in that regard.
If the database can be shared by ODBC, you can insert header and subtotal using 1 step SQL.
insert from ODBC[
SELECT header_columns FROM table WHERE criterion GROUP BY some
SELECT footer_columns FROM table WHERE criterion GROUP BY same_some