SETFILTER - Using Wildcard *

TFCrowther
TFCrowther Member Posts: 35
edited 2009-01-07 in NAV Tips & Tricks
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));
Topaz Solutions Ltd - NZ

Comments

  • kriki
    kriki Member, Moderator Posts: 9,121
    This is already enough: 
    SETFILTER(name,'%1',OtherFieldValue + '*');
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Belias
    Belias Member Posts: 2,998
    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)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • arcull
    arcull Member Posts: 191
    Belias, this I gues doesn't work in any Navision version.
  • kriki
    kriki Member, Moderator Posts: 9,121
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Belias
    Belias Member Posts: 2,998
    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!
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • archaingels
    archaingels Member Posts: 5
    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
    God loves us!!!
  • kriki
    kriki Member, Moderator Posts: 9,121
    On what type of field you are using the filter?
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Belias
    Belias Member Posts: 2,998
    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=
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • meehi
    meehi Member Posts: 16
    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
    kriki Member, Moderator Posts: 9,121
    SETRANGE(Email,'@ahoo.com');

    or also

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

    the SETRANGE is generally better for performance.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Belias
    Belias Member Posts: 2,998
    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
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • meehi
    meehi Member Posts: 16
    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
    danielbouwmeester Member Posts: 22
    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!?
    "Make it idiot-proof and someone will invent a better idiot..."
  • Belias
    Belias Member Posts: 2,998
    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 ;)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog

Categories