How to use the Navision filter commands?

Business Central/NAV User Forum
21

Perhaps we can start again - there have been a few good suggestions in helping you find the items your looking for. Either using Item Varients to Using more fields to help group(ie. Product Groups + Posting Groups, Custom Fields, etc.)

You know how to group your items better than us.

22

I’ll try my best to absord all the suggestion.

Thanks,

ZEN

23

Hi Zen

ABC.01.11.001-SE; if want to get .01.11; I used LEFTSTR(4,6). The characters that I want to get is located at the column 4 and I captured 6 characters, which is .01.11

To my knowledge there is no commenad like this, you would need to filter in the format ???.01.11* to get what you want I believe.

24

“Filter = Something*” will work.
“Filter = <>Something*” will not work.

I think that has to do with SQL as language. In SQL there is a LIKE clause in WHERE command that is most probably used when you do filtering of that kind.

LIKE is used in same manner as first example.
SELECT * FROM Item WHERE “No.” LIKE ‘Something%’.

This would be translation of first filter. Also you can set NOT LIKE in such a way and get the second filter applied.

But, in Nav, you cann’t.
Even if you set Filter = <>* you’ll get whole recordset again :slight_smile:
I suppose there is a logical explanation to this issue but, I cann’t find it :slight_smile:

25

Thank you Steve.

ZEN [:)]

26

Hi phenomenon,

The problem is we have native database.

I am agree with you, there must be an explanation on why <> sign is not working for filtering.

ZEN [:)]

27

What I ment is SQL as a Structured Query Language (not MSSQL). This “<> feature” is same in both solutions, native and mssql.

28

Thank you phenomenon.

ZEN

29

Hi Zen.

You could use the COPYSTR command to achive this

eg FiletrText := ‘ABC’ + COPYSTR(“No.”,4,6) + COPYSTR(“No.”,STRLEN(“No.”) - 3);

Regards

Albert

30

Hi Zen,

you already had a lot of suggestions here and now you know that this <>A* doesn’t work.

I would just add a way (manual and not so easy) to make the users able to filter the list of items. This are the steps in order to have ABC* excluding *-SE:

  1. filter ABC* and mark the records (select all and press CTRL+F1)

2 remove the filter

3 filter *SE and unmark the records (select all and press CTRL+F1 twice if there are also unmarked record, once if they are all marked)

4 remove the filter

5 select View - Marked Only

31

Thanks Albert.

Definately will try your input.

ZEN [:)]

32

Hi Patty,

This is new for me. Thanks for sharing the knowledge.

ZEN