Report Writer: For loop redux

The For loop in the MoneyWorks report writer can be used in several different ways:

  • Iterate over records in a table in the database. You select the table and specify a search expression to select records, an optional sort (up to two fields, each ascending or descending). The loop identifier is the current record whose fields can be accessed as Ident.fieldName. Ident on its own is a 1-based index.
    The search expression can be relational (using [table:search]... notation).
    In a nested loop, a relational search expression using the push operator can be explicitly optimised with a double push (^^). Use this to indicate that the initial term(s) of the search are loop-invariant. MoneyWorks will save the result the first time round and will not re-execute that part of the search. e.g.:
    [detail:detail.period = per]^^[Detail:Detail.Account = acc.concat]*
    Note that this rather difficult to understand notation has been superseded in 6.1 by CreateSelection/IntersectSelection.
  • Iterate over a range of integers, specified as a List defined as start...finish (e.g. 1...20). The loop identifer takes on the value of each integer. The order is always increasing. The list definition can be constructed as a string result of an expression (e.g. ="1..."+VAR)
  • Iterate over a comma-delimited list. The loop identifier takes on each value in the list, in order. The list can be the result of an expression.
  • Iterate over a newline-delimited list. In this case, the list is always specified as an expression, beginning with an = sign. The loop identifier takes on the value of each line in the resulting text. Note that if lines contain tabs, these are respected if you output the line in a heading part (each tab-delimited value appears in a column).
  • Iterate over a table where the selection is specified by a Selection type variable. New in v6.1 are the CreateSelection() and IntersectSelection() functions. These take a search expression and optional sort expression.
    =CreateSelection("tablename","searchexpression" [,"sortepxr"][, descend=0])
    The for-loop then behaves as for the normal records in table case. The advantage is that nested loops can be optimised by saving the results of time consuming searches in variables. You also have greater control over sorting. Naturally the table selected for the for-loop must match the tablename with which the selection is constructed.
    Example: Ledger Report 4 (with createselection).crep
  • Iterate over a text file. A list specified as the text file://pathname will cause the file to be opened and its contents treated as a newline-delimited list. If the named file is not found, a File Open dialog box will be displayed. This is, of course, platform-dependent if you do specify a path.
  • Iterate over a text file downloaded from a URL (new in 6.1.1). As above, but the text file is specified as a URL http://resource. This is not platform-dependent.
Posted in Reporting | Comments Off on Report Writer: For loop redux