Filtering problem - MakeFilter

[Djordje Radovanovic]

I have problem with locator field in my browse.

Example:
Locator is set on field ROB:Description. When user enter 'PLAS' and filter is set to 'Start-With' type locator my browse is empty.

I dig around and found that MakeFilter routine crates this problem. It creates filter like:
(SQL(A.Internet = 1)) AND ( UPPER(ROB:Description) >= 'PLAS' AND  UPPER(ROB:Description) <= 'PLAS<254>')
I understand that this construction is very appropriate for ISAM file drivers but there is less and less application that use them.
Firebird SQL is not happy with this construction and it returns empty dataset. I believe that this is the same for other SQL flavors.

I am sure that it is not easy to change this MakeFilter routine to give us something like A.Description like 'PLAS%' and in other variation  A.Description like '%PLAS%' but it is possible to make in template placeholder to be included in our filter like this
(SQL(A.Internet = 1 %SomeLocatingString)
%SomeLocatingString will be defined by programmer and in my case will looks like ' AND A.Description like '''&clip(UserLocatingString)&'%'''.
If UserLocatingString is empty same apply for %SomeLoactingString and it will also be empty.
This option will give us freedom to use our locating options and mend this problem.
Of course this options will be used by our responsibility.

Best regards,

Djordje Radovanovic

[Bruce]

Hi Djordje,

Are you adding the
SQL(A.Internet = 1)
part as your filter on this browse?

What happens if you remove that filter part?

cheers
Bruce

[Djordje Radovanovic]

Yes, it is Browse filter. It is more complicated but for example simplicity I just use this part of filter.

My browse with no locator filter with no problem. When locator is populated with less characters it works, for example
(SQL(A.PGOD =2016 and A.Internet = 1 and A.PCena > 0)) AND ( UPPER(ROB:Description) >= 'PLAS' AND  UPPER(ROB:Description) <= 'PLAS<254>')

gives me whole dataset with for "P" starting description, same for "PL", same for "PLA" but when I enter "PLAS" there are no records.
Same apply for locator field "SAP", it gives me no dataset but "SA" gives records with start with "SA" and  records start with "SAP" too.

This is filter string when I set filter fields in classic clarion fashion.
 (ROB:PGOD =2016 and ROB:Internet = 1 and ROB:PCena > 0) AND ( UPPER(ROB:Description) >= 'PLAS' AND  UPPER(ROB:Description) <= 'PLAS<254>')

<254> is nondisplayable  character and I manually changed it.

Conclusion is that the same is for filter string no meter how they are built.

Best regards,

Djole

[Djordje Radovanovic]

During my root cause analysis I found place for my code.

It is GenerateTableRows  Routine

    if clip(loc:LocatorValue) <> ''
      loc:FilterWas = sub(clip(FilterString),1,Len(clip(FilterString))-1)&' AND A.Description like '''&Clip(loc:LocatorValue)&'%'')'
      ThisView{PROP:Filter} = loc:FilterWas
    END

only trouble is that NetWeb.CLW routine SetView still tried to add locating part of filter. If I could stop generating code I will be a winner.
Now filter looks like this
 (SQL(A.PGOD =2016 and A.Internet = 1 and A.PCena > 0 AND A.Naziv like 'PLA%')) AND ( UPPER(ROB:Description ) >= 'PLA' AND  UPPER(ROB:Description ) <= 'PLA<254>')

Best regards,

Djole

[Djordje Radovanovic]

Found the way to stop generating filter code.

    if clip(loc:LocatorValue) <> ''
      loc:FilterWas = sub(clip(FilterString),1,Len(clip(FilterString))-1)&' AND A.Description like '''&Clip(loc:LocatorValue)&'%'')'
      ThisView{PROP:Filter} = loc:FilterWas
      loc:viewoptions = bor(loc:viewoptions, Net:NoFilter)
   END

First test looks good.

Best regards,

Djole

[Djordje Radovanovic]

Just for clarification. This workaround works with no problem, but you must enclose your code with, in my case, this code

      if clip(left(Loc:LocateOnFields)) = 'ROB:Description'
          ...
      END

Code is needed for every locator in your browse to make changes only for fields you need.

I found some other bad behavior in locator. When I have numeric field and immediate locating after every locating code execution NT code clears locating field and if you do not type very fast you could not locate on as many characters as you need but only one or two. This workaround gives me solution to mend this problem too.

Best regards,

Djole