Go BACK

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 by 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=' & lShowShopID &')'

   end

 

   if lThisType > 0      ! Show only one AcctType

  sqlFilter = SqlFilter &  ' AND (a.acctTypeid = ' & lThisType &')'

   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