How Do I Define a Range for a Task’s Data View? (Magic xpa 2.x)
By default, Magic xpa will display all the records in the Main Source. When you use the Program Generator to create a program, for instance, you will see all the records in the table.
Usually, however, you will not want to see every record. You may want to exclude just certain records, such as records marked “Deleted”. Or, you may want to see only the sales for one sales rep. And often you will only want to find one unique record.
There are several ways you can limit the number of records in a Data View. All these options are located in the Range Window (Task->Range/Locate, or Ctrl+R), although the Range Columns conveniently also show directly on the Data View.
-
Range: Using the Range Columns
-
Expressions: Using a Boolean Expression
-
SQL Where: Using a SQL Where clause
Let’s go through each of these in more detail.
The first kind of Range is the From/To range. This is the simplest kind of range, and the one that is most often used. It allows you to specify a lower and upper bound to the filter, and gather all the records between those bounds. For alpha fields, it also does some masking, so that if the search string is followed by a ‘*’, all characters after the string are ignored.
This kind of Range is commonly used to select all records of one type (the same status, same country, same parent record ID), or to select one particular record, by setting the FROM and TO ranges to the same value.
In our example, we are searching for all the records that match a search string that is passed in to our program. If the string begins with:
The L
then we include that record. So the results include all:
The Lord of the Rings
movies.
This kind of range, however, cannot be used to search for text in the middle of a string, or to do more complex matches. For that you need a Range Expression.
For the sake of convenience, the From/To Range can be entered and viewed in several different places. Here is a comparison for the Range example shown above.
It doesn’t matter which option you use; they work alike.
The Range Expression option gives you more flexibility in setting a Range. You can enter any Expression you like, and the record will be select if the Expression evaluates to TRUE. In our example, we are using an expression that will return TRUE if the value is located anywhere in the string.
Here, we have the expression entered into the Range/Locate->Expressions tab (Ctrl+R). If the parm is blank, it returns ‘TRUE’LOG always, so every record is returned. Otherwise, it uses the InStr() function to check if the string is a substring of the current record. If it is, the Instr() function returns true, and that record is included in the view.
When you are working with an SQL table, the usual From/To Range options are translated into SQL statements at runtime. However, you can specifically enter SQL code in the DB SQL section of the SQL Where tab. The disadvantage to this is that SQL code is often DBMS dependent, so it might not be portable if you switch DBMSs.
However, you can alternatively enter an Magic xpa SQL Expression, which will be translated into an SQL clause at runtime.
The Online and Rich Client Samples projects (program DV06 and RDV06)
