Option string when querrying from a SQL Server Navision DB

Mehdi.Chahed
Mehdi.Chahed Member Posts: 14
edited 2005-07-14 in Navision Attain
Hi,

In order to make several Data Exports & Publication, I planned to use SQL Views, DTS & Planned jobs on SQL Server.
The principle is OK!
The problem comes from Option string fields: when exported (or querried) they are shown as integer. For end users, the information is not meeningfull ](*,)
DTS could be used to solve the problem but it's not an intelligent way [-X

Clearly I need a hand.
Mehdi CHAHED
Email: mehdi.chahed@dufry.com.tn
DUFRY TUNISIA

Comments

  • kine
    kine Member Posts: 12,562
    I think that there are four ways:

    1) In Navision table you add new text column (field) and fill it with option text (in OnValidate or somewhere to keep it updated) - keep in mind the multilanguage (if you switch language you will have another text)

    2) In DTS change it to the text

    3) In presentation layer (on the website) have function which will translate the number into the option string...

    4) use C/Front to read the values...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Mehdi.Chahed
    Mehdi.Chahed Member Posts: 14
    I agree for all slutions you proposed.
    In fact, I've allready tried 3 of them (not CFRONT). But I stil think that it's not the best way to procced: as I'm dealing with a Navision Retail DB of 90Gbites, I must think twice before adding any field especially when it has to be filled even by a DTS Task.
    Somewhere on the SQL Server DB, a ghost table is there. Hidden by SP3a :evil: - Stupid thoughts 8-[

    Anyway, the best solution is to create/use separate Tables (Field Id - Field Value - Text equivalency), join them when proceeding...
    \:D/ That's the right way !
    But I still think that this table exist
    Mehdi CHAHED
    Email: mehdi.chahed@dufry.com.tn
    DUFRY TUNISIA
  • Dennis_Decoene
    Dennis_Decoene Member Posts: 123
    This table does not exist, the text equivalent of the option is stored in the binary object...
  • DenSter
    DenSter Member Posts: 8,307
    The text values of an option field are stored in a table property. Maybe you can access the table property somewhere in the object table, but I have no clue how to do that. Wouldn't recommend messing around with it either.
    Daniel Rimmelzwaan
    RIS Plus, LLC

Categories