Skip to content

Using SQL Filters (Constraints)

Note

This information pertains to using SQL Filters at the table level. We recommend that you use SQL Filters at the data dictionary level whenever possible.

SQL Filters can be used with all DataFlex SQL Drivers. If you want to filter the records processed by a function, you can check for the record contents in the DataFlex code. For example, to show all order detail records with a quantity ordered greater than or equal to 4, you would do something like this:

Procedure ShowDetails
    Open OrderDtl
    Clear OrderDtl
    Repeat
        Find Gt OrderDtl By Index.1
        If (Found And OrderDtl.Qty_Ordered >= 4) ;
            Showln OrderDtl.Order_Number ", " ;
            OrderDtl.Detail_Number ", " ;
            OrderDtl.Qty_Ordered
    Until (Not(Found))
    Close OrderDtl
End_Procedure

The loop above will work but may be a bit slow, as it processes all records in the Order Detail table. Using filters, you can limit the number of records that need to be processed. If we rewrite the code above to use SQL filters, it would look like this:

Procedure ShowDetailsFilter
    Handle hTable
    String sFilter
    Open OrderDtl
    Move OrderDtl.File_Number to hTable
    Set_Attribute DF_FILE_SQL_FILTER of hTable ;
        to "OrderDtl.Qty_ordered >= 4"
    Set_Attribute DF_FILE_SQL_FILTER_ACTIVE of hTable to True
    Clear OrderDtl
    Repeat
        Find Gt OrderDtl By Index.1
        If (Found) ;
            Showln OrderDtl.Order_Number ", " ;
            OrderDtl.Detail_Number ", " ;
            OrderDtl.Qty_Ordered
    Until (Not(Found))
    Set_Attribute DF_FILE_SQL_FILTER_ACTIVE of hTable to False
End_Procedure

The SQL filter settings are set at the table level. We recommend using SQL filters under controlled finding conditions only. When such conditions apply, you should switch ON filtering, process records, and then switch off filtering again.

When using SQL filters in data dictionary constraints, be aware that navigating between application views requires the SQL filter to be reset.

Special Note

By default, global SQL filters are not applied to all find operations. The exceptions are Find EQ and FindByRowId. This exception exists to avoid breaking relationships. The DF_FILE_SQL_FILTER_EQ attribute controls this behavior. If you want your users to never see data that doesn’t match your filter (for example, when building multi-tenant applications), ensure that DF_FILE_SQL_FILTER_EQ is set to true for each table.

See Also