Tips_sql   >   Smartlists   >   Smartlists (All Contents)

Smartlists

Smartlists are a terrific feature of Omnis Studio. You can turn any list into a smartlist by issuing:

Do List.$smartlist.$assign(kTrue)

The first time you do this to a list, Omnis Studio creates a matching history list. The history list tracks all the changes you make to the normal list.

If the smartlist was defined using $definefromsqlclass when you issue a $dowork() and Omnis Studio automatically inserts the new records, updates the changed records, and deletes the deleted records. Smartlists combined with the $dowork method can save you a ton of work if you are using SQL... another reason for using SQL.

You can also filter smartlists to hide and then show different sets of records.

The documentation on smartlists in Omnis Studio is pretty good so there's no need for me to repeat what has already been said.

Some things that weren't clear to me in the documentation:

  1. You can't view the $history list (bummer). You can only imagine and interrogate it. However with StudioTips you can 'view' the history list ... try the History List topic and demo.
  2. A row variable can't be a smartlist. (You can't add or delete rows from a row).

    Tip

    You can use a single row smartlist instead of a row variable. The thing to watch out for is that when you create the single row list the current line is zero. You must remember to Do List.$line.$assign(1) so that your single row will be the current row.

  3. If you copy ListA which is a smartlist to ListB, ListB is also a smartlist with the exact same history list.
  4. $dowork calls $dodeletes, then $doupdates, and finally $doinserts. Each plural named method then calls the similarly named but singular method for the approriate rows. (i.e ) $doupdates calls $doupdate for each row.
  5. $clear disables $smartlist. If you Do List.$clear(), you don't need Do List.$smartlist.$assign(kFalse).

History List

The moment you issue a Do List.$smartlist.$assign(kTrue), a history list is created which tracks each line in the normal list. The history list contains all the lines in the normal (visible) list, plus any filtered or deleted lines. The history list keeps track of the status for each line. You can access the information in the history list using $history.LineNumber to reference the specified line.

; Make a list of the history list $rownumber property.
Do List.$history.$makelist($ref.$rownumber) Returns HistoryList

; Search the history list to find the current line in the normal list.
Do HistoryList.$search($ref.C1=List.$line,1,0,0,0) Returns %L

; Find out the $status property of the current line in the history list
; $status can be kRowUnchanged, kRowDeleted, kRowUpdated, or kRowInserted
Calculate Status as List.$history.[%L].$status

Warning

The line number of a record in the history list is not necessary the same line number in the normal list. If any normal lines are deleted, the line numbers could be different. To find the matching line in the history list you need to find the history list $rownumber property which matches the normal list line number.

The history list has the following properties (attributes):

SmartList Notation

Listed below are the available smartlist notation methods.

$savelistdeletes() - Removes all kRowDeleted rows from the history list.

$savelistinserts() - Changes all kRowInserted rows to kRowUnchanged, and sets the old contents of those rows to the current contents. It does not change $rowpresent

$savelistupdates() - Changes all kRowUpdated rows to kRowUnchanged and, for all rows, sets the old contents to the current contents; this does not change $rowpresent

$savelistwork() Quick and easy way to execute the $savelist... methods

$revertlistdeletes() - Changes all kRowDeleted rows to kRowUnchanged or kRowUpdated (depending on whether the contents have been changed); for these rows $rowpresent is set to kTrue

$revertlistinserts() - Removes any inserted rows from both the normal and history list

$revertlistupdates()

- Changes all kRowUpdated rows to kRowUnchanged and, for all rows, the current contents are set to the old contents; this does not change $rowpresent.

$revertlistwork() - Quick and easy way to execute the $revertlist... methods

$includelines(kRow...) - Includes rows of a given status represented by the sum of the status values of the rows to be included. Thus 0 means no rows, kRowUnchanged + kRowDeleted means unchanged and deleted rows, and kRowAll means all rows, irrespective of status.

$filter(SearchCriteria) - Applies a filter to a smart list; this method restricts the list to only those rows which match the search calculation; for example, Do List.$filter($ref.Col1=10) will only display lines where Col1 is 10

$unfilter(level) - Removes a filter or filters from a smart list. Level of zero (0) removes all filters.

$refilter() - Reapplies all current filters to a smart list.

$includelines

Do List.$includelines(kRowDeleted+kRowInserted+kRowUpdated+kRowUnchanged)

This function will filter the smartlist to include the lines you specify by the constants.

kRowAll means all rows, irrespective of status.

Filters

Filters only work on smartlists. $filter removes the line(s) from the normal list which don't match the filter criteria. $filter doesn't change the line status in the history list. You can stack up to 15 filters and recall any of them. Anytime you use $unfilter(0), all the lines will be shown again. I either don't understand $refilter() or it doesn't work. The documentation says it is supposed to reapply all the filters?

  1. Do List.$filter($ref.ColName=Value)
  2. Do List.$unfilter(1) - removes all filters back to the first filter applied
  3. Do List.$unfilter(0) - removes all filters, showing all prefiltering lines
  4. Do List.$refilter() - Is supposed to reapply all filters. I haven't gotten this one to work yet. (See Demo)

$search is much faster than $filter. $filter can be slow on large lists (1000+ lines)

Only used $filter if you are going to follow with $dowork() or you need the ability to $unfilter(0).

