Problem with a 'Contains' locator

[jtdunn]

I have a problem using a 'contains' locator in version 4.30 and 4.31 of NetTalk. The browse I am searching on is filtered with this statement in the Filter tab of the browse:

'vEMSItem:CstCatID = ' & p_web.GetSessionValue('lEMSCstCatID')

This yields this filter string: vEMSItem:CstCatID = 8 and it works correctly.

Because we need to search on any text within a column called: ItemDescription in the browse, I set up a 'contains' locator on the Item Description column.  This generates this filter string:

(vEMSItem:CstCatID = AND (Instring(upper('band'),upper(vEMSItem:ItemDescription),1,1) > 0)

This looks correct and I created a small windows tester app to demonstrate that it is and works correctly.  It does not work in the web app as no records are returned when the filter is applied.  After a lot of experimenting I discovered that if I remove the first condition: (vEMSItem:CstCatID = and filter with the second condition only the filter works.  It does not seem possible that this filter statement does not work but I cannot get it to work with both conditions.

[Bruce]

Hi John,

can you post the filter bit again, adding some spaces so it doesn't turn into smileys?

And which build of 4.31 did you test with?

cheers
Bruce

[jtdunn]

Hi Bruce,

I am using 4.31 PR 19.  The filter code looks like this ( I hope this works):

(vEMSItem:CstCatID = 8 ) AND (Instring(upper('band'),upper(vEMSItem:ItemDescription),1,1) > 0)

I am also experiencing another problem with this release but I'll create a new topic for that problem.

Thanks,

John

[jtdunn]

Another issue with the 'contains' locator/filter that I have discovered (using only one condition because the two conditions above return no records): in the page loaded browse with a result set that is larger than the page, pressing the next button removes the filter and I lose the filter result set.  This is a problem in that it is impossible to scroll through the filtered result set.

[ajosuna]

Hi John:

Make sure the fields that you use in your filter are hot.

HTH

[jtdunn]

Hi,

That fixes the problem ... but I don't really understand why.  The vEMSItem:CstCatID was not in the view but filter fields/columns do not need to be in the view (at least for SQL they don't and I am accessing SQL data).  Regardless, it is working now and that's all that matters.

The problem with the filter not holding when navigating to another page is still a very big problem.  My browse is page loaded with 10 items per page.  If the results set returned for the filter is 15 items I cannot page to the next 5 items after the first page of 10 items.  When I press the next button the filter is cleared and I lose the result set from the search.

Thanks again,

John

[Poul]

i concur -  I just applied pre-release 19 to  what was a working browse
- SQL, no filter just a default locator.

if the locator finds more than the records per page,
clicking next will blank  the locator
and the browse is filled from the next item without the contains being applied

i have this column with default ticked
and nothing in the Default Sort Order for the locator..

[Bruce]

Hi guys,

The next-page-locator thing is fixed. I'll release that (PR20) later today.

I'm also not sure why Hot-Fielding the field makes the filter work - that's a bit of a strange one.

Cheers
Bruce

[jtdunn]

Hi Bruce,

That's great news.  Thanks!  I look forward to testing PR20.

John

[Poul]

John,

did the hot field work for you because vEMSItem:CstCatID
was not a column? just curious.

[jtdunn]

Hi Poul,

vEMSItem:CstCatID is a column in the SQL view but was not in the Clarion View.  Adding it as a hot field added it to the Clarion View.  Still, that should not be necessary under 'normal' circumstances as is evident when using only that one condition in the filter.  That worked and vEMSItem:CstCatID was not included as a hot field.  What is mystifying is that with two conditions in the filter I had to add the vEMSItem:CstCatID as a hot field for the filter to work correctly.

John

[ajosuna]

Hi Bruce:

I ran into this a while back and reported it to you. This is the only way you can get the filter to work with SQL. It works the way it should if you make the filter fields hot.

Anthony