Problem when filtering on an option field in a page

Business Central/NAV Developers Forum
1

Hi everyone,

I have an option field in a custom table taking the following values: ,1a,1b,1c,1d,2,3e,3f,3g,3h,3i,4
When I filter on it in the corresponding page, I get strange results:

Filter on "1a" --> correct results
Filter on "1b" --> entries with "2"
Filter on "1c" --> correct results
Filter on "1d" --> entries with "4"
Filter on "2" --> correct results
Filter on "3e" --> correct results
Filter on "3f" --> correct results
Filter on "3g" --> correct results
Filter on "3h" --> correct results
Filter on "3i" --> correct results
Filter on "4" --> error message : '21' is not an option.The existing options are ,1a,1b,1c,1d,2,3e,3f,3g,3h,3i,4

(21 is the index of “4” in the option string)

I can’t understand why it is working for some values and not for the rest…Any ideas?

Thanks in advance :mrgreen:

2

Can you show the code you’re using to set the filters to the option field?

3

Hi,

How do you filter in your coed? or do you filter only on the page?

Going back to the Table how the option values are written?

4

No code, I am using the std nav filtering on the page…

5

I have the same problem whenfiltering in the table…

I checked the optionstring in the table and in the page, they are the same…

6

You should also know how the option fields really work, unless you know it already.

Internally in SQL there is no such thing as an option field. When the data is stored in the database, it only contains the number it has on the option list, starting with 0. So if you’re setting filters from the code, then all you need is really an integer value to set the filter.

Like SETRANGE(“Option Field”,1); You can also write it like SETRANGE(“Option Field”,“Option Field”::“1”); - then when compiling the code, it will “translate” the “1” to the text used in the option setting.

Remember that you must also be 100% sure that both the actual option value string and the option caption ML strings are synchronized. If you somehow have forgotten to update the Option Captions, after you changed the actual options, then it will give you problems.

If you have no code to show, then please show a screen shot of how you are setting the filters in the page. After you have checked the option captions.

7

I checked the option captions, they are the same.

By the way, I checked the table, the problem is there too when I try to filter on the field…

I am using the NAV filtering on the page:
image.jpg

8

You asked in the NAV Developer Forum, so you got a “developer answer”.

So if you click lookup on the fields (on same line that says Where), doesn’t it show you new field?

For the field also to be used from the first filter line, then it needs to be one of the fields in the page.

9

The field is one of the fields of the page [:)]

I choose my field (on same line that says Where) and then I choose a value for the filter(“1a” or “1b” or any other value).

The result is sometimes wrong (as I explained in my first post).

For example, when I choose “1b”, the page shows values entries with “2”, not with “1b”.

How can that be explained?

10

restart the nav service and the windows client, then try again.

if you get the same behavior then filter

  • using apostrophs: ‘4’ instead 4, ‘2’ instead of 2, ‘1d’, …

  • or 4…4, 2…2, 1d…1d

ranges: 1a…1d

i tested with nav 2015. in first run same behavior as you describe. maybe a problem with metadata.

then i restarted windows client, dev. env., nav service.

after that … all ok.