Problem:Setfilter with *

iori · 2008-05-29

Hi All!
here is a table just have one field(code,10),and data in it:
5
61
71
8
50
60
7890
i want to get these data fit for such condition: 5* and 7* but not 50* and not 78*.
so I use setfilter (fieldname,'(%1|%2)&(<>%3)&(<>%4)','5*','7*','50*','78*');

but it doesn't work. it gives me:5,71,50,7890.

and i tried this filter:
setfilter(fieldname,'<>50*');
it doesn't filter 50.

somebody tell me,Nav can't do such a filter : <>50*
anybody can help ? many thanks!

rajpatelbca · 2008-05-29

hi,

use this "Seminar Room".setfilter (Code,'(5*|7*)&(<>50*)&(<>78*)'); instead of setfilter (fieldname,'(%1|%2)&(<>%3)&(<>%4)','5*','7*','50*','78*');

hope it helps

iori · 2008-05-29

hi rajpatelbca,
thank you! but isn't it the same?
using setfilter(code,'<>50*') and using setfilter(code,'<>%1','50*'),gives the same result~
by the way, if there is a record:'50*' in the table, it really filter that 50* out.

kine · 2008-05-29

iori wrote:

hi rajpatelbca,
thank you! but isn't it the same?
using setfilter(code,'<>50*') and using setfilter(code,'<>%1','50*'),gives the same result~
by the way, if there is a record:'50*' in the table, it really filter that 50* out.

It is not the same- first example filter for 50* where * is wildcard, second filter for anything different than value "50*" (asterisk is part of the value, is not used as wildcard).

iori · 2008-05-29

it really seems that NAV can't do such a filter. can filter like =50*,but can't filter <>50*...
guys,is there any soluation?

rajpatelbca · 2008-05-29

if you apply these two filter in a sequence then eg.

1. setfilter (fieldname,'(%1|%2)&(<>%3)&(<>%4)','5*','7*','50*','78*');
and then
2. setfilter(fieldname,'<>50*');

first it applies filter no 1. then if you apply second filter on same field like no 2. then first it will remove filter 1 and then apply filter.

As a result you will have only filter 2 on your record set.

Regards,

iori · 2008-05-29

kine wrote:

iori wrote:

hi rajpatelbca,
thank you! but isn't it the same?
using setfilter(code,'<>50*') and using setfilter(code,'<>%1','50*'),gives the same result~
by the way, if there is a record:'50*' in the table, it really filter that 50* out.

It is not the same- first example filter for 50* where * is wildcard, second filter for anything different than value "50*" (asterisk is part of the value, is not used as wildcard).

thank you kine,i've tried both way:setfilter(code,'<>5*') and setfilter(code,'<>%1','5*'),and use message(code) to show the record set, but system shows '5',as you tell me, this mustn't happen~:(

kine · 2008-05-29

In this case, you are using Native DB aren't you? The construction <>X* is working only with MS SQL server DB...

iori · 2008-05-29

rajpatelbca wrote:

if you apply these two filter in a sequence then eg.

1. setfilter (fieldname,'(%1|%2)&(<>%3)&(<>%4)','5*','7*','50*','78*');
and then
2. setfilter(fieldname,'<>50*');

first it applies filter no 1. then if you apply second filter on same field like no 2. then first it will remove filter 1 and then apply filter.

As a result you will have only filter 2 on your record set.

Regards,

Thank you rajpatelbca,i think i made a mistake in explanation,i just use a more sample filter(filter 2) instead of filter 1,to test the code following your advice, actually i havn't use two filter at the same time..

iori · 2008-05-29

kine wrote:

In this case, you are using Native DB aren't you? The construction <>X* is working only with MS SQL server DB...

!!!!,

,wonderfully~Thank you kine,I guess it's the answer that i need,i'm too shamed...Thanks again~

kine · 2008-05-29

iori wrote:

kine wrote:

In this case, you are using Native DB aren't you? The construction <>X* is working only with MS SQL server DB...

!!!!, ,wonderfully~Thank you kine,I guess it's the answer that i need,i'm too shamed...Thanks again~

You are welcome... 8)

rajpatelbca · 2008-05-29

ok welcome...

