When performing queries against a SQL database (SQL Server, PostGres, MySQL, etc), Foxfire! does not automatically use wild-card searches for LIKE filter operations. Depending on the SQL database, this effectively makes the Like and Exactly Like act the same way. You can add the SQL wild card character (typically %) to the Filter value and Foxfire! will change the filter to act as a wild card.
For example, without a “%”, a query like:
Cars Make Is Like Fo
becomes
CARS.MAKE = 'Fo'
This would exclude any Ford vehicles. However, if the filter condition was
Cars Make Is Like Fo%
Ford would be included as the SQL condition would be CARS.MAKE LIKE ‘Fo%’
In certain implementations, you may want every query that uses a given data item to assume the client has entered the wildcard character.
Approach 1: Transform the Filter Value
Individual data items can be configured to automatically add the % to the end. This is done in the Filter Transformation property (on the filter tab). Set the value to *VALUE* + “%”.
The *VALUE* refers to whatever has been entered as the filter value, either through Ask At Runtime OR in the filter dialog. When Foxfire! runs a query, it will automatically add % to the end of the field.
Approach 2: Use Foxfire! Events
NOTE: This tip applies to regular queries – it does not affect Ask At Runtime queries. To do this, use the Transformation method noted above.
If you want to always make any filter that uses “Like” to, this can be addressed by updating the BEFORE SQL GENERATION phase in the FFCONFIG.PRG.
If you haven’t made any customizations to your FFCONFIG file, you can make these changes using the built-in Event Editor under System Admin tools. If not, you will need to make this change directly in the file using Visual FoxPro.
In the FFCONFIG file, find the BEFORE SQL GENERATION section. Right after that line, add the following lines:
LOCAL lni
FOR lni = 1 TO flt_cnt
IF NOT EMPTY(flt_opr[lni])
IF UPPER(flt_opr[lni])="LIKE" AND NOT EMPTY(flt_val[lni])
flt_val[lni] = TRIM(flt_val[lni]) + IIF(RIGHT(TRIM(flt_val[lni]),1)="%","","%")
ENDIF
ENDIF
ENDFOR
The above code explicitly adds the SQL Wildcard character % to the filter value.
Foxfire! will then adjust the SQL to always use wild card searches.
Again the above approach ONLY applies to filters explicitly entered in the reports.