There is a lot to learn about smartlists and $filter, read the Omnis Studio documentation.

$filter - using a variable for the criteria

If you want to use a variable for your $filter criteria, do the following:

Calculate SearchString as '$ref.ColumnName>Value'
Do List.$filter(eval(SearchString))

You must use eval() in order for it to work.

Overriding Smartlist Table Class Methods

The smartlist is a powerful feature of Omnis Studio. However, if you want to add special calculations, check the data, etc. in your table class before allowing records to be saved you need to intercept these smartlist methods.

When $dowork executes, Omnis Studio processes the smartlist by the history list rows $status in the following order:

  1. kRowDeleted - the table class method $dodelete is called for each deleted line.
  2. kRowUpdated - the table class method $doupdate is called for each updated line.
  3. kRowInserted - the table class method $doinsert is called for each inserted line.

The plural form of each of the above methods, $dodeletes, $doupdates, $doinserts processes their respective row statuses and calls the singular form methods, $dodelete, $doupdate, $doinsert for each row processed.

If you want to make a calculation or redirect any of the smartlist methods, you simply insert the appropriate $do... method in your table class and add your own code.

I like to intercept the singular $do... methods ($dodelete, $doinsert, $doupdate). For each of these single row methods you need you need to add a pfRow field reference parameter. You must use a field reference parameter in order to make your calculations appear in the normal list.

In my application, I wanted to redirect the smartlist $doinsert to use my base table superclass $insert, the $doupdate to use my $update, the $dodelete to use the $delete. Each of these methods is automatically called by $dowork or plural form $do... method.

The code I use for accomplishing this is as follows:

$doupdate (pfRow,pfRowOLD) ;; field reference parameters
Calculate Row as $cinst ;; bind Row to the current table class Do Row.$assignrow(pfRow) ;; copy the values to lv Row
Do Row.$update(pfRowOLD) ;; call $update method for this table class

$doinsert (pfRow) ;; field reference parameter
Calculate Row as $cinst ;; bind Row to the current table class Do Row.$assignrow(pfRow) ;; copy the values to lv Row
Do Row.$insert ;; call $insert method for this table class

$dodelete (pfRow) ;; field reference parameter
Calculate Row as $cinst ;; bind Row to the current table class Do Row.$assignrow(pfRow) ;; copy the values to lv Row
Do Row.$delete ;; call $delete method for this table class

I tested $dowork twice in a row to make sure it would not try to $insert the records twice. Oops it did. I had to modify $dowork as follows to prevent this from happening.

$dowork
Do default
Do $cinst.$savelistwork() ;; match the history list to current list

If you were not redirecting $doinsert to $insert you would not need to add the above.

$dowork SQL Demo

When you send a $dowork message to a smartlist what other $do... methods are invoked, and in what order?

$dowork processes each line of the smartlist which has a status other than kRowUnchanged. Processing is done in the following order: deletes, updates, inserts.

It makes sense to do the deletes first. If there was a unique index constraint conflict between an inserted record and a deleted record, you would want to make sure the kRowDeleted record was removed from the database before doing the insert.

For each kRowDeleted $dowork issues a $dodelete
For each kRowUpdated $dowork issues a $doupdate
For each kRowInserted $dowork issues a $doinsert

$dodelete does not issue a $delete
$doupdate does not issue a $update
$doinsert does not issue a $insert

$dowork does not issue $doinserts, $doupdates, or $dodeletes. These plural forms of the $do... methods are only invoked if you call them yourself. They in turn call their respective singular form for each insert, or update, or delete.

If you add a $doupdate method to your table class, be sure to add the parameters:

    If you add a $doinsert or $dodelete method to your table class, be sure to add the parameter:

    pfRow: Field reference variable

    The pfRow field reference variables give you access to the row. If you change any column values in your custom $do... method, the changes will then be reflected in the normal list.

    Click the Run Demo button to try out the various $do... series methods on a demo database. Members is a standalone table, so you won't affect other data in the demo.

    $dowork with Query Classes

    What happens if you send a $dowork message to a smartlist that is based on a query class?

    If the query class only includes a single schema class $dowork behaves exactly as it would for a schema class based list or row variable.

    If the query class includes more than one schema class, $dowork receives the first message but then Omnis Studio does not attempt to process the $dowork any further. The SQL92 standard, for very logical reasons, doesn't allow you to execute insert, delete, or update on views which include more than one server table. $dowork is following the SQL92 standard.

    It is possible to have a own multi-servertable query class smartlist by creating a table class which has its $sqlclassname property pointing to a multi-servertable query class and then adding your own $dowork method. The custom $dowork method would need to manually copy the column values for the individual schema classes and then insert, update, or delete the rows based on the smartlist row status and whether it was processing a foreign table parent record or a child record.

    Creating a generic method for accomplishing for any mult-servertable query class would be difficult because there is such a wide range of possible constraints and dependencies between different server tables included in a query class (view). But there is nothing stopping you from creating custom query class $dowork table class methods. Think of the multi-servertable query class based table class as a data object. What goes on inside the object (table class) when it receives the $dowork message is internal to the data object. The smartlist along with its history list is available to any list that is set to a smartlist using Do List.$smartlist.$assign(kTrue).