| **Navigation:**  [[introduction.htm|Language Reference]] > 5 - Declaration Attributes > Variable and Entity Attributes >====== FILTER (set view filter expression) ====== | [[external  set defined externally .htm|{{btn_prev_n.gif|Previous page}}]][[introduction.htm|{{btn_home_n.gif|Return to chapter overview}}]][[implements add methods to a class .htm|{{btn_next_n.gif|Next page}}]] |
|  ||

**FILTER(**//expression//**)**

{{blk2blue.jpg|blk2blue.jpg}}

| **FILTER** | Specifies a filter //expression// used to evaluate records to include in the VIEW. |
| //expression// | A string constant containing a logical expression. |

The **FILTER** attribute (PROP:FILTER) specifies a filter //expression// used to evaluate records to include in the VIEW.

The //expression// may reference any field in the VIEW, at all levels of JOIN structures. The entire //expression// must evaluate as true for a record to be included in the VIEW. The //expression// may contain any valid Clarion language logical expression. The //expression// is evaluated at runtime (just like the EVALUATE procedure), therefore you must BIND all variables used in the //expression//.

**Use of MATCH with PROP:Filter and SQL Databases**

The use of PROP:Filter as an SQL filter generator for SQL databases now supports converting the [[match  return matching values .htm|MATCH]](s1, s2, //n//) function result to an appropriate SQL filter for all values of //n//, except Match:Regular. If you use the Match:Regular mode, the filter will be evaluated on the client side just like all other non-SQL convertible filters.

Other conversion rules:

**MATCH(s1,s2,Match:Simple)** will be converted to

**s1 = s2**

for all SQL drivers.

**MATCH(s1,s2,Match:Soundex)** will be converted to

**{fn SOUNDEX(s1)} = {fn SOUNDEX(s2)}**

for all ODBC back ends that support the SOUNDEX function.

**MATCH(s1,s2,Match:Soundex)** will be converted to

**SOUNDEX(s1) = SOUNDEX(s2)**

for the Oracle Accelerator.

**MATCH(s1,s2,Match:Simple + Match:NoCase)** will be converted to

**{fn UPPER(s1)} = {fn UPPER(s2)}**

for all ODBC drivers.

**MATCH(s1,s2,Match:Simple + Match:NoCase)** will be converted to

**UPPER(s1) = UPPER(s2)**

for the Oracle Accelerator.

**MATCH(s1,s2) !Match:Wild mode**

Will be converted to

**s1 LIKE %**

if the s2 parameter is using an asterisk (*), or

**s1 LIKE _**

if the s2 parameter is using a question mark (?) wild card.

This is valid for all SQL drivers.

Adding the //Match:NoCase// mode to the //Match:Wild// mode is converted to

**{fn UPPER(s1)} LIKE %** or **{fn UPPER(s1)} LIKE _**

for all ODBC drivers.

Adding the //Match:NoCase// mode to the //Match:Wild// mode is converted to

**UPPER(s1) LIKE %** or UPPER(s1) LIKE _

for the Oracle Accelerator.

**Example:**

<code>
BRW1::View:Browse VIEW(Members)
                   PROJECT(Mem:MemberCode,Mem:LastName,Mem:FirstName)
                 END
KeyValue STRING(20)

!Get only orders for customer 9999 since order number 100
ViewOrder VIEW(Customer),FILTER('Cus:AcctNumber = 9999 AND Hea:OrderNumber > 100')
          PROJECT(Cus:AcctNumber,Cus:Name)
           JOIN(Hea:AcctKey,Cus:AcctNumber)     !Join Header file
            PROJECT(Hea:OrderNumber)
            JOIN(Dtl:OrderKey,Hea:OrderNumber)  !Join Detail file
             PROJECT(Det:Item,Det:Quantity)
             JOIN(Pro:ItemKey,Dtl:Item)         !Join Product file
              PROJECT(Pro:Description,Pro:Price)
             END
            END
           END
         END !view
  CODE
    BIND('KeyValue',KeyValue)
    BIND(Mem:Record)
    KeyValue = 'Smith'
    BRW1::View:Browse{PROP:Filter} = 'Mem:LastName = KeyValue'  !Specify filter condition
    OPEN(BRW1::View:Browse)                                     !Open the view
    SET(BRW1::View:Browse)                                      !and set to the beginning 
                                                              !of the filtered

  CODE                                                        !and ordered result set
    OPEN((Customer,22h); OPEN((Header,22h);  OPEN((Product,22h); OPEN(Detail,22h)
    BIND('Cus:AcctNumber',Cus:AcctNumber)
    BIND('Hea:OrderNumber',Hea:OrderNumber)
    SET(Cus:AcctKey)
    OPEN(ViewOrder)
    LOOP
       NEXT(ViewOrder)
       IF ERRORCODE() THEN BREAK.
       !Process the valid record
    END
    UNBIND('Cus:AcctNumber',Cus:AcctNumber)
    UNBIND('Hea:AcctNumber',Hea:AcctNumber)
    CLOSE(Header); CLOSE(Customer);  CLOSE(Product); CLOSE(Detail)
</code>

**See Also:**

[[bind  declare runtime expression string variable .htm|BIND]]

[[unbind  free runtime expression string variable .htm|UNBIND]]

[[evaluate  return runtime expression string result .htm|EVALUATE]]

[[order set view sort order expression .htm|ORDER]]

[[sql  use sql code .htm|SQL]]