NetTalk Central

NetTalk Web Server => Web Server - Ask For Help => Topic started by: Mike McLoughlin on February 12, 2017, 06:37:28 AM

Title: using QUOTE in a browse filter
Post by: Mike McLoughlin on February 12, 2017, 06:37:28 AM

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

Title: Re: using QUOTE in a browse filter
Post by: Bruce on February 13, 2017, 05:11:33 AM

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

cheers
Bruce

Title: Re: using QUOTE in a browse filter
Post by: Mike McLoughlin on February 14, 2017, 10:22:02 AM

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

Title: Re: using QUOTE in a browse filter
Post by: kevin plummer on February 14, 2017, 11:21:35 PM

try

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

Title: Re: using QUOTE in a browse filter
Post by: Mike McLoughlin on February 15, 2017, 10:12:19 AM

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

Title: Re: using QUOTE in a browse filter
Post by: kevin plummer on February 15, 2017, 04:28:13 PM

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.

Title: Re: using QUOTE in a browse filter
Post by: Bruce on February 16, 2017, 06:00:38 AM

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

Title: Re: using QUOTE in a browse filter
Post by: Mike McLoughlin on February 16, 2017, 02:32:12 PM

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.

Title: Re: using QUOTE in a browse filter
Post by: Mike McLoughlin on February 16, 2017, 07:41:08 PM

OK here's the combination that finally worked  ::) ::)

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

thanks to Kevin and Bruce