Lists

When you code in Omnis Studio, you should not use the Set current list, #L, #LN, and the other commands that require a current list. Global variables and global methods are not good for object-oriented programming. Code and values which depends on the current list will get you into trouble. This is espcially true with remote forms and multi-tasking.

With object-oriented programming what goes on inside your objects should not be affected by things happening outside of your objects. Set current list, #L, and #LN are global and therefore not a good thing for object-oriented programming.

In the List section I first give you the most common list operations grouped by the types of operations.

The List Notation section lists all the notational list commands in alphabetic order.

List Sendall and Row Sendall are special sections devoted to explaining and demonstration the use of $sendall for lists and rows.

Hope you find this Lists section helpful!

Not all Omnis Studio developers are in agreement with the statements I make in the preceeding section.

There are developers far more experienced than me who state that the Omnis commands are easier to read in the code, faster to type, and execute quicker. (all true)

I admit that remembering and reading the parameters for $search is harder than clicking/reading the checkboxes in the Omnis command Search list. The downside with Search list is you have to first Set current list and doing so you have to be careful that you aren't affecting another method which is depending on the current list. This can be overcome with Begin reversible block and End reversible block ... but then that's 3 more lines of code to allow use of Search list.

The choice is yours. If you like using the Omnis commands, Omnis Studio graciously allows you to do so... but be warned, you are going against object-oriented programming principles if you do.

Define List

There are many ways to define a list in Studio.

The most common methods which I use are:

• $definefromsqlclass
• $cols.$add()
• Calculate List2 as List1

$definefromsqlclass

Do ListOrRow.$definefromsqlclass([LibName.] TableSchemaOrQueryClassName)
Do ListOrRow.$definefromsqlclass('t_author')
Do ListOrRow.$definefromsqlclass(SchemaOrTableRef)

The list is defined to match all the columns listed in the schema or query class.

Once I got into SQL, $definefromsqlclass became a favourite for me. Fast and easy, a very handy command. If you use table classes (and you should!), $definefromsqlclass binds an instance of the table class to the list/row variable, giving you direct access to all your table class methods from the variable. This is similar to object type variables where the object class is bound to the object type variable.

$cols.$add

Do ListRow.$cols.$add(variable|'ColName' [,kDataType,kDataSubtype,iMaxLen]) Returns ColRef

It took me a while to catch on to this one, but now I use it all the time. You can start with an undefined list or row variable and use $cols.$add to completely define the list.

I used to think figuring out the extra kConstants was too much of a hassle, but now that I found the F9 Catalog > Constants tab > Data type and Data subtypes, it's much quicker and easier to use.

Example: Define a 3 column list.

Do List.$cols.$add('name',kCharacter,kSimplechar,200)
Do List.$cols.$add('total',kInteger,kLongint)
Do List.$cols.$add('Active',kBoolean)

You can also use a variable which you've already defined. The column name becomes the variable name.

Do List.$cols.$add(VarName)

If you use a local variable, the column name is lost when you leave the scope of the class instance. Because of this, and the fact that I first have to declare the variable, I rarely use a variable with $cols.$add()

You can also use $addbefore, $addafter on $cols.

Do List.$cols.$addbefore(1,'Test',kBoolean)
Do List.$cols.$addafter(2,'Test2',kBoolean)

You can move a column to a new position using the $ident
Do List.$cols.1.$ident.$assign(3)

You can also use $cols.$remove(rCol).
Do List.$cols.$remove(rCol)

Omnis Studio will not let you affect the original coulmns of a list or row defined using $definefromsqlclass. Omnis Studio will ignore $addbefore, $addafter, $ident.$assign, $name.$assign, or $remove if it affects the original columns of a list or row that has been defined using $definefromsqlclass. The reason for this is that SQL statements generated for you by Omnis Studio depend on the original set of columns that are mapped to the schema or query class.

Calculate List1 as List2

This copies the list definition and data from one list to another

You can copy the current row in a list to a row variable.
Calculate Row as List

If the list is large, Calculate Row as List, is very slow. You are better to Calculate Row as List when the list is empty, and then use Row.$assignrow(List) in your loop.

Looping through a list

There are several methods for looping through a list of records using notation. The best ones I've found are listed below.

For looping through all of the lines in a list:

For List.$line from 1 to List.$linecount step 1
  ; The current line is automatically set by the For loop
End For

For looping through the selected lines in a list use $first and $next with the appropriate parameters:

Do List.$first([bOnlySelectedNY,bBackwardsNY])
Do List.$next(LineN,[bOnlySelectedNY,bBackwardsNY])

Do List.$first(kTrue) ;; Only selected lines
While List.$line
  ; The current line is automatically set by $first, then $next
  Do List.$next(0,kTrue) ;; 0=starting with the current line, kTrue=next selected line
End While

