Filter in report by post code with letters or without.

knaabisknaabis Member Posts: 37
edited 2013-06-11 in Navision Attain
Should I create a filter by post code, but sometimes from beginning is letters, but sometimes not.
How to use in filter only numbers?
I need to find out Customers by post code (for example) NO-2100 and 5010.
For me usable is only numbers like 2100 and 5010 - how to "delete" this "NO-"?

p.s. Sometimes post code will be longer as 4 numbers...
For example:
1) NL-6538 SX
2) SE-113 27
3)...

Comments

  • ara3nara3n Member Posts: 9,255
    It looks like post codes is not a good way to categorize your customers. You need a new field.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • knaabisknaabis Member Posts: 37
    I know, but it's the only way to find out result.
    So, what regarding my question? :)
  • ara3nara3n Member Posts: 9,255
    Do you have all the combination of how they appear?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • camecame Member Posts: 12
    Hi,

    maybe you can delete the chars with function "NewString := DELCHR(String[, Where][, Which])" temporaly
    http://msdn.microsoft.com/en-us/library/dd354973.aspx

    An example gcodPostCode := DELCHR("rec.Post Code","="ABCDEFGHIJKLMNOPQRSTUVWXYZ-.,;/");
    After this the variable gcodPostCode has only numbers. You can add more delchars of course.

    You can catch the error with if ...
    if (DELCHR("rec.Post Code","="ABCDEFGHIJKLMNOPQRSTUVWXYZ-.,;/"))<> "" then
    do ....
    else
    do ...

    or

    create a temp table of your record, fill it and use it before the loop starts.

    or

    check your Post Code with ...
    if rec."Post Code" in ["A","B","C","D","E","F","G","H","I","J" ......] then
    // check if the "Post Code" has any char in the []
    else
    //all with any char
  • knaabisknaabis Member Posts: 37
    UK-AL2 1BB
    UK-NP19 4PU
    RO-061072
    NL-4693 RE
    DK-4600
    DE-48499
    KO-135-820
    SE-141 75
    ES-07011
  • ara3nara3n Member Posts: 9,255
    If you are trying to find customer just by number. You can do it this way.
    NumberFilter := 123;
    Customer.reset;
    Customer.setfilter("Post Code",'*'+format(Numberfilter) + '*');
    if Customer.findset then repeat
    until Customer.next = 0;
    


    If you have dashes in your number then you need to follow the advise given by the above poster and create a temp table.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    As far as I remember Navision also understands 'NO-2100'..'NO-4200'. It should filter correctly.


    If you can only filter on numbers then normally with this notation addint the country code filter should work to avoid filtering on customers in other countries.
  • knaabisknaabis Member Posts: 37
    came wrote:
    Hi,

    maybe you can delete the chars with function "NewString := DELCHR(String[, Where][, Which])" temporaly
    http://msdn.microsoft.com/en-us/library/dd354973.aspx

    An example gcodPostCode := DELCHR("rec.Post Code","="ABCDEFGHIJKLMNOPQRSTUVWXYZ-.,;/");
    After this the variable gcodPostCode has only numbers. You can add more delchars of course.

    You can catch the error with if ...
    if (DELCHR("rec.Post Code","="ABCDEFGHIJKLMNOPQRSTUVWXYZ-.,;/"))<> "" then
    do ....
    else
    do ...

    or

    create a temp table of your record, fill it and use it before the loop starts.

    or

    check your Post Code with ...
    if rec."Post Code" in ["A","B","C","D","E","F","G","H","I","J" ......] then
    // check if the "Post Code" has any char in the []
    else
    //all with any char

    Thanks, BUT right code is:

    gcodPostCode := DELCHR("rec.Post Code",'<>','ABCDEFGHIJKLMNOPQRSTUVWXYZ-.,;/');

    :)
  • camecame Member Posts: 12
    "knaabis wrote:
    Thanks, BUT right code is:

    gcodPostCode := DELCHR("rec.Post Code",'<>','ABCDEFGHIJKLMNOPQRSTUVWXYZ-.,;/');

    :)

    Sorry, i don't try this out :wink:
    If this was the solution, maybe you can mark the topic as fix.
    Thanks.

    came
  • AlbertvhAlbertvh Member Posts: 516
    This will also work
    gcodPostCode := DELCHR(rec."Post Code",'=',DELCHR(rec."Post Code",'=','0123456789'));
    

    This will remove all the non numeric characters.
Sign In or Register to comment.