Chapter 6—List Programming

Omnis has two structured data types; the list and the row. A list can hold multiple columns and rows of data, with each row having the same column structure, while a row is effectively a single-row list. You can create lists of strings, lists of records from a database, or lists of lists. You can define a list from individual variables, or base a list on one of the Omnis SQL data classes, such as a schema, query, or table class. In this case, the list gets its column definitions from the columns defined in the SQL class.

Each list can hold an unlimited number of lines with up to 32,000 columns, although you should be aware that the limitations on memory may limit the number of rows in a list with many columns. A row can have up to 32,000 columns.

The list is the single most important data type in Omnis programming. Omnis makes use of lists in many different kinds of programming tasks, such as generating reports, handling sets of data from a database server, and importing and exporting data. List variables provide the data (content) and formatting for many of the visual list components available in the JavaScript Client, including List boxes, and Data grids, as well as Bar and Pie Charts: the different types of visual list controls are described in the JavaScript Components chapter in the Creating Web & Mobile Apps manual.

In this chapter, rows are generally treated the same as lists, that is, you can use a row name in any command that takes a list name as a parameter. In addition, references to SQL lists in this chapter refer to lists based on either schema, query, or table classes, which are referred to collectively as SQL classes.

Note that most of the example code in this chapter is generic that can be applied to all list handling, including lists containing SQL data and list variables contained in Remote forms used for creating web and mobile apps; however, some of the example code may relate to window classes only, but the code may be easily adapted to work with remote forms.

Declaring List or Row Variables

You can create various scopes of list and row variables, including task, class, instance, and local variables. You declare a list or row variable in the variable pane of the method editor, or in the Create Variable dialog in the Code Editor. The following table summarizes the variable types and their visibility.

List or row type When created? Where visible? When removed?
Task variable on opening task within the task and all its classes and instances that belong to the task on closing task
Class variable on opening the library within the class and all its instances on clearing class variables or closing library
Instance variable on opening instance within the instance only on closing instance
Local variable on running method within the method only when method terminates
Parameter variable on calling the method within the recipient method returning to the calling method

To declare a list or row variable in the Variable pane

To declare a list or row variable in your code

You can declare a list or row variable directly in your code, by first naming it in your code and then using the Create Variable dialog, as follows:

When you type the name of a new variable in your code, you can specify the initial scope and type for the variable using a predefined prefix and suffix, respectively, so in this case you can type iCustomerList to specify an instance variable of List type.

Lists in the JavaScript Client

Like all variables you use in Remote forms to be displayed in the JavaScript Client, any list or row variables that you want to use in a remote form should be declared as Instance variables (or Local / Parameter as appropriate). Lists can be declared as task variables which are available to all instances in the current remote task instance.

Defining List or Row Variables

To define a list or row variable you need to specify its columns. You can do this using Omnis commands or the notation. You can define a list or row variable

If you want to use a list in a remote form (or any class that can be opened), you should define the list in the $construct() method of the class, or call a method from the $construct() that defines the list. This ensures the list is defined and in memory ready to be used in the current instance.

(A list can be defined from a File class, but this class type is only available for backwards compatibility in legacy apps using Omnis datafiles.)

Defining Lists from Variables

To define a list from a number of variables you can use the $define() method (the equivalent of the old Define list command in previous versions). For example

# The variables for the list columns need to be declared
# cvList1 of List type
# cvCol1 of Short integer type
# cvCol2 of Character type
# cvCol3 of Date Time (Short date 1980..2079) type

Do cvList1.$define(cvCol1cvCol2cvCol3)

This method will define the list cvList1 with the columns cvCol1, cvCol2, cvCol3. You can define a list with up to 32k columns, although you should limit the size of the list to only the columns that are required. The data type of each field or variable defined in the list determines the data type of the corresponding column in the list.

When $defineresolvesfieldrefs is set to kTrue (default is kFalse), if a field used to define a list is a field reference, Omnis resolves the field reference and defines/redefines the list using the resolved field.

Defining Lists and Rows from SQL Classes

