SetFilter without case sensitivity

marvinqvist

Hi,

Imagine that I have a table (temp) filled with customer numbers and emailaddresses. I would like to find customers with matching emails (not the same customer). I do this like so...

Customer.SETFILTER(Customer."No.",'<>%1',Temp."No.");
Customer.SETFILTER(Customer."E-Mail",'=%1',Temp."E-Mail");
IF NOT Customer.ISEMPTY THEN BEGIN
...
END;

This code works just perfect when det cases are equal, but it is not catching e.g. "mail@mail.com" = "Mail@mail.com". It seems to be case sensitive, but how can I prevent case sensitivity to this code, so that this will be caught.?? I have already tried to replace the '=%1' with '@%1' but this doesn't work either.

Please help!

Thanks

Procat

Try:
Customer.SETFILTER(Customer."E-Mail",'%1','@'+Temp."E-Mail");

Not sure it there's a problem with it being emails already containing "@".

marvinqvist

Oh ye.. I have also been trying with that model, but it still doesn't work.

Any other ideas??

Is it because that "E-mail" already has a @-sign??

What can I do??

vijay_g

marvinqvist wrote:

I have a table (temp) filled with customer numbers and emailaddresses.

why not you skip this character in E_Mail Id field at the time of inserting even though you are using it temperory.

jannestig

doesn't your query have to resemble the normal NAV filter ?

Take the contact table for example EMail field if i want to filter for 'st' in any case combination i would use *@st*

I assume it would work in your code as well

so perhaps Customer.SETFILTER(Customer."E-Mail",'%1','*@*'+Temp."E-Mail"); would work better or the correct coding for it at least for the wildcard filters

kriki

try:

Customer.SETFILTER("E-Mail",'@' + CONVERTSTR(Temp."E-Mail",'@','?'));

marvinqvist

vijay_g >>
Well it is not that temporary. I need the data in the table for a while. It is not a one-time stunt, it has to work in the future.

jannestig >>
I don't get how the wildcard-notion should work?? It has to be the exact same e-mail string, just ignoring upper/lowercases.

kriki >>
Your example catches, but it is still case sensitive.

I am really lost. Any ideas?? Why is it so difficult to match to e-mailaddresses??

jannestig

I am not a developer but basically you need the * wildcard character to incude the @ symbol where st in my example is replaced by the email address.

How you corretly express this may be different, following a debug to see how standard nav handles it with that filtering expression would give you the answer i imagine ?

marvinqvist

jannestig>>

Still not getting the point.

In your example you are asking for any e-mail which contains "st" in both upper and lowercases. That is not what I need. I need the exact same emailaddress. I have been trying to filter for: '@'+"E-Mail" (@ is ignore upper/lowercases) but this doesn't work. It might be because of the @-sign already is within the "E-mail"-field. I need some way to exclude the @-sign when matching I guess.

Still no other suggestions??

marvinqvist

It is easy to test, also for non-programmers...

Open the customer card, make sure that two customers have two matching e-mailadresses... e.g. Test@test.com and test@test.com. (one with a uppercase character)

Now, when standing in the e-mail field press F7 (filter) and try the following...:

@test@test.com
@test?test.com
test@test.com
test?test.com

None of the above examples work, they all catch none or at most one of the two customers...

What is the correct notation, to catch both customers??

Sog

What version are you on?
In NAV 5 the filter @test?test.com works and finds both customers.

marvinqvist

Hmm... something is very strange... I am working on a Nav2009

I just went to a 5.0 version, and you are right it worked. The same for a NAV2009sp1.

I guess there must have been made some adjustments to this version I am working on. The correct solution should then be as kriki mentioned...

Customer.SETFILTER("E-Mail",'@' + CONVERTSTR(Temp."E-Mail",'@','?'));

I will get back when I have testet and discovered the mystery.

kriki

Actually, I tested this on a CRONUS 2009SP1:

SETFILTER("e-mail",'london.Candoxy.storage.campus?cronuscorp.net');

and if finds the email "london.candoxy.storage.campus@cronuscorp.net" (see .Candoxy. vs .candoxy.).

What are the settings in File=>Database=Alter=>tab collation of your DB?

marvinqvist

Hi Kriki,

Thanks for your respons.

I had it set to SQL-sorting, just changes it to Windows-sorting. Is there any issues to be aware of when changing this on a running database??

It almost works now... i am catching the test@test.com/Test@test.com with "@test?test.com", but something is still fishy.

In Denmark we have a character "å/Å", it is the contraction of two a's. So it is very normal to have in an email as double a's. E.g. "aarhus@denmark.dk" (Århus is a danish town), but it is not possible to filter on e-mails with double a's... WHY??? I tried to filter even without the @-sign just to get one of two customers, but nothing appears. Also in other versions I can't get this to work.

Please test my example: "aarhus@denmark.dk".

Think I am going crazy!

kriki

I tried aarhus@denmark.dk on a 5.0SP1 and I can filter it using "aarhus?denmark.dk" (even if I change some chars to uppercase in the filter).
I have Windows Collation, "Afrikaans, Basque, Catalan, Dutch, English, Faeroese, German, Indonesian, Italian, Portuguese", "accentive sensitive"

marvinqvist

Hi all,

I have tried og testet almost all posibilities now. And I still insist, something is wrong

The funny thing is that as kriki said it works when set as windows collation "Afrikaans, Basque, Catalan, Dutch, English, Faeroese, German, Indonesian, Italian, Portuguese", "accentive sensitive". But in Denmark we use the Collation "Danish, Norwegian", and when this is on it does not work.

E.g. the two emails: aarhus@Denmark.dk and aarhus@denmark.dk is not visible when filtering for "@aarhus?denmark.dk", not even the one with same cases.

I do not know if you outside of Denmark/Norway have this Collation option, but it would be great if anyone who has can test. Or if anyone can come up with som kind of filter workaround. I guess some exception character must exist. I am really lost. It is important that it works, and it has to be solved via filtering.

/MHQ

jannestig

I do remember a good posting about this issue exactly for double letter filtering in names.

I am pretty sure it had your resolution there, will update if i find it

jannestig

Here it is

viewtopic.php?f=23&t=20420&hilit=danish

marvinqvist

Well the post is almost on the exactly same issue. But it is 6 years old and it does not leave a solution. Is this really still a problem?

My problem is only on cases. I am able to find both "aarhus@denmark.dk" and "aarhus@Denmark.dk" (by replacing the @ with ?), but not at the same time!

The other post leaves a note that searching for å (double a i danish) should find both, I guess by typing "@århus?denmark.dk" but this is still not the case.

The problem is still:

Customer.SETFILTER(Customer."No.",'<>%1',Temp."No.");
Customer.SETFILTER(Customer."E-Mail",'=%1',Temp."E-Mail");
IF NOT Customer.ISEMPTY THEN BEGIN
...
END;

..and here I am not able to convert aa -> å, even not temporary. Can it really be true that I have to convert every single e-mailadresse to lowercases and then afterwards do the match as statet above.?? This will really consume ressources!

/MHQ