SETFILTER with Wildcard (NAV 4.0 SP3 SQL Client)

mickuk

Hi,

I have a question regarding the use of SETFILTER and wildcards. When writing a report in NAV (v4.0 SP3 SQL) any wildcard filters that i set using SETFILTER() appear to be ignored. The GETFILTERS() command shows that the reports are set on the data item; however I'm still presented with the data that should be filtered out.

I've noticed that there are several threads on this forum where people have a similar issue with earlier version of the NAV product. Does anyone know if this has been resolved, if not I will look to move the report outside of the NAV client and work with SQL directly.

Applying these filters directly on the table appears to work. The data that I wish to exclude is not shown, the problem only seems to be when applying the same logic behind the report ](*,)

Further Thread...

http://www.mibuso.com/forum/viewtopic.php?t=18962

Sample Code from report

    SETFILTER("FIELD1.", '<>%1', 'M*');
    SETFILTER("FIELD2.", '<>%1', '*MISC*');

Belias

I just tried using F7 to filter...it works...by code...it doesn't...really strange..... :shock:

Belias

this works...

SETFILTER("FIELD1.", '<>M*'); 
SETFILTER("FIELD2.", '<>*MISC*');

and THIS is really :shock: :shock: :shock: :shock:

mickuk

Thanks for your feedback Belias. Do you have the filters working in your second post on a report and not the table?

Belias

well...using F7 to fieldfilter a table or form, you can insert <>A* and so on...(be aware of case sensitivity)it works...
Then I tried to write a simple cu which reads a test table with 2 records:

v
voo

in the codeunit i wrote:

mytable.setfilter(myfield,'<>v*');
mytable.findfirst

the system returned the error record not found for the filter...so it's all right! (the "v" is lowercase because the field in the table is text in order to test also case sensitivity)

using %1 etc. the filter does't work...I don't know why...

kine

In some cases the "parameter replacement" is not working (it means that the string '%1' is not replaced by the parameter value). This is known bug since version 2.x and you can check that by checking the filter value after you set the filter (e.g. through MESSAGE('%1',GETFILTERS);).

mickuk

Thanks for your help guys I've moved outside of the C/SIDE environment to resolve the issue.

ufuk

You can try:

SETFILTER(YourField,STRSUBSTNO(Text001,FilterText))

where Text001 is <>%1*

I haven't had any problem with this method

mickuk

Thanks ufuk,

Our service centre advised against using the wildcard method when using the SQL option, however they were not able to say why.

The solution that was offered was to check the data when scanning the table using STRPOS()...

For example

IF STRPOS(Field1, 'M') <> 0 THEN
CurrReport.SKIP;

So bascially if anything other than 0 is returned we know that the M char has been found within our field. Seems a bit of a hack to get around the fact the filter just will not apply :P

kine

Filtr will apply, but sometime will not be created correctly... ;-)

Wiechardt

Hi there

ufuk is correct in saying that this:
SETFILTER(YourField,STRSUBSTNO('<>%1*',FilterText)) works and that
SETFILTER(YourField, '<>%1*',FilterText)) does not work and
SETFILTER(YourField, '<>%1',FilterText + '*')) does not work either

Very interesting is that neither of the abovementioned options work on C/SIDE server!

The first option is the only one that works and will only work on SQL.

kine

Very interesting is that neither of the abovementioned options work on C/SIDE server!

The first option is the only one that works and will only work on SQL.

It is not interesting - it is described in On-Line help in topic about entering filter criteria. There is remark that <> and * together is working just on SQL Option...

archaingels

All of this is not working for me.

I got an error message:

The sum of the values cannot be calculated because the filter contains question marks(?), asterisk(*), or 'at' sign (@).

I need help I really need to fix this issue for my report.

thanks,

archaingels

kine

You can use CALCSUMS only if you are filtering to continuous range. If you are using filtering with wildcards and <> you need to use loop to calculate the sums.

archaingels

Hi thanks for reply,

this is how I code

I need to filter the Field which is starting with MRN.

SETFILTER(FIELDS,'%1*','MRN');

then use calcsum

CALCSUMS(Record.Quantity);

then I get this error message

The values in QUantity cannot be calculated because the filter contains question mark(?), asterisk(*), or 'at' sign (@).

I already solved the problem using below codes

Text000 = MRN

SETFILTER(FIELDS,Text000+'..'+Text000+'99999999');
CALCSUMS(Record.Quantity);

hope it helps someone out there facing the same problem.

Anyway thanks again for reply please continue the good work in helping others like me.

Archaingels