How to use the Navision filter commands?

Get our Free Newletter

Don't you have the time to be online here at DUG every day? So how do you follow what is going on in the Dynamics industry and community?
If you subscribe to the DUG Newsletter then you can relax! We will make sure that you don't miss the big news!

Check out our
Newsletter Subscription Center
page a complete list of our different newsletters.

Solution (Verified) This post has 1 verified solution | 31 Replies | 6 Followers

Male
309 Posts
4,055 Points
Joined: 2005-10-26
Last Online:
2013-10-2 2:16
Zen Bodhi Huh? [:^)] posted on 2006-8-31 11:57 | Locked

I am finding difficulties with item code filtering for multiple conditions. Each digit of our item codes is representing certain meaning. We have about 3 thousands item codes. The following are the samples of our item codes for series ABC:

ABC.01.1.11.1.001- SE          (SE = Special Edition).

ABC.01.1.11.1.001- SO         (SO = Special Order).

ABC.01.1.11.1.001- LE          (LE = Luxury Edition).

ABC.01.1.11.1.001- PE          (PE = Promotion Edition).

ABC.01.1.11.1.001- WE and etc.

 

I am struggling when I tried to help Users to filter the data with multiple conditions. For example if I want to filter all ABC items but excluding –SE or all ABC items with .001 but only with –PE and many other filtering conditions and combine with Inventory Posting Group. I never success to filter the data to get some strings in middle plus including/excluding some strings at the front/back.

Most of the time; I am filter the best that I could and I do the rest in excel.

 

So far; I am only know command sign |, *, >, <, = and &. Does any body have information/article about this filtering command?

 

Please help.

 

Thanks,

ZEN Smile

Solution (Verified) Verified Solution

Male
117 Posts
3,333 Points
Joined: 2006-5-30
Last Online:
2013-12-1 1:50
Location: Miami, FL USA
Solution (Verified) John replied on 2006-8-31 18:19 | Locked
Verified by Erik P. Ernst

Here is the breakdown of filters available.  It is important to note that  > , <,  <> only work for alpha characters when using SQL. For standard Navision Database they only work with numeric values.

When you enter criteria, you can use all the numbers and letters that you can normally use in the field. In addition, you can use some special symbols or mathematical expressions. Here are the available formats:

Symbol

Meaning

Sample Expression

Records Displayed

=

Equal to

377

Number 377

 

 

BLUE

Those with the BLUE code, for example, the BLUE warehouse code

 

 

22

A datetime: from 22-current month-current year 0:00:00 to 22-current month-current year 22:59:59

 

 

22 10

An exact datetime: 22-01-01 10:00:00

..

Interval

1100..2100

Numbers 1100 through 2100

 

 

..2500

Up to and including 2500

 

 

..12 31 00

Dates up to and including 12 31 00

 

 

P8..

Information for accounting period 8 and thereafter

 

 

..23

From the beginning of time until 23-current month-current year 23:59:59

 

 

23..

From 23-current month-current year 0:00:00 until the end of time

 

 

22..23

From 22-current month-current year 0:00:00 until 23-current month-current year 23:59:59

|

Either/or

1200|1300

Those with number 1200 or 1300

&

And

<2000&>1000

Numbers that are less than 2000 and greater than 1000.

The & sign cannot be used by itself with numbers because no record can have two numbers.

<>

Not equal to

<>0

All numbers except 0

The SQL Server Option allows you to combine this symbol with a wild card expression. For example, <>A* meaning not equal to any text that start with A.

>

Greater than

>1200

Numbers greater than 1200

>=

Greater than or equal to

>=1200

Numbers greater than or equal to 1200

<

Less than

<1200

Numbers less than 1200

<=

Less than or equal to

<=1200

Numbers less than or equal to 1200

*

An indefinite number of unknown characters

*Co*

Text that contain "Co"

 

 

*Co

Text that end with "Co"

 

 

Co*

Text that begin with "Co"

?

One unknown character

Hans?n

Text such as Hansen or Hanson

 

Calculate before rest

30|(>=10&<=20)

Those with number 30 or with a number from 10 through 20 (the result of the calculation within the parentheses)

@

Ignore case (either uppercase or lowercase allowed)

@location

Text such as LOCATION, location or Location

You can also combine the various format expressions:

5999|8100..8490

Include any records with the number 5999 or a number from the interval 8100 through 8490.

..1299|1400..

