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. If we want to show all order detail records with a quantity ordered greater than or equal to 4, we would do something like:
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. The loop will process 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, we would get something like:
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 table level. We recommend using SQL filters under controlled finding conditions only. When such conditions apply, one will switch ON filtering, process records and 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.
Note that by default, global SQL filters are not applied to all find operations. The exceptions are Find EQ and FindByRowId. This exception exists to not break relates. The DF_FILE_SQL_FILTER_EQ attribute controls this behavior. So if you want your user to never see data that doesn’t match your filter (for example when building multi-tenant applications), then you need to make sure that DF_FILE_SQL_FILTER_EQ is set to true for each table.