How do I filter on a post code

We have a shipping agent service cost table and each record has a FROM Post Code and a TO PostCode
For example

No FROM TO charge
1 AA01 AZ99 100
2 ZA01 ZA99 200

Now logically I know that if I pass a post code of AB9 then I want to return record 1 and the charge of 100. However, how do I do that within a filter ?
I know that the issue is the mixture of Text and Numbers in the code field. I don't know how to get round it....

Any help is gladly accepted



  • RemcoRauschRemcoRausch Posts: 6Member
    In your particular example you can just use
    Form Code <=AB9
    To Code >=AB9
  • HarikiranHarikiran Posts: 33Member
    You can use SetFilter and just use the condition what Remco mentioned. Are u aware? Setfilter can hold these type of strings to filter.
    Harikiran BM | Dynamics NAV technical consultant
  • lubostlubost Posts: 374Member
    ... and change field names, because you NAV can somewhere use "TO" as keyword.
  • Keeper75ukKeeper75uk Posts: 2Member
    Hi Thanks for the replies.
    On the field naming convention, these are already defined the customers tables so cant change them, and as long as to TO is encapsulated within " then then I'm sure im ok .. ie "To Post Code" and "From Post Code"
    However the crux of the issue remains. If the Data passed is nice and neat then we are ok. For example in the UK I live in the post code of SY4 and not SY04. My issue is that the Web Service passing the data (out of my control) to my function may well pass SY04. As the post code field is CODE SY4 and SY04 are treated very differently although to the human eye we know SY4 is the same as SY04.

    I was hoping there was something I was missing in the filtration but it looks like I will have to manipulate the text passed to the function (to remove the errant 0) before running the actual functionality.
  • vaprogvaprog Posts: 745Member
    There's no reason you can't change a field name (or any name) in NAV. The only object you have to manually change ist the local one (i.e. the table, in this case). All other objects catch up on the new name when you edit or export them. You don't even need to recompile them in order for them to properly work with the new name. This works for all compiled objects of any type.

    If you even wish to avoid having to correct the names in the local object, you may use a translation file to rename it (Development Environment - Menu - Tools - Translate - Export/Import).

    As to your post code problem: There is essentially nothing you can do about bad data. After all, you are running this on a computer (sic!) not a guessing machine or a "do what I mean"-machine (those have yet to be invented).

    Of course you can try to sanitize the data before you use it, in case you find an algorithm to do so.
Sign In or Register to comment.