using QUOTE in a browse filter

[Mike McLoughlin]

One of the observers names is D'Auria and its throwing my filters out.

In a conditional browse filter I have set up the filter as

'upper(quote(rev:observer)) = ''' & upper(quote(p_web.gsv('trainingadvisor'))) & '''  '

but it still doesn't find D'Auria.  I checked the prop:filter and it was being doubled up correctly as D''Auria

Can anyone see anything wrong with the filter string? 

It works OK as long as there is no apostrophe in the name.

Mike

[Bruce]

can you post the filter here as it appears in DebugView?

cheers
Bruce

[Mike McLoughlin]

this is what Debugview shows

[6720] [st] [netTalk][thread=3] BrowseReviews :: Order: REV:Linkid,-REV:PlannedDate,REV:Sysid
[6720] [st] [netTalk][thread=3] BrowseReviews :: Filter: REV:Linkid = 119 AND upper(quote(rev:observer)) = 'ANTHONY D''AURIA'

the linkid is correct but I'm wondering if PROP:FILTER supports QUOTE?

I'm not using SQL but I presume the view uses PROP:FILTER

Mike

[kevin plummer]

try

'upper(quote(rev:observer),1) = ''' & upper(quote(p_web.gsv('trainingadvisor')),1) & '''  '

[Mike McLoughlin]

Thanks for the idea Kevin but the 1 flag doubled up the quotes:

[10276] [st] [netTalk][thread=4] BrowseReviews :: Order: REV:Linkid,-REV:PlannedDate,REV:Sysid
[10276] [st] [netTalk][thread=4] BrowseReviews :: Filter: REV:Linkid = 149 AND upper(quote(rev:observer),1) = 'ANTHONY D''AURIA'

Do you know anywhere in the browse, just before the filter is applied to that row, where I could run QUOTE on rev:observer itself?

Mike

[kevin plummer]

you could be right that the filter expression does not like quote.

so firstly I would try to prove what dos and doesn't work by hard coding the training adviser value.

'upper(rev:observer) = <39>' & 'ANTHONY D<39>AURIA' & '<39>'
'upper(quote(rev:observer)) = <39>' & 'ANTHONY D<39>AURIA' & '<39>'
'upper(quote(rev:observer),1) = <39>' & 'ANTHONY D<39>AURIA' & '<39>'

You could also create a binded hidden column in the browse and populate that in the view as it is being built.

something like: l:observer = upper(quote(rev:observer)) and then use l:observer in your filter expression.

Last idea is to use the L:Observer method and just strip out any ' using string theory as the browse is being built and out of your trainig advisor variable.

[Bruce]

Hi Mike,

>> where I could run QUOTE on rev:observer itself?

I think that's the root of your problem. You should not run Quote on rev:Observer.
The filter you should be aiming towards is

Filter: REV:Linkid = 149 AND upper(rev:observer) = 'ANTHONY D''AURIA'

Cheers
Bruce

[Mike McLoughlin]

still no luck - I even tried CLIP as a longshot:

[8304] [st] [netTalk][thread=4] BrowseReviews :: Filter: REV:Linkid = 149 AND clip(upper(rev:observer)) = 'ANTHONY D'AURIA'

If I put a space instead of the apostrophe, in both browses, it filters fine. 

The customer is using this as a temp workaround until I come up with a solution.

I'm working through Kevin's ideas (above) and will feedback my results here.

[Mike McLoughlin]

OK here's the combination that finally worked 

'upper(rev:observer) = <39>' & upper(quote(p_web.gsv('trainingadvisor'))) & '<39>  '

thanks to Kevin and Bruce