You can define a list based on one of the SQL classes, that is, a schema, query, or table class, using the $definefromsqlclass() method (the equivalent of the old Define list from SQL class command in previous versions). Alternatively, you can create the variable in the Variables pane in the Method Editor, or the Create Variable dialog, and set a SQL class name as the Subtype of the list or row variable.

Defining a list or row based on one of the SQL classes binds the variable to the schema or query class and consequently maps the list’s columns to the server table. When you define a list or row variable from a table class, it can have its $sqlclassname property set to the associated schema or query class to get the definition from the SQL class. You can do this either in the Property Manager or using the notation.

Do  $clib.$tables.MyTable.$sqlclassname.$assign('MySchema') ## or

Do $clib.$tables.MyTable.$sqlclassname.$assign('MyQuery')

You can however use a table class without any schema or query class assigned in $sqlclassname. In this case, the table class has a $load method that has a manual SQL statement, i.e. with Begin and End SQL. The list variable will then define the list columns from the select result automatically.

The following example defines an instance row variable from a schema class called 'MyPictures':

# create iSqlRow of Row type
Do iSqlRow.$definefromsqlclass('MyPictures')

The full syntax of the $definefromsqlclass() method is as follows:

$definefromsqlclass(class[,row,parameters])

Where class is a schema, query, or table class (name or item reference to it), and the row and parameters are optional.

The row parameter affects the columns used when the SQL class is a schema or table referencing a schema. A row with no columns (or the parameter is omitted) means that the list is defined using all the columns in the schema. Otherwise if the row is specified each column in the row becomes the name of a column to add to the list definition from the schema. The parameters can be a list of parameter values that are passed to $construct() of the table class instance created by the method.

For example:

