Is there an "Option-Field-Translation-Table"?

pupricht

Me again...

...still migrating our old interface routines (ODBC) from Navision 4.x (native database) to NAV 2009 (MSSQL).

All off the old interfaces are using the ODBC drive with the option "OptionFieldType" set to "text"
(The "Identifiers" optin is set to "a-z,A-Z,0-9,_" which explains the "additional" underlines in the following sample statements)

Therefore selecting records in the old environment from lets say the "Cust_ Ledger Entry" table may look like this:

SELECT fields 
FROM Cust__Ledger_Entry
WHERE Document_Type = 'Invoice'

It's the option string < ,Payment,Invoice,Credit Memo,Finance Charge Memo,Reminder,Refund> defining the mapping

Payment=1,
Invoice=2
....and so on...


The new MSSQL-Environment does not allow to choose between OptionFieldType Interger/Text.
Therefore I have to rewrite the above statement as

select * from [dbo].[Cust_ Ledger Entry] where [Document Type] = 2 

So far so good. Having access to the designer it's easy to find out what the integer value is.

I am wondering (remember I don't have the NAV 2009 application but only a connection via SQLNCLI) if there exists any table to "translate" option field integers into strings and/or vice versa?????

Something that would enable me to grab

select IntegerValue, TextValue from [TranslationTable] where [FieldReference] = 'Document Type'

Thank you


Peter

einsTeIn.NET

I think there is no table like this. But you can create it on your own by using RecRef and FieldRef. FieldRef provides function OPTIONCAPTION and you can seperate it by SELECTSTR. You just have to take care about empty and doubled option values.