Include records with a number less than or equal to 1299 or a number equal to 1400 or greater (all numbers except 1300 through 1399).

>50&<100

Include records with numbers that are greater than 50 and less than 100 (numbers 51 through 99).

*C*&*D*

Text containing both C and D.

@*co?*

Text containing co, CO, Co, cO, such as cot, cope and incorporated.

CO, cO, Co or co must be present, followed by at least one character, but there can be an indefinite number of characters before and after these, and case is unimportant.

 

All Replies

Male
962 Posts
21,345 Points
Joined: 2003-3-16
Last Online:
2014-11-21 23:36
Location: New York
DynamicsNAVMVP
Savatage replied on 2006-8-31 14:31 | Locked

Do you use product groups for anything?

We use our product groups to help with filtering by setting the items up

for example
Product Group SE, SO, LE, PE, WE, etc

We would use the item # to get the ABC
& Product Group to get SO for example.

This way is easier than relying on trying to filter a COMPLICATED item no. alone.

Genearlly we can narrow it down in steps if needed....

Gen. Prod Posting Group - covers a broad range (ex. SHAMPOO, CONDITONER, GEL, ETC)
Product Group - Less General (ex. LOREAL, REDKEN, NEXXUS, ETC)
Item# is Exact

By Using all three or a combination we are able to filter quite well on what we're looking for.

We're around 5,500 active items.

----Oh Don't forget you can use ? in your filtering too

ABC.??.?.??.?.001- PE

 

 

Harry Ruiz  - Dynamics NAV MVP
www.BiloBeauty.com
www.AutismSpeaks.org

Male
329 Posts
4,884 Points
Joined: 2006-8-17
Last Online:
2014-11-26 18:24
Location: Fuengirola, Spain or London, U.K.
Colin Bradley replied on 2006-8-31 14:58 | Locked
Have to agree with Harry, you will probably be better to break it down into as many groups as will enable your users to quickly add filters by using the F7 + F6 (select) and build up the table filter.
To show someone is to make it look complicated, but when you have your groupings and make sure they are in the form, it is just so quick when using many thousands of items. Just watch the users when they get used to the method, they fly.

"Experience is what you get when you hoped to get money"

Male
309 Posts
4,055 Points
Joined: 2005-10-26
Last Online:
2013-10-2 2:16
Zen Bodhi replied on 2006-8-31 15:00 | Locked
Savatage:

Do you use product groups for anything?

for example
Product Group SE, SO, LE, PE, WE, etc

----Oh Don't forget you can use ? in your filtering too

ABC.??.?.??.?.001- PE

Hi Harry,

No, we don't. We are using "Inventory Posting Group" instead.

We can't use Product Group as SE, SO, LE, PE, WE and etc because it's going to be a lots of products groups and it's not effective for our business as they are varies for Distributors, Direct Shipments and Retails.

Could explain what is '?' function?

Thanks,

ZEN

 

Male
309 Posts
4,055 Points
Joined: 2005-10-26
Last Online:
2013-10-2 2:16
Zen Bodhi replied on 2006-8-31 15:13 | Locked

colingbradley:
Have to agree with Harry, you will probably be better to break it down into as many groups as will enable your users to quickly add filters by using the F7 + F6 (select) and build up the table filter.
To show someone is to make it look complicated, but when you have your groupings and make sure they are in the form, it is just so quick when using many thousands of items. Just watch the users when they get used to the method, they fly.

Hi colingbradley,

The item codes created are crazy (I think the person who creates it too Big Smile ) and the length of the digits (for spare parts) are crazy too, long like a train. You can get dizzy by looking at it.

For sure we have to follow the coding method from the factory but actually I have simplified the coding by utilizing "Vendor Item Code" even though only for certain Inventory Posting Group.

The problem for me is the item codes were already setup and created. I am for sure will think about Harry's suggestion but can my questions be fulfilled?

ZEN

 

Male
272 Posts
3,275 Points
Joined: 2001-8-25
Last Online:
2010-9-29 0:43
Location: Livermore, California
themave replied on 2006-8-31 15:16 | Locked

I think a better solution would be to use item variants.

then you would have one item number and all the variants your wanted, you can filter and report on variants also.

For your situation above

try  for the first    <>ABC*-SE 

and for the second ABC*.001-PE

