mibuso.com
Home› SQL General
Options

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

najjanajja Member Posts: 67
edited 2008-04-24 in SQL General
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

Comments

  • Options
    najjanajja Member Posts: 67
    I thinks its because they are 'Flow Fields'.

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

    Najja
  • Options
    DenSterDenSter Member Posts: 8,304
    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.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    ara3nara3n Member Posts: 9,255
    In other words you need to do a left Join with the actual table.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    garakgarak Member Posts: 3,263
    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
    Do you make it right, it works too!
  • Options
    najjanajja Member Posts: 67
    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
  • Options
    bbrownbbrown Member Posts: 3,268
    Virtual tables exist only in the NAV client. You will not be able to join to them with SQL queries.
    There are no bugs - only undocumented features.
  • Options
    najjanajja Member Posts: 67
    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
  • Options
    ara3nara3n Member Posts: 9,255
    Create a table in 50 K range populate it with field table data.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    najjanajja Member Posts: 67
    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.
  • Options
    ara3nara3n Member Posts: 9,255
    which virtual table are you using and trying to run?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
Sign In or Register to comment.