If you have ever tried to read data from Microsoft Dynamics NAV SQL tables then you quickly found out that Option type fields are stored in the table as integer values. Unfortunately, it is difficult for users to relate the option representation with the integer value. NAV performs the translation of the integer to the appropriate option value for display within the application and doesn’t offer much in the way of translation outside of the application.
Generally I have come across cases where people have built the translation directly into their source, as this SQL example:
Document_Type = CASE [Document Type] WHEN 0 THEN 'Quote' WHEN 1 THEN 'Order' WHEN 2 THEN 'Invoice' WHEN 3 THEN 'Credit Memo' WHEN 4 THEN 'Forecast Order' WHEN 5 THEN 'Return Order' ELSE '' END
This may solve the issue of frustration however, what happens if options v alues are changed or added? Under this scenario the query would need to be recoded. Depending on the number of queries and their integration this can become tedious and time ($) consuming task.
So, what’s the alternative? Wouldn’t it be nice if you could dynamically read the options value? There really isn’t a dynamic way to externally translate the values outside of NAV however; you can read the values from a table. Before thinking that this requires a lot of data entry; NAV does allow for you to read these values from within the application.
The solution that I use is to dynamically populate (and update regularly) a table that contains the Table, Field and Option value information. This option allows for the selection of the option value based on table and field number. If option values are added, removed or changed they are reflected in the table and there isn’t a need to make any change to any queries.
I have attached to this post the text file for the objects used >>HERE<<.