Columns missing (or not shown) in some NAV Tables in SQL

najja

When I opened NAV Tables through SQL Management Studio I can see that some columns are missing or not shown. For example the 'Table Name' column of Table 405 - Change Log Entry. There are columns missing in other tables such as Windows Login, Windows Access Control etc. These are the one I noticed.

Can anyone help me to kow why this happened and what if I want those columns

Najja

najja

I thinks its because they are 'Flow Fields'.

But what if I want the flow fields values in SQL table

Najja

DenSter

The way that flowfields are implemented on SQL Server is that they are not fields, they are calculated values. If you need the value directly from SQL Server, you will have to figure out where the actual values are stored and calculate them yourself.

ara3n

In other words you need to do a left Join with the actual table.

garak

on sql you will not see flow Fields and Flow Filter.
To get the value of an flow field in sql, you must make an join, subselect or some else.

let us know, ff you need the sql example for table 405.

Regards

najja

Hello Friends, sorry for replying late.

Yes I would like to see the sql sample for Table 405. In that table the user id is recorded directly but the 'Field Name' is a flow field which is looking up information from the Virtual Table 'Field' (Table No 2000000041). Please let me see the sample so that I can also know how to query and use joins on Virtual Tables, coz there is another virtual table I am interested in (Table 'SDI - Account ID', Table No. 2000000055)

Thanks in advance

Najja

bbrown

Virtual tables exist only in the NAV client. You will not be able to join to them with SQL queries.

najja

Thanks bbrown for the info.

But how will I get the information stored as a flow field which is looking up in virtual tables. example the table 405, column 'Field Name'

Najja

ara3n

Create a table in 50 K range populate it with field table data.

najja

Thanks ara3n. Thats the solution I also had in my mind, but this is only prctical for the "Field" table. The 'SID - Account ID' is not showing any values when I created a new form using that table as data item and creatd a tabular form using wizard. Same in the case when I created report also. It is not showing the complete list of records as it is showing in the report created using 'Field' table. Instead, it is asking me to enter the SID on the Request Form and showing only the corresponding user ID.

ara3n

which virtual table are you using and trying to run?