Do  list.$definefromsqlclass('schema',row('c1,'c2'))

would only include columns c1 and c2 in the list definition.

Do  list.$definefromsqlclass('schema') 

Would include all the columns in schema.

To include all columns and call $construct with parameters:

Do  list.$definefromsqlclass('table',row(),1,2,3)

This method passes parameters 1, 2, 3 to $construct and includes all the columns from the schema.

SQL table instance methods

When you create a list or row variable based on one of the SQL classes a table instance is created, so the list or row variable contains the standard properties and methods of a table instance. Specifically, if you create a variable based on a table class it contains any custom methods you have added to the table class; these can override the standard table instance methods. The following standard methods are available for lists based on a SQL class.

These methods offer a powerful mechanism for processing or inserting data on your server via your SQL list or row variable. For example, to fetch 30 rows into your list

# declare cvList1 of list type
Do cvList1.$definefromsqlclass(MySchema)
Do cvList1.$select() Returns myFlag ## sends a select
If myFlag = 0 ## checks for errors
  OK message {SQL error [sys(131)]: [sys(132)]}
End If
Do MyList.$fetch(30) Returns myFlag ## fetches 30 rows

# to fetch another 10 rows and add them to your list
Do MyList.$fetch(10,kTrueReturns myFlag

Defining Lists using SQL Workers

From Studio 10.1, you can specify that a SQL list or row will use a SQL Worker Object of the same DAM type as the SQL session object to perform SQL list operations asynchronously. See SQL Worker Lists.

Defining Lists from File classes

For legacy apps using Omnis datafiles, you can define a list based on a file class using the notation list.$define(filename). You can use the notation list.$define("lib.filename") to reference a file class in another library: note that the name must be passed as a quoted string. You can use the switch /s, e.g. "lib.filename/s", where s means skip columns with empty names in the file class.

List/Row subtypes

A Schema, Query, or Table class name can be used as the subtype of a list or row variable, that is, a class, instance, local, task or parameter variable, or a column in a list or row defined from a SQL class.

Omnis uses the subtype class to define the list or row, or in the case of parameters, to identify the expected definition of the list or row, although Omnis does not do anything if the definition does not match.

Schema classes have a property $createinstancewhensubtype that controls whether or not there is a table instance associated with a List or Row variable with a schema class as its subtype; you can set this property in the Property Manager when editing the schema class. The property defaults to kTrue for existing and newly created schema classes. When using the schema class exclusively with Web Services, it is likely that the table instance will not be required, and in this case turning off $createinstancewhensubtype will therefore improve performance.

Adding columns

The $addcols() method provides a short-hand way of adding one or more columns to a list or row variable. It has the following parameters:

list.$addcols(cName,type,subtype,maxlen,...)

which can be used to add one or more columns to a list or row variable, so the parameter count must always be a multiple of four. Each new column must be specified with the following four parameters:

Legacy List Commands

All the commands in the Lists and List Lines groups, such as Define list and Search list, have been deprecated in Omnis Studio 11 and are no longer visible in the Code Assistant in the Code Editor (they will not appear when you type the first few characters); however, they are still present in Studio 11 and will continue to function in legacy code. You can show these commands by disabling the appropriate Command Filter in the Modify menu in the Code Editor.

You should use the equivalent methods where available, such as $define() instead of Define list, $search() instead of Search list, $sort() instead of Sort list, and so on, to manipulate the contents of list variables. Various sections in this chapter will have example code for both the list methods and the old list commands, and for all new applications you should use the list methods.

Building List Variables

You can build lists from SQL data using a SELECT statement and the $fetch() method.

Building a List from SQL Data

The SQL SELECT statement defines a select table, which is a set of rows on the server that you can read into Omnis in three ways:

To transfer rows:

Do  mylist.$definefromsqlclass(SchemaRef,Lname,Town) ## define list
Do MyList.$select() Returns myFlag ## make select table
Do MyList.$fetch(10) Returns myFlag ## fetch 10 rows into list or
Do MyRow.$fetch() Returns myFlag ## fetch a row into a row var

You should avoid loading a large number of rows into the list, since this may interrupt the interface in your app; you could consider fetching a small batch before refreshing the screen. You can retrieve the rows in batches using the $linemax property which limits the size of the list, pausing after each batch to redraw the list field.

Viewing the contents of a list variable

You can view the current contents of a list variable by Right-clicking on the variable name in the Method Editor and selecting the “Variable <var_name>” option (the first option in the context menu), which will open a table containing the list contents. You can do this wherever the variable name appears in Omnis, including the Variable pane in the method editor, the Code editor or Catalog. In order to view the contents of a list instance variable the class containing the variable must be open or instantiated, e.g. a remote form must be open in the client browser to view its instance variables.

List Variable Values

The second option in the List context menu, shown by Right-clicking on a list variable, shows the Value for the variable, which for a list variable includes information about the number of lines, which lines are selected, as follows:

List and Row functions

Omnis provides functions for converting independent variables into a row, and for converting a series of row variables into a list.

The list() Function

The list() function accepts a set of row variables as parameters, and creates a list variable from them. The definition for the first row variable is used to define the list. If subsequent row variables have different definitions, Omnis will convert the data types to match the first row.

Calculate myList as  list(myRow1, myRow2, myRow3)

The row() Function

The row() function accepts a set of variables as parameters, and creates a row variable from them. The variable types are used to define the columns of the row.

Calculate myRow as  row(myVar1, myVar2, myVar3)

Accessing List Columns and Rows

You can access data in a list by loading an entire row of data using the $loadcols() method. The following example for the Sidebar window control uses the $loadcols() method to load values based on the chosen icon.

# $event method for sidebar component, incl pLinenum (Integer)
On evIconPicked
  Do iSidebarList.$line.$assign(pLinenum)
  # selects the list line according to item selected in sidebar
  Do iSidebarList.$loadcols()
  # loads the values in the selected list line, including iID
  
  Switch iID ## branches according to value of iID
    Case 1
      Do something..
    Case 2
      Do something..
    Case 3
      Do something..
  End Switch

You can use the lst() function as part of a calculation to extract a particular cell of information from a list.

Calculate MyVar as  lst(MyList, row, ColumnName)

You can address cells directly by referring to them as ListVarName.ColumnName for the current row or ListVarName.RowNumber.ColumnName for a specified row. Omnis also recognizes the syntax ListName(‘ColumnName’,RowNumber). The column name must be in quotes.

You can use RowVarName.ColumnName or RowVarName.ColumnNumber when you assign a row variable to a remote form or window edit field. Remember that your list and row variables should be defined in the $construct() of a form or window so they are available to edit fields and other data bound objects when the form or window opens.

Since ListName.ColumnName and ListName.RowNumber could be ambiguous, Omnis assumes character values are column names. In the case of the row number being contained by a character variable, this should be indicated by adding ‘+0’.

Calculate MyNum as  MyList.Amount       ## the current row
Calculate MyNum as MyList.5.Amount     ## row 5
Calculate MyNum as MyList('Amount',5)  ## this legacy code works, but the above is the preferred method

The two types of statement above are also used to assign a value to a list element.

Calculate MyList.5.Amount as  100     ## sets Amount column, row 5 to 100

As part of resolving list notation (e.g. List.C1), when Omnis accesses a list it will automatically convert a NULL list variable to empty.

List Column Calculations

To allow for expressions like myList.col or myList.10.col where the list line does not exist, perhaps because the list is empty, you can set the library preference $validcolumninbadrowisnull ($clib.$prefs) to true. If true, non-existent list columns in calculations evaluate to #NULL rather than an empty character string.

List line commands

The List Lines group of commands (that includes the Load from list command) are now obsolete, and they are no longer visible in the Code Assistant in the Code Editor (although they are still present in Studio 10 and will continue to function in legacy code). You can show these commands by disabling the appropriate Command Filter in the Modify menu in the Code Editor.

List Variable Notation

List variables have certain standard properties and methods that provide information about the list, such as how many rows or columns it has, or the number of the current line. List columns, rows, and cells have properties and methods of their own which are listed in the Omnis Help (press F1 to open the Omnis Help).

List Properties and Methods

All types of list have the following properties. A list created from a SQL class has the standard properties and methods of a table instance, together with these list properties.

For a row variable, $linecount, $linemax and $line are all set to 1 and cannot be changed.

Lists also have the following methods.

Do  MyList.$definefromsqlclass('MySchema') ## the schema has 2 numeric cols, col1 and col2
Do MyList.$add(1.1,2.1)
Do MyList.$add(3.1,4.1)
Do MyList.$add(2.2,1.1)
Do MyList.$totc(MyList.col1+MyList.col2Returns Total

# outputs Total = 13.7 i.e the total of both columns

Properties and Methods of a List Column

The columns of a list are contained in the List.$cols group. The $cols group has the following methods, that is, the standard group methods, including the $add… methods that allow you to add columns to the list (but not schema or table based lists):

A list column has the following properties:

List columns have the following methods:

Note: $count, $total, $average, $minimum and $maximum can be used in client executed methods in the JavaScript client.

Properties and Methods of a List Row

A list row has the following properties:

A list row has the following methods:

Properties of a List Cell

If a list cell is itself a list or row variable it has all properties of a list or row. List cells have the following properties.

Manipulating Lists

You can change both the structure and data of a list variable using both commands and notation.

Dynamic List Redefinition

You can add, insert, remove, or move columns in list or row variables without losing the contents of the list or row. This functionality applies to all types of list and row variables including smart lists.

When using List.$cols.$add(colname, type, subtype, length) to add a column, the type and subtype parameters need to be constants under Data Types and Data Subtypes in the Catalog (press F9). In addition, the subtype and length are not always required, depending on the type of the column. The following method defines a list and then adds a further two columns to the right of the existing columns.

Do  mylist.$define(col1,col2)
Do mylist.$cols.$add('MyCol',kCharacter,kSimplechar,35)
Do mylist.$cols.$add('MyPicture',kPicture)

Note you cannot add a column to a list using square bracket notation or using the fld() function. In addition, you cannot insert, remove, or move columns in a list defined from a SQL class, since you cannot redefine schema-, query-, or table-based lists. However you can use List.$cols.$add() to add extra columns to a SQL list.

Clearing List Data

You can use the command Clear list or ListName.$clear() to clear the data from a list. You can clear individual columns of a list with the ListName.ColumnName.$clear(), and individual rows with ListName.rowNumber.$clear().

Searching Lists

You can search a list using the $search() method, and a successful search sets the flag. You can use a search calculation to search a list as follows:

Do  MyList.$search(calculation [,bFromStart=kTrue, bOnlySelected=kFalse, bSelectMatches=kTrue, bDeselectNonMatches=kTrue]) 

For example, to search the Country column for “USA” you can use:

Do  MaiList.$search(Country = 'USA') Returns myFlag

The search calculation can use list_name.colname to refer to a list column. When searching a list column in a client method in the JavaScript Client you must prefix the column name with $ref. For example:

Do  iList.$search($ref.iCol="ABC")

With bSelectMatches or bDeselectNonMatches the first line number whose selection state is changed is returned (or 0 if no selection states are changed), otherwise the first line number which matches the selection is returned (or 0 if no line is found).

When bSelectMatches and bDeselectNonMatches are kFalse, the list's current line is set to the first matched row.

$search is optimized to operate on a single line at a time, so your calculation cannot contain multiple line conditions.

Selecting List Lines

When you display the data in a list variable in a list field on a window, by default you can select a single line only. However, you can allow multiple selected lines by setting the list or grid field’s $multipleselect property. When the user highlights list lines with the mouse, the $selected property for those lines is set. If the field does not have $multipleselect set, the current, selected line is the highlighted one; if the $multipleselect property is set, all highlighted lines are selected, and the current line is the one with the focus.

Some of the commands that operate on a list variable use $selected to indicate their result. For example, Search list (Select matches) will set $selected for each line that matches the search criteria.

Each list variable has two select states, the saved and current selections. The current selection is the set of lines currently selected, whereas the saved selection is the previous set of lines that was selected before the current selection changed.

There are a number of commands that you can use to manipulate selected lines, save the current selection, and swap between the selected and saved states. These commands are described in the Omnis Studio Help.

Merging Lists

You can copy lines from one list to another using the Merge list command or the $merge() method. Merging copies a specified set of lines from one list, and appends them to another . The following example copies the selected lines from LIST1 to LIST2 by checking each line’s $selected property.

Set current list LIST2
Set search as calculation {#LSEL}
Merge list LIST1 (Use search)

$merge() provides slightly different capabilities in that it can match the destination columns by column name as well as by column number. Merge list works by column number only. The syntax is

$merge(list, byColumnName, selectedOnly)

The above example could be written as:

Do  List2.$merge(List1, kFalsekTrue)

Note that $merge() does not have a search capability.

Sorting Lists

You can specify up to nine levels of sorting using the Sort list command or $sort() method. To use Sort list you need to set up the sort fields first, and clear any existing sort levels since these are cumulative. $sort() clears existing sort fields automatically. For example

Set current list {MyList}
Clear sort fields
Set sort field Country
Set sort field Town
Set sort field Name
Sort list
Redraw lists

The $sort() method takes the sort variables or column names in order, each followed by a boolean indicating the sort direction; the sort order flag bDescending defaults to kFalse (that is, the sort is normally ascending).. Using notation, the equivalent of the above example would be

# Country, Town, Name are columns in MyList
Do MyList.$sort($ref.Country,kFalse$ref.Town,kFalse$ref.Name,kFalse)
Redraw lists

Removing Duplicate Values

List columns have the $removeduplicates() method which removes lines with duplicate values in the column. You must sort the list on the column before using this method.

Do  MaiList.$sort($ref.CustNum,kFalse## sorts list on CustNum column
Do MaiList.$cols.CustNum.$removeduplicates() Returns NumRemoved

Smart Lists

You can track changes made to a list by enabling its $smartlist property. A smart list saves any changes, such as deleting or inserting rows, in a parallel list called the history list. Smart lists can be filtered, a process which allows data not meeting a particular criteria to be made invisible to the user while being maintained in the history list.

A smart list variable therefore contains two lists:

If you store a smart list as a binary object is a SQL database, all the smart list information is stored automatically.

Smart Lists and the JavaScript Client

The JavaScript Client does not support smart lists in client executed methods, insofar as if you change the list in some way on the client, it will no longer be a smart list when the updated data is sent from the client back to the server.

Enabling Smart List Behavior

To enable the smart list capability of any list variable you have to set its $smartlist property to kTrue.

Do  ListName.$smartlist.$assign(kTrue)      ## to enable it

Setting $smartlist to kTrue creates and initializes the history list. If it is already kTrue, then setting it again has no effect.

Setting $smartlist to kFalse discards the history list completely. The current normal list remains unchanged, so the current contents of the normal list are preserved, but all history and filtering information is lost.

If you define or redefine a list using any mechanism, or add columns to a list, its $smartlist property is set to kFalse automatically.

The History List

The history list has one row for each row in the normal list, together with a row for each row that has been deleted or filtered. The history list has the columns contained in the normal list as well as the following additional columns:

Properties of the History List

You can access the history list via the $history property, that is, LIST.$history where LIST is a smart list. $history has the properties:

$history also supports the standard group methods $first() and $next() as well as $makelist(), but you cannot change the history list.

Properties of Rows in the History List

LIST.$history.N refers to the Nth row in the history list. You can use this notation to access the columns using the following properties:

The above row properties are also properties of the list rows in the normal list, and provide a means of going directly to the history data for a line. In this case, $rowpresent is always kTrue, but can be set to kFalse.

Tracking the Changes

Change tracking occurs automatically as soon as you enable the $smartlist property for a list. From this time, Omnis automatically updates the status of each row in the history list whenever it inserts, deletes, or makes the first update to the row. Note that change tracking only remembers a single change since the history list was created. Hence:

Change Tracking Methods

The history list has several standard methods that let you undo or accept changes to the list data. After using any of these methods, the list is still a smart list.

You can use the following methods for accepting changes:

And these are for undoing changes made to the list data:

The history list also has a default method that lets you set the row present property based on the value of the status.

Filtering

Filtering works only for smart lists. You apply a filter by using the $filter() method, for example

Do  ListName.$filter(COL1 = '10') Returns Count

$filter() takes one argument, which is a search calculation similar to one used for $search(). It returns the number of rows rejected from the list by the filter.

Filtering uses the row present indicator of the history list to filter out rows. In other words, after applying a filter, Omnis has updated $rowpresent to kTrue for each row matching the search criterion and kFalse for the others. Filtering applies only to the rows in the normal list, that is, rows where $rowpresent is kTrue, with the result that repeated filtering can be used to further restrict the lines in the list.

Filter Level

Each history row contains a filter level, initially zero. When you apply the first filter, Omnis sets the filter level of all rows excluded by the filter to one; that is, for each row in the normal list, for which $rowpresent becomes kFalse, $filterlevel becomes one. Similarly for the nth filter applied, Omnis sets $filterlevel for the newly excluded rows to n. You can apply up to 15 filter levels.

Whenever a row is made present, for whatever reason, the filter level is set back to zero, and whenever the row is made not present, for any reason other than applying a filter, the filter level is also set back to zero.

Undoing a Filter

You can restore filtered rows to the normal list using the $unfilter() method, for example:

Do  ListName.$unfilter() Returns Count

When called with no parameters, $unfilter() removes the latest filter applied. Otherwise, $unfilter removes filters back to the level indicated by the parameter. Thus $unfilter(0) removes all filters, $unfilter(1) removes all but the first, and so on.

Reapplying a Filter

You can reapply all the filters which have already been applied, in the same order, to all lines present in the normal list using the $refilter() method. For example

Do  ListName.$refilter() Returns Count

The Filters Group

A list has a read-only group called $filters which lets you navigate through a list of the filters that have been applied. For example

ListName.$filters.N

identifies the Nth filter currently applied to the list, that is, the filter which filtered out rows at filter level N. Each member of the $filters group has a single property, $searchcalculation, which is the text for the search calculation passed to $filter() when applying the filter.

Sorting smart lists

When a smart list is sorted, Omnis sorts the second list. This list does not contain selection states, these are only stored in the first list, therefore using $selected when sorting a smart list is not supported.

Committing Changes to the Server

The current state of the normal list can be committed to the corresponding server table, assuming the list was defined from a SQL class, using the following smart list methods

List Commands and Smart Lists

Any command or notation which defines a list sets $smartlist to false, so that any history information is lost. You can use the following list commands and notation with smart lists but with particular effects.