SETFILTER - Using Wildcard *

TFCrowther

Applying the SETFILTER using a Wildcard filter against a value from another field does not work.

For example if the OtherFieldValue is another field from a different table and you need to filter a text field for names begining with the letter 'G'
You would expect the SETFILTER to return only records begining with 'G'

OtherFieldValue := 'G';
SETFILTER(name,'%1*',OtherFieldValue);
This does not work

A way around this is to use the STRSUBSTNO to include the WILDCARD

SETFILTER(name,'%1',STRSUBSTNO('%1*',OtherFieldValue));

kriki

This is already enough:

SETFILTER(name,'%1',OtherFieldValue + '*');

Belias

Another info if you do not know: in version 3.10 (and below i suppose) if you apply a filter like <>A*, it doesn't work!(from code and also using F7 on a field)

arcull

Belias, this I gues doesn't work in any Navision version.

kriki

Belias wrote:

Another info if you do not know: in version 3.10 (and below i suppose) if you apply a filter like <>A*, it doesn't work!(from code and also using F7 on a field)

This works with a SQL-DB but not with a Navision-DB.

Belias

Tested in a 3.60sql db and not working...
5.0fdb not working...
4.0sp1 sql working...
3.70 sql working...so.......................
only in version 3.70 and above, for sql DBs only, filters like <>A* works!

archaingels

Hi,

I am using Nav. 4.0 SP2 SQL DB.

I am having a problem with using setfilter with *

SETFILTER(Field,'%1',STRSUBSTNO('%1*',TEXTVALUE));
OR
SETFILTER(Field,'%1*',TEXTVALUE);

I am having an error message:

The Filter cannot have question mark(?),asterisk(*), or 'at' sign (@)

thanks,

archaingels

kriki

On what type of field you are using the filter?

Belias

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).

"Kine" wrote the lines above in the topic below, I hope it will definitively complete this "tips & trick" topic

I Wrote:

Tested in a 3.60sql db and not working...
5.0fdb not working...
4.0sp1 sql working...
3.70 sql working...so.......................
only in version 3.70 and above, for sql DBs only, filters like <>A* works!

I was talking about inserting filters within the mask (user's side - F7).
In C/AL code you must handle wildcards in the way written in the link below. Hope this helps!

http://www.mibuso.com/forum/viewtopic.php?t=23219&highlight=

meehi

Hi everyone!
I'm using Navision financial 2.65.

I'm trying to search for this string in the database:

@ahoo.com

.SETFILTER(Email,'%1','@ahoo.com'); -> not working (couse: @ - special char)
.SETFILTER(Email,'%1','*ahoo.com'); -> not working (couse: dont give back the exact result)

Are there any "ESCAPE" character in Navision I can use.
Thanks for the help.

not SQL database....

kriki

SETRANGE(Email,'@ahoo.com');

or also

SETFILTER(Email,'%1','?ahoo.com');

the SETRANGE is generally better for performance.

Belias

kriki wrote:

SETRANGE(Email,'@ahoo.com');

or also

SETFILTER(Email,'%1','?ahoo.com');

the SETRANGE is generally better for performance.

I suppose he wants *@ahoo.com...

BTW...meehi, did you try with the find/replace(or replace all) feature with @ahoo.com keyword?
oh! damn it!this topic is double posted in navision too!
edit: not really...only the question is duplicated

meehi

Belias wrote:

kriki wrote:

SETRANGE(Email,'@ahoo.com');

or also

SETFILTER(Email,'%1','?ahoo.com');

the SETRANGE is generally better for performance.

I suppose he wants *@ahoo.com...

BTW...meehi, did you try with the find/replace(or replace all) feature with @ahoo.com keyword?
oh! damn it!this topic is double posted in navision too!
edit: not really...only the question is duplicated

continue this here:
http://www.mibuso.com/forum/viewtopic.php?p=125500#125500

danielbouwmeester

I have also a question concerning this topic. I'm making in NAV4.6 a report, in which I generate a list of my articles, my articles all have an alphanumeric No. and I want to filter out the article No.'s containing the combination 'PIP'. I thought this would be easy by inserting a predifined filter in Table View -> No. <> *PIP*. But this didn't seem to work. Then I tried to insert a line of code at the DataItem 'Item', to skip the records containing 'PIP' by:
IF "Item"."No." = '*' + 'PIP' + '*' THEN CurrReport.SKIP; but it didn't seem to work. Can someone give me a hint!?

Belias

does nav 4.6 exists? :shock:
anyway...in a sql db 4.01 it works

also: the code you wrote is wrong

IF "Item"."No." = '*' + 'PIP' + '*' then
  currreport.skip;

with this code you skip THE item named *PIP*...in other words...you're not setting a filter with this code!!

the correct code to achieve your goal is this (use it only if the filter does not work, as it is slow)

IF strpos("Item"."No.",'PIP') <> 0  then
  currreport.skip;

see the online help for strpos function