SQLKey

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 routine

BuildSQLFilter ROUTINE

sqlFilter = ‘SQL( (1=1)’ !setting (1=1) will show all records

! in case no other option is true

If lShowShopID > 0
sqlFilter = SqlFilter & ‘ AND
(a.ShopID=’ &l

ShowShopID &’)’

end

if lThisType > 0 ! Show only one AcctType

sqlFilter = SqlFilter & ‘ AND (a.acctTypeid = ‘ & l

ThisType &’)’

end

if ExcludeClosed

sqlFilter = SqlFilter & ‘ AND (a.[status]=0)’

end

if ShowOnlyWatched
sqlFilter=SqlFilter&’ AND (a.WatchedFlag is NOT NULL AND a.WatchedFlag>0)’

END

CASE lColor

OF ‘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 parenthesis

BRW1::Changed = TRUE

ForceRefresh = TRUE

EXIT

The 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 

Go BACK