OptionString Property in Dynamics NAV (and outside)

Once in a while, you will need to write a quick report in SQL Reporting Services, or in Excel, while reading data from Dynamics NAV. And, usually, that is quite easy, if you are familiar with database structure and if you know in which fields and in which tables you can find the data you need. However, when you do this for the first time, you will definitely encounter a “mismatch” between how data looks in SQL and Dynamics NAV – a field with type of “Option”.

For example, if  you would try to write a report based on sales header table, you might want to use field Document Type. If you would run the table 36 “Sales Header” from Object Designer, you will get readable “Document Type” values:
Field Type Option - Document Type in Sales Header in Dynamics NAV

However, if you would run SELECT query, for the same table from SQL Management Studio, you will get less understandable results:
Field Type Option - Document Type in Sales Header in SQL

Instead of proper “Document Values” you will get plain numbers. But do you know why?

Firstly, we need to understand what an “Option” field is in Dynamics NAV:

An option field is defined with an option string, which is a comma-separated list of strings representing each valid value of the field. This string is used when a field of type Option is formatted and its value is converted into a string.

For example, the Option field “Color” is defined with the option string “Red,Green,Blue”. Valid values of the field are then 0, 1, and 2, with 0 representing “Red” and so on. When the “Color” field is formatted, 0 is converted into the string “Red”, 1 into “Green”, and 2 into “Blue”.

How to find Option String Values in Dynamics NAV?
If you a writing a report using SQL Reporting Services and you need to find what your integer values mean (for example, what 0,1,2,3,4,5 means in Document Type field), you will need to:
1. Go to Object Designer (click SHIFT+F12)
2. Find your table in question (in our example, it is table 36 “Sales Header”) and click “Design” button (or click ALT+D)
3. Find your field in question (in our example, it is field 1 “Document Type”) and click View > Properties (or click SHIFT+F4)
4. Find property OptionString and check the Value column (Quote,Order,Invoice,Credit Memo,Blanket Order,Return Order).
5. Map OptionString values to Integer numbers starting from 0 (0 = Quote, 1 = Order, 2 = Invoice, 3 = Credit Memo, 4 = Blanket Order, 5 = Return Order).

Understanding how OptionString values are being displayed in SQL Server gives us a better chance of writing meaningful reports. There is a very nice example written of doing that in the Related Links section below.

Related Links

This entry was posted in Dynamics NAV / Navision, SQL and tagged , , , , , . Bookmark the permalink.

1 Response to OptionString Property in Dynamics NAV (and outside)

  1. Tom H says:

    Tomas, thank you so much for this very useful guide on how to find option string numbers. With this information I am now able to run SQL queries with correct values for Dynamics NAV Option lookup fields.

    Do you know where in the database NAV stores these field settings? It would be nice to look up the table option field metadata for option strings without needing to use the Dynamics NAV Development Environment. Ideally I would like to use a SQL query against NAV metadata directly in the database to find this information. Or perhaps NAV does not save this information to the database and only stores it in compiled NAV objects/files on the server? I’m a SQL DBA with extensive query and reporting experience, but NAV is new to me.

Leave a Reply

Your email address will not be published. Required fields are marked *