How to use the Navision filter commands?

[Savatage]

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.

[Zen_Bodhi]

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.

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

Thanks,

ZEN

[AdamRoue]

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.

[phenomenon]

“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
I suppose there is a logical explanation to this issue but, I cann’t find it

[Zen_Bodhi]

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.

Thank you Steve.

ZEN [:)]

[Zen_Bodhi]

“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
I suppose there is a logical explanation to this issue but, I cann’t find it

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 [:)]

[phenomenon]

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

[Zen_Bodhi]

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

Thank you phenomenon.

ZEN

[Albievh]

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

[Patty]

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

[Zen_Bodhi]

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

Thanks Albert.

Definately will try your input.

ZEN [:)]

[Zen_Bodhi]

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

Hi Patty,

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

ZEN