2 Replies Latest reply on Apr 1, 2014 5:37 PM by user19752

    loop to find record matching criteria

    tigerdad

      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.

        • 1. Re: loop to find record matching criteria
          erolst

          tigerdad wrote:

           

          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

           

          Case (

          fieldName = criterion ; Get ( RecordNumber ) ;

          GetNthRecord ( fieldName ; pointer ) = criterion ; pointer ;

          pointer < Get ( FoundCount ) ; FindRecordUp ( criterion ; fieldName ; pointer + 1 ) // this is the "looping" (iterative) part …

          )

          tigerdad wrote:

          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.

          • 2. Re: loop to find record matching criteria
            user19752

            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

            UNION

            SELECT footer_columns FROM table WHERE criterion GROUP BY same_some

            ]