How do I display Dynamics NAV table option field text values in SQL Server

Question

Sometimes during Dynamics NAV development, it is helpful to take a quick look at the data using SQL Server. But because any fields of type option are an enumeration, all you get in SQL Server is the numeric value. I needed a quick and dirty way to get the option text values instead.

Minus. Because first of all you don’t need to look at sql and second you can always see option string through dev env. — Mak Sim, Sep 29 '18 at 20:10
I know you can just look at the file properties in the dev environment but sometimes it is helpful to link several tables with inner joins in sql (e.g. Routing Header, Routing Lines, Routing Tool, Routing Comment, Routing Personnel and Routing Quality Measure). For a quick and dirty look, it is easier to sometimes just use sql. In that case, it is helpful to have the option strings available to you. I am NOT suggesting this for any kind of production scenario, just testing and debugging. — j2associates, Oct 01 '18 at 16:38

score 1 · Answer 1 · answered Nov 01 '18 at 01:31

From within NAV you can read the OPTIONSTRING property of a FieldReference. This is a comma separated string. A job can be scheduled that will loop through all of the tables (Object virtual table filtered on table) by number, find the options strings and add them to a table. Then in a query you can find the option text value for the Table, Field No, and Field Value.

RecRef.OPEN(TableNo);
FOR i := 1 TO RecRef.FIELDCOUNT DO BEGIN
  FieldRef := RecRef.FIELDINDEX(i);
  IF FORMAT(FieldRef.TYPE) = 'Option' THEN BEGIN
    optionstring := FieldRef.OPTIONSTRING;
    c := NumberofOptions(optionstring);
    FOR o := 1 TO c DO BEGIN
      OptionsTable.INIT;
      OptionsTable."Table No" := TableNo;
      OptionsTable."Field No" := FieldRef.NUMBER;
      OptionsTable."Option Value" := o-1;
      OptionsTable."Option Text" := SELECTSTR(o, optionstring);
      OptionsTable."Field Name" := FieldRef.NAME;
      IF NOT OptionsTable.INSERT THEN OptionsTable.DELETE;
    END;
  END;
END;

j2associates · Answer 2 · 2018-09-29T18:29:53.627

0

To make this a little less painful, I created a macro enabled Excel file that parses the Dynamics NAV field option string into a Sql Server T-Sql Case statement. It provides a horizontal or vertical case statement and uses the field name as the column alias in Sql Server. Enjoy...

Here is a link to the Excel file Excel File

edited Sep 29 '18 at 18:29

answered Sep 29 '18 at 18:06

j2associates

1,115
10
19

score 0 · Answer 3 · answered Dec 11 '18 at 10:10

I often get this problem. I created a table with option values (int) and names (string). The primary key is code, value. So you can use it also to resolve magicnumbers from other systems. Then you can easy join this table:

select Type, i.[Option] [Option Name]
from Object o
join [xxx$IntegerToOption] i on i.Code = 'OBJEKT TYP' and i.Integer = o.Type
order by o.Name

Output:

Type    Option Name
5   Codeunit
2   Form
1   Table
2   Form
2   Form
1   Table
2   Form
5   Codeunit
3   Report

How do I display Dynamics NAV table option field text values in SQL Server

3 Answers3