Optionsting and SQL

DonSchilloDonSchillo Member Posts: 8
edited 2008-05-28 in SQL General
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.


  • rajpatelbcarajpatelbca Member Posts: 178
    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.
    Experience Makes Man Perfect....
    Rajesh Patel
  • krikikriki Member, Moderator Posts: 9,079
    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]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • DonSchilloDonSchillo Member Posts: 8
    Thanks for your replays so far. I was already afraid, that I would have to go this way and create a new table.... ](*,)
  • pdjpdj Member Posts: 643
    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...
Sign In or Register to comment.