Regards,

Belias · 2008-05-29

http://www.mibuso.com/forum/viewtopic.php?t=18962
also this can clarify the wildcard behavior...

meehi · 2008-06-04

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.

Belias · 2008-06-04

SETFILTER(Email,'*ahoo.com');
should work (everything ending in ahoo.com)

meehi · 2008-06-04

Belias wrote:

SETFILTER(Email,'*ahoo.com');
should work (everything ending in ahoo.com)

Unfortunately this not good for me. This will find for example this too:
aaaxxx@yahoo.com

But I only want to find email ending like this:
aaaxxx@ahoo.com

(I want to find and correct wrong emails...)

meehi · 2008-06-04

//emails ending with '@ahoo.com'
txtSearchString := '@ahoo.com';

recEmails.SETFILTER(Email,'*' + txtSearchString);
recEmails.FIND('-');
REPEAT
intPos := STRPOS(recEmails.Email,'@');
IF COPYSTR(recEmails.Email,intPos,STRLEN(recEmails.Email) - intPos + 1) = txtSearchString THEN
MESSAGE(recEmails.Email);
UNTIL recEmails.NEXT = 0;

This works!!!
But very slow: if you have 1 million records ending with @yahoo.com and it has only 10 ending with @ahoo.com then you need to go through all the 1 million records to find those 10.

Belias · 2008-06-04

did you try with the find/replace(or replace all) feature with @ahoo.com keyword?

meehi · 2008-06-04

Belias wrote:

did you try with the find/replace(or replace all) feature with @ahoo.com keyword?

I have about 600 mistakes listed in excel, so I dont want to solve the problem manualy. I need to make a program code for this in a codeunit...

Belias · 2008-06-04

ctrl+h, find/replace all...this should be at least semi-automatic...

meehi · 2008-06-04

Belias wrote:

ctrl+h, find/replace all...this should be at least semi-automatic...

Sorry, you missunderstood me

.
I have about 600 email mistake endings. (like @ahoo.com, @yahho.com, @yahhoo.co.uk.hu ....)

kriki · 2008-06-04

recEmails.RESET;
IF recEmails.FINDSET(TRUE,TRUE) THEN
  REPEAT
    recEmails2 := recEmails;
    intPos := STRPOS(recEmails.Email,'@');
    CASE COPYSTR(recEmails.Email,intPos + 1) OF
      'yahho.com','ahoo.com': BEGIN
        recEmails2.Email := COPYSTR(recEmails2.Email,intpos) + 'yahoo.com';
        recEmails2.MODIFY(FALSE);
      END;
      'yahhoo.co.uk.hu': BEGIN
        recEmails2.Email := COPYSTR(recEmails2.Email,intpos) + 'yahoo.co.uk.hu';
        recEmails2.MODIFY(FALSE);
      END;
      ... and so on

    END
UNTIL recEmails.NEXT = 0;

meehi · 2008-06-04

kriki wrote:

recEmails.RESET;
IF recEmails.FINDSET(TRUE,TRUE) THEN
  REPEAT
    recEmails2 := recEmails;
    intPos := STRPOS(recEmails.Email,'@');
    CASE COPYSTR(recEmails.Email,intPos + 1) OF
      'yahho.com','ahoo.com': BEGIN
        recEmails2.Email := COPYSTR(recEmails2.Email,intpos) + 'yahoo.com';
        recEmails2.MODIFY(FALSE);
      END;
      'yahhoo.co.uk.hu': BEGIN
        recEmails2.Email := COPYSTR(recEmails2.Email,intpos) + 'yahoo.co.uk.hu';
        recEmails2.MODIFY(FALSE);
      END;
      ... and so on

    END
UNTIL recEmails.NEXT = 0;

WoW

.
Nice code and I think this could work, but this is slow, isn't it? I have 500000 e-mail addresses and I must do this routin daily.
And what is FINDSET??? I think this is not implemented in Nav2.65
If no other options then I think I will choose this one

. Thanks kriki

Belias · 2008-06-04

i thought you need it for a one shot launch!(in that case, write nearly 500-600 case statements would have been the same as do some copy/paste with find...)sorry...

meehi · 2008-06-04

