Optionsting and SQL

DonSchillo

Hi everyone,
I searched the forum for this topic but I didn't find a proper solution.
I want to create a select statement for example on the table purchase header which delivers all fields. But in case of Navision option values I only receive the corresponding integer values. Can anyone tell me how option values are handled by the Navision SQL option and how I can get the optionstring for example for the field document type in the mentioned table. I've Navision 4.00 and SQL 2005.

Thanks a lot.

rajpatelbca

hi DonSchillo,

for Navision option data type corresponding data type of SQL server is an Integer. it means when u use SQL server option in Navision you will find only integer values will be stored in SQL Database.

if you are creating select query in SQL Database then it is not possible to get option string.

kriki

In the database-fields, the Navision-options are always encoded as integers.
The option-values are saved in the Navision-objects that in SQL (and also the Navision DB) are saved as BLOB's.
If you need to show them in a SELECT-statement, you need to program it yourself.

E.g. You might create a new table (DataPerCompany=FALSE) in Navision and maintain in there the values and captions of the option-fields.
In SQL, you can then refer to that table.

The layout of the table is something like this:
"Table ID" : integer ; the ID of the table
"Field ID" : integer : the ID of the option-field in the table
"Option ID" : integer : the ID of the option in that table (this is the internal value of the option.
"Option Value" Text30 : the value of the option (this is the option-value in Navision).

[Topic moved from Navision forum to SQL General forum]

DonSchillo

Thanks for your replays so far. I was already afraid, that I would have to go this way and create a new table.... ](*,)

pdj

If you take a look at the Mobile solution in NAV5SP1 you will find some new tables to handle this problem: "Mobile Language" and "Mobile Option Field Setup" and "Mobile Option Field Caption". You could also take a look at the report "Mobile Option Translation" for inspiration...