Complex Filtering a SQL Browse in Clarion
-
I noticed a question on the SoftVelocity newsgroups a few days ago that has prompted me post this tip.If you want to have several options on your Browse such as shown on this screen shot..then you will want the list to respond as users set or reset any of the options.
-
What works well for me, is a local SQLFilter defined as a
CSTRING(401) that I set as my browse filter.. -
Then, whenever any of the user selectable items ( Check Boxes, Lookup Buttons, Drop-Combos)
are changed, I simply call a local routine to build by SQLFilter string for the back end to use.Here is a sample routineBuildSQLFilter ROUTINE
sqlFilter = ‘SQL( (1=1)’ !setting (1=1) will show all records! in case no other option is trueIf lShowShopID > 0
sqlFilter = SqlFilter & ‘ AND
(a.ShopID=’ &lShowShopID &’)’endif lThisType > 0 ! Show only one AcctType
sqlFilter = SqlFilter & ‘ AND (a.acctTypeid = ‘ & lThisType &’)’endif ExcludeClosedsqlFilter = SqlFilter & ‘ AND (a.[status]=0)’end
if ShowOnlyWatched
sqlFilter=SqlFilter&’ AND (a.WatchedFlag is NOT NULL AND a.WatchedFlag>0)’END
CASE lColorOF ‘RED’
sqlFilter = SqlFilter & ‘ AND (b.ItemColor=<39>R<39>)’
OF ‘GREEN’sqlFilter = SqlFilter & ‘ AND (b.ItemColor=<39>G<39>)’
OF ‘BLUE’sqlFilter = SqlFilter & ‘ AND (b.ItemColor=<39>B<39>)’
OF ‘ALL’
sqlFilter = SqlFilter & ‘ AND (b.ItemColor IS NOT NULL)’
END
sqlFilter = SqlFilter & ‘)’ ! add the ending parenthesisBRW1::Changed = TRUEForceRefresh = TRUEEXITThe code in the sample routine here does not match exactly the screen shot above, but you should get the idea. I added a CASE statement as an example of what can be done…Using the (1=1) is a trick to get all records if no other selection has been made that would otherwise
get into the filter. In such cases, the SQLFilter would end up as ‘SQL( (1=1) )’ and the SQL engine
would include the whole dataset.If you did not want to show any records until a user had made at least one option change, then just
initialize your SQLFilter to ‘SQL( (1=0) )’ in your window initialize area. Then the browse would
open showing no records.Using this technique, as long as you understand that the first table in your local view will be
prefixed a.
and the second as b.
and so on, then you can build some very complex filters quite easily.John Griffiths