David NAV 4.0 NON-SP1 NAV2.0C WITH ADVANCED DISTRIBUTION
Male
329 Posts
4,884 Points
Joined: 2006-8-17
Last Online:
2014-11-26 18:24
Location: Fuengirola, Spain or London, U.K.
Colin Bradley replied on 2006-8-31 15:28 | Locked
I did have one client that I managed to adapt their work practice by using the Description 2 as the original long and complex code and creating a new short code as the item No.

May be worth thinking about. One benefit of this is you keep the original code if anyone wants to use it but you have the benefit on a more logical short code.

If it were me, I would be creating new tables and group codes as well as getting the owner of the data to consider the option above.

"Experience is what you get when you hoped to get money"

Male
648 Posts
7,325 Points
Joined: 2000-10-30
Last Online:
2012-5-24 23:07
Location: Vancouver, BC
TonyH replied on 2006-8-31 15:32 | Locked
Zen Bodhi:

ABC.01.1.11.1.001- SE          (SE = Special Edition).

ABC.01.1.11.1.001- SO         (SO = Special Order).

ABC.01.1.11.1.001- LE          (LE = Luxury Edition).

ABC.01.1.11.1.001- PE          (PE = Promotion Edition).

ABC.01.1.11.1.001- WE and etc.

 

 

Have you thought about adding a few more fields. Such as a Item Edition field? That stores the end chars of your Item No. such as SE, or SO. You can knock some code up that will update the "Item Edition" when you insert\Modify a item record. You could do this for as many breakdowns as your Item No.'s include. "ABC" "1001"

 

To get the best performance on filtering you would need to add keys, at which point some people will gasp!, but if your using SQL and set them up correctly you should not see a major performance decrease. If your using Native then it would be based to keep keys to a minimum, but if you must then ensure you optimise the table, that way the pages will be in sync.

 

T

Male
272 Posts
3,275 Points
Joined: 2001-8-25
Last Online:
2010-9-29 0:43
Location: Livermore, California
themave replied on 2006-8-31 15:46 | Locked

Not to harp on it too much, but this is basically the diffinition of item variants, and requires no coding or modification, and will be that way after an upgrade also.

"In this table, you can specify variants of items. This is useful if you have a large number of almost identical items that vary only in color, for example. Instead of setting up each variant as a separate item, you can set up one item and then specify the various colors as variants of the item."

David NAV 4.0 NON-SP1 NAV2.0C WITH ADVANCED DISTRIBUTION
Male
11,177 Posts
180,778 Points
Joined: 2000-11-3
Last Online:
2014-11-26 23:49
Location: UK
DynamicsNAVMVP
Moderator
AdamRoue replied on 2006-8-31 16:48 | Locked

Could explain what is '?' function

It means "any character" but unlike * it has to match the exact characters. So
Hats
Hits
Huts
Would all be returned filtering H?ts

So to expand on this embarrassing example if the list included Hatstand and you filteres H* all four would be returned but H?ts gives you the ability to remove Hatstand from the returned list.

“Any fool can know. The point is to understand.”
Albert Einstein

Male
309 Posts
4,055 Points
Joined: 2005-10-26
Last Online:
2013-10-2 2:16
Zen Bodhi replied on 2006-8-31 16:53 | Locked
themave:

I think a better solution would be to use item variants.

then you would have one item number and all the variants your wanted, you can filter and report on variants also.

For your situation above

try  for the first    <>ABC*-SE 

and for the second ABC*.001-PE

I did try *-SE; it works but it doesn't work for <>*-SE or <>*'-SE' or '<>*-SE'.

This is also not working ABC*.001* & <>ABC*.001-SE.

I don't know why <> doesn't work on any filtering. Please try it in your system.

ZEN

Male
309 Posts
4,055 Points
Joined: 2005-10-26
Last Online:
2013-10-2 2:16
Zen Bodhi replied on 2006-8-31 17:04 | Locked
TonyH:

Have you thought about adding a few more fields. Such as a Item Edition field? That stores the end chars of your Item No. such as SE, or SO. You can knock some code up that will update the "Item Edition" when you insert\Modify a item record. You could do this for as many breakdowns as your Item No.'s include. "ABC" "1001"

 

To get the best performance on filtering you would need to add keys, at which point some people will gasp!, but if your using SQL and set them up correctly you should not see a major performance decrease. If your using Native then it would be based to keep keys to a minimum, but if you must then ensure you optimise the table, that way the pages will be in sync.

 

Hi Tony,

 

Actually Yes but it's not going to work (I think) because each Inventory Posting Group is represent one product line. Each product line has their own coding arrangement as they are totally different category. It's becomes complicated when filtering Item Codes plus Inventory Posting Group with multiple conditions.

 

