StudioTips - Lists Miscellaneous

Tips > Lists > Lists Miscellaneous

Lists Miscellaneous

Included in this section are miscellaneous list related topics.

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

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