We run Navision 3.6 on a SQL 2000 server. I want to query from SQL server the Change Log Entry table. Is there a way to display the ‘Field Name’ for each of the ‘Field No_’? ‘Field No_’ is a field that is part of the SQL table, however ‘Field Name’ is not. I cannot find any SQL table that cross-references the Filed No to the Field Name. Any ideas…? Thanks! SoPo
Hi, If you have a look at the flowfield definition for “Field Name” it looks like this: Lookup(Field.“Field Caption” WHERE (TableNo=FIELD(Table No.),No.=FIELD(Field No.))) So I guess you have to make a view that joins the table with the table Field.
The problem is that this joint has to be done in SQL. I could not identify a SQL table that stores field numbers and captions.
Well, you have to look for a table named “Field”.
Well, there isn’t one! That’s the problem!
You’re right - sorry about the confusion… The table fields is a virtual table and apparently it doesn’t exist in SQL. Fieldnames obviously are stored another way, perhaps in syscolumns? I’m sure Robert C. has an answer
The entire table definition (fields, keys and everything) is stored in the [BLOB reference] field of the Object table, for each table. This is of course an internal Navision format and nothing to do with SQL system tables. The “Field” table is generated by Navision, iterating through every table definition in Object. Although not impossible to crack the format externally, there is no public API to do it, and Navision does not expose the structure as, for example, SQL extended properties, although there have been several requests to do so. Sorry.
What we did for a client was to create two new fields in change log entry and added code in navision to fill in the fields.
It wold probably have been better to create a new table, and copy the virtual FIELD table to this one that is then visible from SQL.
Sorry, I should have explained more… THe Change log is a ver transaction intesive table, and you really should not do anything that will add more load to it, other wise you will start to see a lot of table locking. By creating a new table containing field names, you only need to run the routine once, so t will not have any afffect on your normal day to day performance. Adding the two fields to the Log table is a big mistake, and you will start to see issues.