Belias wrote:

i thought you need it for a one shot launch!(in that case, write nearly 500-600 case statements would have been the same as do some copy/paste with find...)sorry...

No, i wont write 500-600 case statements

.
But this part of the problem is solved (I will read the wrong and right email endings from an excel file and do this with variables. Just one IF THEN statement will do the rest

)

Belias · 2008-06-04

meehi wrote:

Belias wrote:

i thought you need it for a one shot launch!(in that case, write nearly 500-600 case statements would have been the same as do some copy/paste with find...)sorry...

No, i wont write 500-600 case statements .
But this part of the problem is solved (I will read the wrong and right email endings from an excel file and do this with variables. Just one IF THEN statement will do the rest )

in that case...no prob!

kriki · 2008-06-04

-FINDSET : use FIND('-') in older versions.

-In stead of using an Excel-sheet, create a new table and put the data in there. In the loop, you can add some things:
1) read the extra table and put it in a temptable (in memory! This way all those records are NOT read for each record in the emails-table).
2) in stead of the case, you can use a search on the temptable.
3) it will always be slow ... OR... in the table Emails, you need to create a new field ("email domain" => the email address domain-part) with an index on it and fill that field up on insert/modify of the email-address.
In this case you can loop the errors-table and then put a filter on the emails-table to find the records with a wrong email-address in it.

This is the code if you DON'T create an extra field:

// put all the errors to fix in a temptable
recEmailErrors.RESET;
IF recEmailErrors.FIND('-') THEN
  REPEAT
    tmpEmailErrors := recEmailErrors;
    tmpEmailErrors.INSERT(FALSE);
  UNTIL recEmailErrors.next = 0;


recEmails.RESET;
IF recEmails.FIND('-') THEN
  REPEAT
    intPos := STRPOS(recEmails.Email,'@');
    
    tmpEmailErrors.reset;
    tmpEmailErrors.SETRANGE("Wrong Domain", COPYSTR(recEmails.Email,intPos + 1));
    IF tmpEmailErrors.FIND('-') THEN BEGIN
        recEmails2 := recEmails;
        recEmails2.Email := COPYSTR(recEmails2.Email,intpos) + tmpEmailErrors."Correct Domain";
        recEmails2.MODIFY(FALSE);
    END;
UNTIL recEmails.NEXT = 0;

meehi · 2008-06-06

kriki wrote:
-FINDSET : use FIND('-') in older versions.

-In stead of using an Excel-sheet, create a new table and put the data in there. In the loop, you can add some things:
1) read the extra table and put it in a temptable (in memory! This way all those records are NOT read for each record in the emails-table).
2) in stead of the case, you can use a search on the temptable.
3) it will always be slow ... OR... in the table Emails, you need to create a new field ("email domain" => the email address domain-part) with an index on it and fill that field up on insert/modify of the email-address.
In this case you can loop the errors-table and then put a filter on the emails-table to find the records with a wrong email-address in it.

This is the code if you DON'T create an extra field:
// put all the errors to fix in a temptable
recEmailErrors.RESET;
IF recEmailErrors.FIND('-') THEN
  REPEAT
    tmpEmailErrors := recEmailErrors;
    tmpEmailErrors.INSERT(FALSE);
  UNTIL recEmailErrors.next = 0;


recEmails.RESET;
IF recEmails.FIND('-') THEN
  REPEAT
    intPos := STRPOS(recEmails.Email,'@');
    
    tmpEmailErrors.reset;
    tmpEmailErrors.SETRANGE("Wrong Domain", COPYSTR(recEmails.Email,intPos + 1));
    IF tmpEmailErrors.FIND('-') THEN BEGIN
        recEmails2 := recEmails;
        recEmails2.Email := COPYSTR(recEmails2.Email,intpos) + tmpEmailErrors."Correct Domain";
        recEmails2.MODIFY(FALSE);
    END;
UNTIL recEmails.NEXT = 0;

Nice, very nice code

.
Unfortunately I dont have any free tables (due to the company licence) but I will use an old one.
And I think it is more optimal if I go through the wrong emails and not all the emails (maybe faster).

Problem:Setfilter with *

Comments

Categories