ZEN

Male
309 Posts
4,055 Points
Joined: 2005-10-26
Last Online:
2013-10-2 2:16
Zen Bodhi replied on 2006-8-31 17:07 | Locked
sbweaver:

Could explain what is '?' function

It means "any character" but unlike * it has to match the exact characters. So
Hats
Hits
Huts
Would all be returned filtering H?ts

So to expand on this embarrassing example if the list included Hatstand and you filteres H* all four would be returned but H?ts gives you the ability to remove Hatstand from the returned list.

Steve,

I see. This is new for me.

Is it possible to use some kind of SUBSTR, LEFTSTR, or RIGHTSTR to filter the middle part?

ZEN

Male
117 Posts
3,333 Points
Joined: 2006-5-30
Last Online:
2013-12-1 1:50
Location: Miami, FL USA
Solution (Verified) John replied on 2006-8-31 18:19 | Locked
Verified by Erik P. Ernst

Here is the breakdown of filters available.  It is important to note that  > , <,  <> only work for alpha characters when using SQL. For standard Navision Database they only work with numeric values.

When you enter criteria, you can use all the numbers and letters that you can normally use in the field. In addition, you can use some special symbols or mathematical expressions. Here are the available formats:

Symbol

Meaning

Sample Expression

Records Displayed

=

Equal to

377

Number 377

 

 

BLUE

Those with the BLUE code, for example, the BLUE warehouse code

 

 

22

A datetime: from 22-current month-current year 0:00:00 to 22-current month-current year 22:59:59

 

 

22 10

An exact datetime: 22-01-01 10:00:00

..

Interval

1100..2100

Numbers 1100 through 2100

 

 

..2500

Up to and including 2500

 

 

..12 31 00

Dates up to and including 12 31 00

 

 

P8..

Information for accounting period 8 and thereafter

 

 

..23

From the beginning of time until 23-current month-current year 23:59:59

 

 

23..

From 23-current month-current year 0:00:00 until the end of time

 

 

22..23

From 22-current month-current year 0:00:00 until 23-current month-current year 23:59:59

|

Either/or

1200|1300

Those with number 1200 or 1300

&

And

<2000&>1000

Numbers that are less than 2000 and greater than 1000.

The & sign cannot be used by itself with numbers because no record can have two numbers.

<>

Not equal to

<>0

All numbers except 0

The SQL Server Option allows you to combine this symbol with a wild card expression. For example, <>A* meaning not equal to any text that start with A.

>

Greater than

>1200

Numbers greater than 1200

>=

Greater than or equal to

>=1200

Numbers greater than or equal to 1200

<

Less than

<1200

Numbers less than 1200

<=

Less than or equal to

<=1200

Numbers less than or equal to 1200

*

An indefinite number of unknown characters

*Co*

Text that contain "Co"

 

 

*Co

Text that end with "Co"

 

 

Co*

Text that begin with "Co"

?

One unknown character

Hans?n

Text such as Hansen or Hanson

 

Calculate before rest

30|(>=10&<=20)

Those with number 30 or with a number from 10 through 20 (the result of the calculation within the parentheses)

@

Ignore case (either uppercase or lowercase allowed)

@location

Text such as LOCATION, location or Location

You can also combine the various format expressions:

5999|8100..8490

Include any records with the number 5999 or a number from the interval 8100 through 8490.

..1299|1400..

Include records with a number less than or equal to 1299 or a number equal to 1400 or greater (all numbers except 1300 through 1399).

>50&<100

Include records with numbers that are greater than 50 and less than 100 (numbers 51 through 99).

*C*&*D*

Text containing both C and D.

@*co?*

Text containing co, CO, Co, cO, such as cot, cope and incorporated.

CO, cO, Co or co must be present, followed by at least one character, but there can be an indefinite number of characters before and after these, and case is unimportant.

 
Male
11,177 Posts
180,778 Points
Joined: 2000-11-3
Last Online:
2014-11-26 23:49
Location: UK
DynamicsNAVMVP
Moderator
AdamRoue replied on 2006-9-1 1:47 | Locked
I am afraid I do not really understand the reply! Can I have an example if the above list does not answer your question!

“Any fool can know. The point is to understand.”
Albert Einstein

Page 1 of 3 (32 items) 1 2 3 Next > | Get this RSS feed | Bookmark and Share