loop to find record matching criteria

Discussion created by tigerdad on Apr 1, 2014
Latest reply on Apr 1, 2014 by user19752

All, i have a detail table that needs the ability to have "Header Lines" and "Subtotal Lines" in the table itself. it is basically the detail protion of the invoice, but the end user needs to be able to dynamically break the items down into subgroups with a header and subtotal. However, i don't want to have a dropdown list on each line item to dictate what group it is in.


I have seen this done before in MS SQL, but don't remember how.


Line | Desc. | Amount

1 | general conditions | 1000

2 | Sitework | 1000


C000| Changeorders | - Header Row

C001|Change order 1 | 1000

C002|Change Order 2| 1000

C999|Subtotal|2000 - Subtotal Row


the user clicks a button to insert header, insert line, insert subtotal and there typically would be mutliple groups. building A,building B, change orders, ....etc.


I have found a custom fuction to sum a range given a start and end record number, my problem is I cant figure out how to "find" the record number that matches the "Header" or "Total" line type. I created the below custom function to loop up through the records, but it doesn't loop.


FindRecordUp(Criteria, fieldName, start)


Let(c=start;Case (GetNthRecord ( fieldName ; c )=Criteria ; Get ( RecordNumber );FindRecordUp ( Criteria ; fieldName ; c - 1 )))


when i monitor it as FindRecordUp("Total", payappdetail::type, Get(RecordNumber). if I am on the total record, it returns the correct record number, but if I am in any other record below that one then it returns the current record number -1, instead of looping up to the record and returning the correct record number.


I'm stuck. and I apoligize for being long winded.


As always, i appricate anyone's help or suggestions, or just plain a better way to do it, after all i am still a novice.