You could use $first and $next to loop through all the lines in a list but it is not as fast or as simple as For List.$line from 1 to List.$linecount step 1.

The Omnis command For each line in list... is still the fastest. If you have a large list (100,000+ lines) and speed is critical you may want to use the Omnis command Set current list in a reversible block and the Omnis command For each line in list to loop through the list.

If you click the Run Demo button in the StudioTips Browser you can compare the performance of the above loops. On my computer the results for looping through the 100,000 line list are as follows:

• 0.30 seconds - For List.$line
  
• 0.45 seconds - $first and $next
  
• 0.08 seconds - For each line in list
  

All of the 100,000 line loops come in under 1 second, so I'm not sure that the end user will notice if you use For List.$line vs. For each line in list.

Moving Lines

Moving a line in the same list can be tricky because the line number of the line your are moving will change if you insert the line higher up the list. Or if you insert the line lower in the list, the position you are moving the line to will be reduce by one after you remove the old line.

The following code could be put into a $moveLineInList method with the appropriate parameters.

; If the "ToLineNum" is less than or equal to the "FromLineNum", then "ToLineNum" must be incremented.
If ToLineNum>=FromLineNum
   Calculate ToLineNum as ToLineNum+1
End If

; If the "FromLineNum" is greater than or equal to the "ToLineNum", then "FromLineNum" must be incremented.
If FromLineNum>=ToLineNum
   Calculate FromLineNum as FromLineNum+1
End If

; Add a line before the "ToLineNum" and immediately assign the "LineFrom" values to the new line.
Do List.$addbefore(ToLineNum).$assignrow(List.[FromLineNum])

; Remove the "FromLineNum" line.
Do List.$remove(FromLineNum)

Set current line

You can set the current line using $line, $search, $first, or $next

Remember that the selected line and the current line are not the same thing, they can be different. You can have 50 lines selected in a list and no current line. You can have a current line and no lines selected.

You can set and get a value from a column in a list without setting the current line, but then you must specify the line number in your calculation.

Calculate Value as List.[LineNum].ColName

If the current line is set, you do not need to specify the line number.

Calculate Value as List.ColName

Be sure to always set the current line before you try to set or get column values. This is an easy mistake to make when you are first learning to use Omnis Studio.

For row variable the one and only row is always the current row, you never need to set the current line for a row variable.

Calculate Value as Row.ColName

$line tells you the current line number in the list. Using $assign you can set the current line.

; Set the current line to the value of LineNum, provided LineNum is between 1 and List.$linecount.
Do List.$line.$assign(LineNum)

; Set the current line to zero. No line selected.
Do List.$line.$assign(0)

; Set the current line to the last line in the list.
Do List.$line.$assign($ref.$linecount)

; Either of the following will tell you the current line.
Calculate %L as List.$line
Do List.$line Returns %L

The $search method with the parameters 1,0,0,0 will set the current line.

; Set the current line to the first line which matches the search criteria.
Do List.$search($ref.ColName=Value,1,0,0,0)

See the topic for $search or Search to Select/Deselect for more information.

The $first method can be used to set the current line in a list.

Do List.$first([kOnlySelectedNY,kBackwardsNY])

; Set current line to the first line in the list.
Do List.$first()

; Set current line to the first selected line in the list.
Do List.$first(kTrue)

; Set current line to the last line in the list.
Do List.$first(kFalse,kTrue)

; Set current line to the last selected line in the list.
Do List.$first(kTrue,kTrue)

The $next method can be used to set the current line in a list.

Do List.$next(LineN,[kOnlySelectedNY,kBackwardsNY])

; Set current line to the next line in the list, starting from the current line
Do List.$next(0)

; Set current line to the next selected line in the list.
Do List.$next(0,kTrue) ;; (From current line, Selected lines)

; Set current line to the next lines in the list, going backwards.
Do List.$next(0,kFalse,kTrue) ;; (From current line, Selected lines, Backwards)

; Set current line to the next selected line in the list, going backwards.
Do List.$next(0,kTrue,kTrue) ;; (From current line, Selected lines, Backwards)

Totals

There are a number of notational commands for totaling columns in a list: $total, $average, $maximum, $minimum

; Total of the values in a column of the list.
Calculate Total as List.$cols.ColName.$total()

; Average of the values in a column of the list.
Calculate Average as List.$cols.ColName.$average()

; Maximum of the values in a column of the list.
Calculate Maximum as List.$cols.ColName.$maximum()

; Minimum of the values in a column of the list.
Calculate Minimum as List.$cols.ColName.$minimum()

; Count the number of non-null values in a column of the list.
Calculate Count as List.$cols.ColName.$count()

; You can add the parameter kTrue to any of the above to include selected lines only.

; Total value of the values in a column of the list, selected lines only.
Calculate Total as List.$cols.ColName.$total(kTrue) ;; (Selected lines)

