Smartlists

Smartlists are a terrific feature of 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 creates a matching History list. The history list tracks all the changes you make to the normal list.

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

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

The documentation on smartlists is pretty good in Studio, 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 demo.

2. A row can't be a smartlist. (You can't add or delete rows from a row).

Note: You can use a single row smartlist instead of a row variable (Craig Lewis and others use this trick to make a "smart" row). 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 a List which is a smartlist to List2, List2 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.

$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:
->pfRow: Field reference variable ->pRowOLD: Row variable.

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.

Trying to redirect $dodelete to $delete using pfRow does not work. You can only accomplish this using a "clean $definefromsqlclass" row variable, assigning the column values to it, and then sending the $delete message to the "clean" row variable. This gets a little tricky because you have to properly handle SQL errors and make sure the smartlist history status is properly set. (Use $savelistdeletes or $undolistdeletes)

$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 Studio doesn't attempt to process the $dowork any further. The SQL 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 SQL 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.

$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))

$includelines

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

Filters

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

Filters only work on smartlists Filter removes the line(s) from the normal list which don't match the filter criterial, but 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?

WARNING: $search is MUCH faster than $filter. $filter can be SLOW on large lists (1000 lines)

SUGGESTION: 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 $smartlist and $filter, read the documentation

I used $smartlists for all my connected child records (ie. Invoice items, PO Items) that way I don't have to keep track of whether a child item was added, deleted, or updated the $smartlist, $history list, and $dowork keep track of this and handle it without my intervention.

WARNING about History List the Line# of a record in the SMARTLIST is NOT NECESSARILY the same Line# in the HISTORYLIST!!!

History List

The moment you "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.

CAUTION: The line number in the history list does NOT necessarily match the line number in the normal list. If any lines were deleted from the normal list, they are still in the history list. To find the status of a line in the smartlist, you must $search for the same line in the $history list. The following is an example how find the matching line in the history list.

-----
Do List.$history.$makelist($ref.$rownumber) Returns HistoryList
Do HistoryList.$search($ref.C1=List.$line,kTrue,kFalse,kFalse,kFalse) Returns %L \
Calculate #S1 as HistoryList.$status ;; tells you the status of the current line in the list
$status can be kRowUnchanged, kRowDeleted, kRowUpdated, or kRowInserted

HISTORY LIST ATTRIBUTES

$currentcontents - current value of the row from the normal list,
if the status is not kRowDeleted, and $rowpresent is kTrue

$errorcode - error code associated with a row (initially zero)

$linecount - number of rows in the history list

$oldcontents - the old contents of a row

$rowpresent - removes or adds the row to the normal list

Overriding smartlist methods

The smartlist is a powerful feature of 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, Studio calls $dodeletes, then $doupdates, and finally $doinserts. Each of these methods process the rows in the smartlist which match their status type. The respective $dodelete,$doupdate,$doinsert method (singular) is called. The row variable is passed to it.

So, 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 'stick'.

EXAMPLE:
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 > $doinserts,... (plural) if you call them.

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 $dowork would not try to $insert the records twice. Oops it did. I had to modify $dowork as follows to prevent this from happening. If you were not redirecting $doinsert to $insert you would not need to add the following:

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

SmartList Notation

Listed below are the available smartlist notation methods.

$savelistdeletes()
removes all kRowDeleted rows from the history list, and also from the normal list if $rowpresent is kTrue

$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()
$includelines(status) 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

$filter()
$filter(search-calculation) 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(COL1 = Ô10Õ) will only display lines where COL1 is 10

$unfilter()
$unfilter(level) removes a filter or filters from a smart list

StudioTips Documentation - Copyright 2006 Vencor Software