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?
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
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.
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.
The item codes created are crazy (I think the person who creates it too [:D] ) 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?
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.
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.
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.”
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.
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.
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
&
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)
@
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.
I am sorry for not explaning it in detail. In other programming language I used SUBSTR,LEFTSTR or RIGHTSTR to filter some strings at particular character position.
For example : xCode:= 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
In relate to this case; I was thinking whether Navision is having similar functionality and combining it with command signs (as explained by JohnCon). I am thinking to have some thing like this:
Thanks for the compliment, but i just copy and pasted the filter help from Navision, so not much work.
I thought you where trying to find the filtering commands available to users when they filter (f7) not programically. I also was trying(but didnt do a good job of it) to explain why ‘<>-PE’ does not work when a user filters. the less than, greater than and does not equal will not work in user filters with the native database on alpha characters, only numeric. With SQL database it will work with both alpha and numeric.
However when programming a setfilter they do work.
Yes, originally I am trying to filter out but after failed/tired trying I am thinking to create dataport instead. Even though I realize is not effective and might be a lot of dataport need to be created. Is there better solution rather than the dataport?
Hopefully somebody can give feedback the mistery of the ‘<>’ sign.