Trouble with browse filter

questions
,
1

(also posted in the C11 newsgroup)

Hi,

I’m trying to filter a browse. I have a couple of filter fields and a
dropdown that are supposed to filter the browse. The first filter field
has to be filled in.

After a change in the any of the three filters I call a derived
windowmanager procedure:

ThisWindow.reset_filter PROCEDURE()

  CODE
  if filter1 <> ''
     filter_exp = 'sql(lower(ADDRESS) like <39>%' & FILTER1 & '%<39> and 
(<39>' & FILTER2 & '<39> is null or lower(ADDRESS) like <39>%' & FILTER2 & 
'%<39>) and (<39>' & LOC:COMMUNITY & '<39> is null or  <39>' & 
loc:community  & '<39> = city ))'
  else  
     filter_exp =  'V_C:address = <39>ZZZ<39>'
  end

The seecond filter should result in nothing showing on the browse, and is
the filter set on entering the procedure (cos Filter1 is blank when you
start), and we don’t want to waste time displaying everything in the file
on startup.

I have !filter_exp as the filter for browse and filter_exp as a reset
field for the browse. I’ve added a couple of other things in desperation:
BRW1.ApplyRange after changing filter1, and explicitly calling
BRW1.ResetSort after the parent method in TakeAccepted. I’ve put some
PROP:Logs in the trace to see what is going on, and it looks like this:

SETview(VIEW:65FC1F0:USE_CAPS."V_CASE_BASIC_ADDRESS":065FC108H) 
Filter reset to sql(lower(ADDRESS) like '%2%' and ('' is null or 
lower(ADDRESS) like '%%') and ('Rigolet' is null or  'Rigolet' = city ))  
02D64H(2) 14:20:52.247 Parsing T9 Cursor 106658244 : SELECT  A."FILE_NO", 
A."FIRST_NAME", A."LAST_NAME", A.ADDRESS, A.CITY, A."POSTAL_CODE", 
A."SERV_PROVIDER", A."HOUSING_START" FROM USE_CAPS."V_CASE_BASIC_ADDRESS" 
A WHERE (  A.ADDRESS = 'ZZZ' )  ORDER BY   A.CITY,   A."FILE_NO"  Return 
Code: 0 Time Taken:0.00 secs
036DCH(2) 14:36:07.624 SETview(VIEW:
6DA2288:USE_CAPS."V_CASE_BASIC_ADDRESS":06DA21A0H)   Time Taken:0.00 secs
036DCH(2) 14:36:07.624 CLEAR(USE_CAPS."V_CASE_BASIC_ADDRESS":0122CD68H)  
Time Taken:0.00 secs
036DCH(2) 14:36:13.739 Filter before resetsort is sql(lower(ADDRESS) like 
'%2%' and ('' is null or lower(ADDRESS) like '%%') and ('Rigolet' is null 
or  'Rigolet' = city ))
036DCH(2) 14:36:13.739 Parsing T9 Cursor 114661708 : SELECT  A."FILE_NO", 
A."FIRST_NAME", A."LAST_NAME", A.ADDRESS, A.CITY, A."POSTAL_CODE", 
A."SERV_PROVIDER", A."HOUSING_START" FROM USE_CAPS."V_CASE_BASIC_ADDRESS" 
A WHERE (  A.ADDRESS = 'ZZZ' )  ORDER BY   A.CITY,   A."FILE_NO"  Return 
Code: 0 Time Taken:0.00 secs
036DCH(2) 14:36:13.941 After resetsort

So what seems to be happening is that yes, the filter variable is being
changed, confirmed a) after the embed on filter1 sets it, and b) before
the second call to ResetSort in ThisWindow.TakeAccepted, but in both cases
the query sent to the backend is based on the original value of filter_exp
and not the current value.

What do I need to do to get it to use the current value of the filter
variable?

Jon

2

Jon, setting the filter to !filter_exp (i’m assuming in the Record Filter prompt) only sets the initial filter to the value of filter_exp. It passes the contents of filter_exp into the browse class SetFilter method. Not a reference to the variable, so if it changes there is no impact on the browse.
Instead, when you change filter_exp you need to call the SetFilter(filter_exp) again. The do your reset logic.

3

What rick said.
I think you need to use .SetFilter followed by .ApplyFilter
SetFilter set the filter expression and ApplyFilter actually applys it and makes it go.

4

Have not had filter failures with:
a) Call SetFilter from ApplyFilter before parent
b) Add filter variables as Browse reset variables
c) Use 1=0 filter as default on procedure entry to force empty list
d) Declare a set of string equates for filter number - ie FILTER:1 EQUATE(‘9’)
*** Note that ‘9’ has a higher priority than ‘1’
e) One can mix in SQL filter statements as needed
f) Include variable value in the filter instead of the variable name to avoid needing to BIND

The typical result is something like:
IF MyFilterValue = 0
SELF.SetFilter(‘0=1’,FILTER:1)
ELSE
SELF.SetFilter(‘TBL:Value=’ & MyFilterValue,FILTER:1)
END
SELF.SetFilter(‘TBL:String=’ & SQuote(MyFilterString),FILTER:2)
SELF.SetFilter('SQL(mysqltable.column LIKE '% ’ & MyLikeFilter & ‘%)’,FILTER:3)

5

Thanks guys,

Now works like it should. I was pretty sure it was something simple I was missing.

Jon