Option string when querrying from a SQL Server Navision DB

Mehdi.Chahed

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.

kine

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...

Mehdi.Chahed

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

Dennis_Decoene

This table does not exist, the text equivalent of the option is stored in the binary object...

DenSter

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.