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:
However, if you would run SELECT query, for the same table from SQL Management Studio, you will get less understandable results:
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.