; Count the number of non-null values in a column of the list, selected lines only.
Calculate Count as List.$cols.ColName.$count(kTrue) ;; (Selected lines)

Use $totc to find the total number of selected lines in list.

; Number of selected lines in a list.
Calculate %N as List.$totc(#LSEL)

Working Message

The Omnis Studio built in Working Message has a number of parameters which allow you to show a progress bar when looping through a list. To show a progress bar in a loop do the following:

1. On a new line in the method editor, type: Wor, Omnis completes the line for you selecting Working message in the Omnis commands list.
2. Click the Configure button, this opens a dialog window were you can set various Title properties for your working message.
3. Click the OK button to accept the default settings. Omnis will enter the default settings in the working message's Title field.
   
   Working/-1073735824,-1073735820;50;0;60
4. The syntax for the Title field is as follows:
   
   Title text/start id,end id;speed;progress bar range;display delay
5. The F1 Help defines each Title field parameters as follows:
• start id and end id specify a range of icon ids, which identify the icons drawn in the alternating sequence on the working message window. These are usually negative numbers, as each id is the sum of the icon id and the value of the constant k16x16, k32x32 or k48x48, to indicate the size of the icon. These icons can come from #ICONS for the library, or OmnisPic or UserPic.
• speed indicates the time, in 1/60th second units, that an icon in the alternating sequence is displayed.
• progress bar range specifies the range of a progress bar. If you specify a non-zero value, then the working message window displays a progress bar, and each call to the Working message command increases the length of the bar until it reaches the range.
• display delay specifies the time in 1/60th second units, that must elapse before the working message window becomes visible. This allows you to use the Working message command in situations where the processing is sometimes very rapid, and in that case avoid the message displaying and disappearing almost immediately.
6. When putting the Working message in a loop, I normally calculate the List.$linecount to a local variable %LN, and the use [%LN] as the progress bar range parameter. (See the sample code in the demo below)

Note

: You need to include Redraw working message in the loop if you have a variable in the Message field that changes for each cycle through the loop.

Redraw working message slightly affects performance in a large loop (100,000+ lines). If you evaluated a variable in the working message text it severely affects performance. (as of Omnis Studio 4.2) Click the Run Demo button in the StudioTips Browser to see the speed differences.

Lists Miscellaneous

Column Alias

You can refer to any column in a list using the alias C# where # is the column number. If the 3rd column in the list is LastName you could refer to it as List.LastName or List.C3. This is handy when you use $makelist, which doesn't define column names.

Column aliases make your code harder to read. Only use column aliases on rare occassions, or not at all.

Column Names Case Sensitivity

Are the column names in rows and list variables case sensitive in your code. Will List.ColName, List.colname, and List.COLNAME all evaluate to the same result?

For a long time I assumed that the column names in rows and lists were case sensitive. When I tested the theory is was surprised to find out that Omnis Studio is case insensitive for lists which are defined using $cols.$add. However, lists defined using $definefromsqlclass are case sensitive.

Click the Run Demo button in the StudioTips Browser to test the theory.

; Define a single column list using $cols.$add
Do List.$cols.$add('ColName',kCharacter,kSimplechar,100)
Do List.$add('George')
Do List.$line.$assign(1)
OK message Test Column Name Case Sensitivity (Icon) {List.ColName = [List.ColName]////List.colname = [List.colname]////List.COLNAME = [List.COLNAME]////Column names for lists defined using $cols.$add are NOT case insensitive.}

; Define a single column list using $definefromsqlclass
Do List.$definefromsqlclass('sCaseSensitiveColNames')
Do List.$add('George')
Do List.$line.$assign(1)
OK message Test Column Name Case Sensitivity (Icon) {List.ColName = [List.ColName]////List.colname = [List.colname]////List.COLNAME = [List.COLNAME]////Column names for List defined using $definefromsqlclass ARE case insensitive.}

Column Properties

Each column has the following properites:

• $name - the column name
• $coltype or $objtype - the data type
• $colsubtype or $objsubtype- the data subtype
• $colsublen or $objsublen- the data sub-length

You can use $makelist to build a list of the columns in a row or list.

; Make a list of the columns using $obj...
Do Row.$cols.$makelist($ref().$name,$ref.$objtype,$ref.$objsubtype,$ref.$objsublen) Returns ColsList

; Make a list of the columns using $col..
Do Row.$cols.$makelist($ref().$name,$ref.$coltype,$ref.$colsubtype,$ref.$colsublen) Returns ColsList

You can get the property of a column by specifying the actual column number and property.

Calculate ColName as Row.$cols.1.$name

Comparing Lists

There are situations where you want to compare two lists and select all the records in the one list which match the records in the other list.

There are many ways to accomplish this. One way is to loop through List1 and then inside the loop search for the matching values(s) in List2.

; Define a list variable using $cols.$add
Do List1.$cols.$add('FirstName',kCharacter,kSimplechar,100)

; Add some names to List1
Do List1.$add('Doug')
Do List1.$add('Andy')
Do List1.$add('Chuck')

; Copy List1 to List2.
Calculate List2 as List1

; Add few more lines to List2.
Do List2.$add('Robert')
Do List2.$add('John')
Do List2.$add('Craig')

; Deselect all lines in List2.
Do List2.$search(kFalse)

; Loop through List1, searching and selecting matching names in List2
For List1.$line from 1 to List1.$line step 1
   
   ; $search(Criteria, From start, Selected lines, Select matches, Deselect non-matches)
   Do List2.$search($ref.FirstName=List1.FirstName,1,0,1,0)
   
End For

OK message Search Using Loop (Icon) {There are [List2.$totc(#LSEL)] out of [List2.$linecount] records in List2 which match the [List1.$linecount] records in List1.}

The above code can be compressed into a single line by using a $search within a $search.

; Deselect all lines in List2.
Do List2.$search(kFalse)

; Select all the lines in List2 which match List1 using a $search withing a $search.
Do List2.$search(List1.$search($ref.FirstName=List2.FirstName,1,0,0,0))

OK message Search Using a $search within a $search (Icon) {There are [List2.$totc(#LSEL)] out of [List2.$linecount] records in List2 which match the [List1.$linecount] records in List1.}

The $search notation executes a loop through the specified list. The outer loop, List2.$search, loops through every line in List2. The inner loop, List1.$search, gets executed for each line of List2. The inner loop searches List1 for a FirstName column value which matches the current List2.FirstName column value. With the inner $search parameters set to 1,0,0,0 the inner search returns zero if no match is found and a value greater than zeror if a match is found. The current line in List2 of the outer loop is then deselected or selected based on the zero or non-zero value returned by the inner loop $search.

Don't try to use $search within $search on large lists (10,000+ lines).

Comparing Rows

Having a generic method which you can call to compare the values in 2 rows for any changes can be handy to call to decide whether or not to update the database.

There are many different ways to accomplish this. A one liner is listed in List Notation $sendall

The following method loops through the columns of the row variable testing for any differences. If there is a difference the local variable bRowsMatch is set to kFalse.

; Define a row variable using $cols.$add
Do Row1.$cols.$add('FirstName',kCharacter,kSimplechar,100)
Do Row1.$cols.$add('LastName',kCharacter,kSimplechar,100)

Calculate Row1.FirstName as 'John'
Calculate Row1.LastName as 'Smith'

Calculate Row2 as Row1

; This loop will set bRowsMatch to false if any values are different.
Calculate bRowsMatch as kTrue
For %N from 1 to Row1.$colcount step 1
   If Row1.[%N]<>Row2.[%N]
      Calculate bRowsMatch as kFalse
   End If
End For

If bRowsMatch
   OK message (Icon) {Row1 and Row2 are exactly the same.}
Else
   OK message (Icon) {Row1 and Row2 are different.}
End If

; Change a value in Row2
Calculate Row2.LastName as 'Smithers'
OK message (Icon) {The LastName in Row2 has been changed to 'Smithers'.////About to test for matching values again.}

; This loop will set bRowsMatch to false if any values are different.
Calculate bRowsMatch as kTrue
For %N from 1 to Row1.$colcount step 1
   If Row1.[%N]<>Row2.[%N]
      Calculate bRowsMatch as kFalse
   End If
End For

If bRowsMatch
   OK message (Icon) {Row1 and Row2 are exactly the same.}
Else
   OK message (Icon) {Row1 and Row2 are different.}
End If

Lists Notation

This section covers, in alphabetic order, notational commands which are related to lists.

$add,$addbefore,$addafter

The $add function is used all over the place in Studio. With respect to lists it is used to add rows or add columns. Also see $remove for various ways to delete line(s) or columns from a list.

This section covers adding lines to a list. See $cols.$add for adding columns to lists or rows.

Do List.$add([Col1Value,Col2Value,...]) Returns rLine

Adds an empty row to the end of the list. If you include values, they will be added by column number.
$add does not change the current line in the list!

You can use rLine in your calculations immediately following $add

; You can use rLine in a calculation after after a line.
Do List.$add() Returns rLine
Calculate rLine.ColName as 'ABC'

; You can use LineRef.$line to assign the current line immediately following $add
Do List.$add() Returns rLine
Do List.$line.$assign(LineRef.$line)
Calculate List.ColName as 'ABC'

; You can use $ref.$linecount to assign the current line immediately after $add.
Do List.$add()
Do List.$line.$assign($ref.$linecount)
Calculate List.ColName as 'ABC'

Do List.$addbefore(LineNum [,Col1Value,Col2Value,...]) Returns rLine

Adds an empty row before LineNum in the list. If you include values, they will be added by column number. If LineNum=0, the current line will be used.

$addbefore does not change the current line in the list!

Do List.$addafter(LineNum [,Col1Value,Col2Value,...]) Returns rLine

Adds an empty row after LineNum in the list. If you include values, they will be added by column number. If LineNum=0, the current line will be used.

$assigncols

Do List.$assigncols(VALUE1,VALUE2,...)

$assignrow

Do List1.$assignrow(List2 [,bMatchColNames])

Assigns the values in the current line of List2 to the current line of List1.

If you include the optional parameter bMatchColNames as kTrue, the column names will be matched, otherwise column number is used.

; Assign row values to current line in the list.
Do List.$assignrow(Row,kTrue) ;; (Match column names)

; Assign values from the current line in List2 to the current line in List1.
Do List1.$assignrow(List2,kTrue) ;; (Match column names)

; Adds a line to the list and immediately assign the values from the row to the added line.
Do List.$add().$assignrow(Row)

$average

Average values in the list for the specified column. Boolean parameter allows you to specified selected lines only. Default is kFalse.

; Average of the values in a column of the list.
Calculate Average as List.$cols.ColName.$average()

; You can add the parameter kTrue to include selected lines only.
Calculate Average as List.$cols.ColName.$average(kTrue) ;; (Selected lines)

$clear

Do List.$clear()

Clears the list leaving the list definition in tact.

Do List.[LineNum].$clear()

Clears the column values in the LineNum. If LineNum=0 the current line column values are cleared.
Do Row.$clear()

Clears the column values in the row.

Do List.$cols.ColName.$clear()

$colcount

The number of columns in a list or row

$cols.$add,$addbefore,$addafter

Do ListRow.$cols.$add(variable|'ColName' [,kDataType,kDataSubtype,iMaxLen]) Returns ColRef

It took me a while to catch on to this one, but now I use it all the time. You can start with an undefined list or row variable and use $cols.$add to completely define the list.

I used to think figuring out the extra kConstants was too much of a hassle, but now that I found the F9 Catalog > Constants tab > Data type and Data subtypes, it's much quicker and easier to use.

Example: Define a 3 column list.

Do List.$cols.$add('name',kCharacter,kSimplechar,200)
Do List.$cols.$add('total',kInteger,kLongint)
Do List.$cols.$add('Active',kBoolean)

You can also use a variable which you've already defined. The column name becomes the variable name.

Do List.$cols.$add(VarName)

If you use a local variable, the column name is lost when you leave the scope of the class instance. Because of this, and the fact that I first have to declare the variable, I rarely use a variable with $cols.$add()

You can also use $addbefore, $addafter on $cols.

Do List.$cols.$addbefore(1,'Test',kBoolean)
Do List.$cols.$addafter(2,'Test2',kBoolean)

You can move a column to a new position using the $ident
Do List.$cols.1.$ident.$assign(3)

You can also use $cols.$remove(rCol).
Do List.$cols.$remove(rCol)

Omnis Studio will not let you affect the original columns of a list or row defined using $definefromsqlclass. Omnis Studio will ignore $addbefore, $addafter, $ident.$assign, $name.$assign, or $remove if it affects the original columns of a list or row that has been defined using $definefromsqlclass. The reason for this is that SQL statements generated for you by Omnis Studio depend on the original set of columns that are mapped to the schema or query class.

$cols.$remove

You can use $cols.$remove(rCol), except on the original columns of lists or rows defined using $definefromsqlclass.

; Remove column 2.
Do List.$cols.$remove($ref.$cols.2)

$count

Count the number of non-null values in the list for the specified column. Boolean parameter allows you to specified selected lines only. Default is kFalse.

; Count the number of non-null values in a column of the list.
Calculate Count as List.$cols.ColName.$count()

; You can add the parameter kTrue to include selected lines only.

; Count the number of non-null values in a column of the list, selected lines only.
Calculate Count as List.$cols.ColName.$count(kTrue) ;; (Selected lines)

$define

Do List.$define()

If a list is already defined, this removes all the columns. Handy to use if you want to make sure a list has no columns before you issue the first Do List.$cols.$add(...

You can define a list using actual varaiables.

Do List.$define(Variable1,Variable2,Variable3)

One problem I've had with this format of the List.$define command, is if you use local variables for defining the list, the column names are lost when you leave the scope of the class instance. The data remains in tact, but the column names are gone.

I rarely use this format because I don't like having to first define a bunch of variables and then define the list. I like being able to define a list and store data in it without having the column names exist as variables anywhere.

$definefromsqlclass

• Do List.$definefromsqlclass("[LibName.] SchemaTableOrQueryClassName")
• Do List.$definefromsqlclass(SchemaTableOrQueryClassRef)

$first & $next

$first

and $next are useful for looping through selected lines in a list.

Do List.$first([bOnlySelectedNY,bBackwardsNY])

Do List.$next(LineNum,[bOnlySelectedNY,bBackwardsNY])

; Selected lines loop.
Do List.$first(kTrue) ;; Selected Lines
While List.$line
   ; The current line is automatically set by $first, then $next
   Do List.$next(0,kTrue) ;; 0=Starting with the current line, kTrue=Next selected line
End While

$line

$line

tells you the current line number in the list. Using $assign you can set the current line.

; Set %L to equal the current line in the list.
Calculate %L as List.$line
; or
Do List.$line Returns %L

; Set the current line to line 3.
Calculate LineNum as 3
Do List.$line.$assign(LineNum)

; Set the current line to zero. No current line.
Do List.$line.$assign(0)

; Set the current line to the last line in the list.
Do List.$line.$assign($ref.$linecount)

$linecount

Either of the following will tell you the total number of lines in a list.

Calculate %LN as List.$linecount
Do List.$linecount Returns %LN

$linemax

Do List.$linemax.$assign(100)

$loadcols

Do List.$loadcols(VARIABLE1,VARIABLE2,...)

$maximum

Maximum value in the list for the specified column. Boolean parameter allows you to specified selected lines only. Default is kFalse.

; The maximum value in the specified column in the list.
Calculate Maximum as List.$cols.ColName.$maximum()

; You can add the parameter kTrue to include selected lines only.
Calculate Maximum as List.$cols.ColName.$maximum(kTrue) ;; (Selected lines)

$merge

Do List1.$merge(List2 [,bMatchColNamesNY,bOnlySelectedNY][,bClearDestList=kFalse])

Merges the data from List2 into List1. List2 must be defined before the $merge is executed. Omnis Studio won't define the list for you.

Match by column number. Merge all lines.
Do List1.$merge(List2)

Match by column name. Merge all lines.
Do List1.$merge(List2,kTrue)

Match by column name. Merge selected lines only.
Do List1.$merge(List2,kTrue,kTrue)

Match by column name. Merge all lines only. First clear the list
Do List1.$merge(List2,kTrue,kFalse,kTrue)

You can merge a Row into a List.
Do List1.$merge(Row)

To make your code more readable for yourself and other developers, it is a good habit to comment the true/false parameters, or declare local variable with their initial value value set to the boolean value you want to use.


; Merge list. Match by column name. Merge selected lines. First Clear the list.

; Code without parameter comments.
Do List1.$merge(List2,kFalse,kTrue,kTrue)

; Code with parameter comments.
Do List1.$merge(List2,kTrue,kTrue,kTrue) ;; (Match column names, Selected lines, Clear list)

; Code with local variable parameters preset using Init. Value/Calc.
Do List1.$merge(List2,bMatchColumnNames,bSelectedLines,bClearList)

$minimum

Minimum value in the list for the specified column. Boolean parameter allows you to specified selected lines only. Default is kFalse.

; The minimum value in the specified column in the list.
Calculate Minimum as List.$cols.ColName.$minimum()

; You can add the parameter kTrue to include selected lines only.
Calculate Minimum as List.$cols.ColName.$minimum(kTrue) ;; (Selected lines)

$next & $first

$remove

Once you search and select lines in a list you often will either want to remove the lines or merge them. If you want to remove lines, then $remove is the ticket.

; Remove the current line.
Do List.$remove(0)

; Remove a specific line.
Calculate LineNum as 5
Do List.$remove(LineNum)

; Keep the selected lines, remove the rest.
Do List.$remove(kListKeepSelected)

; Delete the selected lines, leave the rest.
Do List.$remove(kListDeleteSelected)

$removeduplicates

Do List.$cols.ColName.$removeduplicates(bSortNY,bIgnoreCaseNY)

$search

Searching, selecting lines and deselecting lines in list is something I do a lot. When I started using Omnis Studio, I worked with smartlist filters, but later found $search with $remove to be much much faster.

Do List.$search(SearchCriteria [,kFromStartYN,kOnlySelectedNY,kSelectMatchesYN,kDeselectNonMatchesYN])
Do List.$search($ref.ColumnName=Value)

Do not use $ref in the Value, the search will fail if you try. If you want Value to be another column in the list on the same line use List.OtherColumnName in place of Value. The Value can be a calculation such as pos('doug',low(List.FirstName))>0.

If parameters 3 & 4 are set to kFalse, $search will set current line in the list to the first matched line, otherwise $search does not change the current line.

$search returns the line number of the first match found, or zero if no matches found

To select or deselect all lines in a list:

Do List.$search(kTrue) ;; Select all lines
Do List.$search(kFalse) ;; Deselect all lines

To search all the lines in the list for the first matching value and set that line to the current line:

Do List.$search($ref.ColName=Value,1,0,0,0) Returns %L

By setting parameters 3 & 4 to kFalse (meaning don't select matches and don't deselect non-matches) the $search will change the current line to the first line which is found. If no matching line is found, %L will be zero.

You can use $search in an If statement to test whether or not a matching record was found in a list.


; Search syntax.
; Do List.$search(SearchCriteria [,kFromStartYN,kOnlySelectedNY,kSelectMatchesYN,kDeselectNonMatchesYN])

If List.$search(pos('doug',low($ref.FirstName))>0)
OK message (Icon) {'Doug' was found in the list.}
Else
OK message (Icon) {'Doug' was NOT found in the list.}
End If

; Search with special arguments.
If List.$search(low($ref.FirstName)='john'&low($ref.LastName)='smith')
OK message (Icon) {'John Smith' was found in the list.}
Else
OK message (Icon) {'John Smith' was NOT found in the list.}
End If

You can get quite creative with the search criteria!

You can use arguments like:

• pos('abc',low($ref.ColName))>0
• len($ref.ColName)>5

Do not use $ref in the Value. The search will fail if you try.

; This does NOT work.
Do List.$search($ref.ColumnName=$ref.OtherColumnName)

; This does work.
Do List.$search($ref.ColumnName=List.OtherColumnName)

You can invert the selection for all lines in a list using $sendall.

$selected

$selected

tells you whether or not a line is currently selected. Using $assign you can select or deselect the line.

; Is the current line selected?
Calculate bSelected as List.0.$selected

; Replace zero with a specific line number to find the selection state any line in the list.

; Set the current line to selected.
Do List.0.$selected.$assign(kTrue)
; Replace zero with a specific line number to set any line in the list.

; Select all the lines in the list.
Do List.$search(kTrue)

; Deselect all the lines in the list.
Do List.$search(kFalse)

; You can invert selection for all lines in a list using $sendall.
Do List.$sendall($ref.$selected.$assign(not($ref.$selected())))

; Total selected lines in the list.
Calculate %N as List.$totc(#LSEL)

$sendall

$sendall

is a powerful function with many different uses in Omnis Studio.

$sort

Do List.$sort($ref.ColName|$ref.C# [,bDescendingNY,$ref.ColName,bDescendingNY,...])

You can have up to 9 columns in the $sort.

; Sort the list by the "LastName" column, ascending.
Do List.$sort($ref.LastName)

; Sort the list by the "DateOrdered" column, descending.
Do List.$sort($ref.DateOrdered,kTrue)

; Sort the list by the "LastName/FirstName" ascending.
Do List.$sort($ref.FirstName,kFalse,$ref.LastName,kFalse)

; Sort the list on column number 3 without having to specify the column name.
Do List.$sort($ref.C3)

; Sort the list on the uppercase values of the "LastName" column, ascending.
Do List.$sort(upp($ref.LastName),kFalse)

$total

Total sum value of the specified column in the list. Boolean parameter allows you to specified selected lines only. Default is kFalse.

; Total of the values in a column of the list.
Calculate Total as List.$cols.ColName.$total()

; You can add the parameter kTrue to include selected lines only.

; Total value of the values in a column of the list, selected lines only.
Calculate Total as List.$cols.ColName.$total(kTrue) ;; (Selected lines)

Use $totc to find the total number of selected lines in list.

; Number of selected lines in a list.
Calculate %N as List.$totc(#LSEL)

$totc

$totc(expression)

Lists Sendall

$sendall

is a powerful Omnis Studio method which can pack a lot of code into a single line.

For the first few years that I worked with Omnis Studio $sendall was intimidating to me. I had trouble following the syntax and didn't see many (any?) examples in the documentation on how to use it. $sendall experts on the Omnis Developers List like Tim Stewart, Weitse Jacobs, and Reg Paling would amaze me with some of the complex sendalls they would post on the Omnis underground list server. Since I didn't clearly understand $sendalI I generally avoided using it.

There is a concern among developers that if you go overboard with the use of $sendall in your code others will have a hard time understanding and maintaining your code. You can't step through a $sendall the way you can step through a loop. It either works, or it doesn't.

If your $sendall is getting wider than the screen it is probably going to be difficult for others to debug and understand. You should not assume that others will understand your $sendall statement, so be sure to clearly comment it.

$sendall is a group method which sends a message to all the items in a group. When I first read about $sendall in the Omnis Studio documentation I only considered it for window objects and report objects. It never occured to me that $sendall could be used for lists. A list is structured as a group of rows, so using $sendall on a list will send a message to each row of the list.

So the first thing you need to visualize is that a list variable has a group of rows, in the same way that a window has a group of field objects.

$sendall is not faster than a For loop. The List.$sendall processes each line of the list, just like a For loop. If you are only processing the selected lines of a large list, using $first & $next with the selected line arguments will be slightly faster than using $sendall. If you are issuing $sendall more than once to the same list you should consider using a single For loop.

List.$sendall Syntax

Do List.$sendall($ref.ColName.$assign(Value),[Criteria]) Returns Count

Splitting the $sendall notation:

• List.$sendall - This loops through the rows in the specified list variable.
• $ref.ColName.$assign(Value) - This is the calculation that is applied to the specified column in each row.
• [Criterial] - This is optional criteria which can limit the rows to apply the calcuation to.
• Count - The number of rows the sendall was applied to.

Think of List.$sendall as a single line loop.

List.$sendall is the For & End For in a single line.

For List.$line from 1 to List.$linecount step 1

End For

What you put between the parenthesis before the comma is the middle part inside the loop. ($ref.C1.$assign(List.$line))

For List.$line from 1 to List.$linecount step 1
  Calculate List.C1 as List.$line
End For

What you put in the brackets after the comma is the If & End if part inside the loop. ( ,$ref.0.$selected)

For List.$line from 1 to List.$linecount step 1
  If List.0.$selected
    Calculate List.C1 as List.$line
  End If
End For

You can get as complex as you like with the optional [Criteria]. Criteria accepts | (or) and &

List.$sendall changes the current line in the list. It operates like a loop, changing the current line to the line being processed as it steps through the list. The last line in a list will always be the current line after you issue a List.$sendall.

List.$sendall Examples

The following examples give you some ideas of things you can do with List.$sendall.

; Increment each value in a column by 1.
Do List.$sendall($ref.ColName.$assign(List.ColName+1))
; or
Do List.$sendall($ref.ColName.$assign($ref+1))

Why were we able to replace List.ColName with $ref in the second part of the above example? With Omnis notation, $ref is an argument which points to the notation which preceeds $ref in the string. In the above example the first $ref points to List which preceeds it. The second $ref point to $ref.Value which preceeds it.

; Add the value of each "List.ColName" to "Total" for the selected lines.
Do List.$sendall(Total.$assign(Total+List.ColName),List.0.$selected)
; or
Do List.$sendall(Total.$assign($ref+List.ColName),List.0.$selected)

; The $ref in the second example points to the variable "Total" which preceeds it.
; Therefore we can use $ref in place of the second "Total" in the sendall.

For list totals use the $total or $totc methods. The above is just a demonstration of List.$sendall.

; Pad each "List.ColName" with preceeding zeros using the jst() function.
Do List.$sendall($ref.ColName.$assign(jst($ref,'-3P0')))

; Calculate the subtotal for every line in the list.
Do List.$sendall($ref.Subtotal.$assign(List.Qty*List.CostPer))

; Set a column value to the line number.
Do List.$sendall($ref.ColName.$assign(List.$line))

Row Sendall

You can use $sendall for setting values in columns of a row variable.

A row is composed of a group of columns ($cols), so Row.$cols.$sendall() allows you to loop through the columns in a row applying whatever tricks you can pack between the brackets of the $sendall.

Do Row.$cols.$sendall(Row.[$ref.$ident].$assign(Value),[Criteria]) Returns Count

Splitting the $sendall notation:

• Row.$cols.$sendall - This loops through the group of columns in the specified row variable.
• Row.[$ref.$ident].$assign(Value) - This is the calculation that is applied to each column.
• [Criterial] - This is optional criteria which can limit the columns to apply the calcuation to.
• Count - The number of rows the sendall was applied to.

Set Row Values

Set Row Nulls to blank

Count Row Nulls

$sendall

returns the number of objects that the $sendall was applied to. We can use this to tell us the number of null values columns in a row.

In this example we leave out the calculation portion of the $sendall, and only include the criteria portion.

; Count the number of nulls in the row.
Do Row.$cols.$sendall(,isnull(Row.[$ref.$ident])) Returns NumNulls


; To do the above in a For loop.
Do Row.$cols.$first() Returns rCol
While rCol
   
   If isnull(Row.[rCol.$ident])
      Calculate NumNulls as NumNulls+1
   End If
   
   Do Row.$cols.$next(rCol) Returns rCol
   
End While

Compare Row Values

We can use $sendall to compare 2 rows and return the number of column values which are different. This might be useful for figuring out if the user has made changes to a new row compared to the old row.

; Compare the new row with the old row to see if there have been any changes.
Do NewRow.$cols.$sendall(,NewRow.[$ref.$ident]<>OldRow.[$ref.$ident]) Returns NumDiff


; To do the above in a For loop.
Do NewRow.$cols.$first() Returns rCol
While rCol
   
   If NewRow.[rCol.$ident]<>OldRow.[$ref.$ident]
      Calculate NumDiff as NumDiff+1
   End If
   
   Do NewRow.$cols.$next(rCol) Returns rCol
   
End While

This method does not work for null value columns.

StudioTips Documentation - Copyright 2006 Vencor Software