Using views in Navision SQL option

xavigepexavigepe Member Posts: 185
edited 2009-08-28 in NAV Tips & Tricks
Hi everybody.
I have a Navision database in SQL called XXX. There I have created a view and I want to get the results of that view in a form in Navision. How can I do it (if possible)?.

Thanks.

Comments

  • kinekine Member Posts: 12,562
    Create view in the Navision DB connected to this databse and view and create table in Navision with same name, set DataPerCompany = No and LinkedObject=Yes.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • xavigepexavigepe Member Posts: 185
    Thanks as lot but I have one more problem. When I try to change the key of that table in Navision a message "The table xxx does not exist in the database" appears. Is it possible to change the keys of tables related to views in SQL?


    Thanks for your help. [-o<
  • kinekine Member Posts: 12,562
    I think no. Because it assume that it is connected to table and automatically call alter table (but you need alter view....) :-)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • DenSterDenSter Member Posts: 8,304
    That's because it is not a table in navision. It is a datasource that is linked in the SQL database through a view, that you link to in Navision. When you change the table design in navision the link will stop working.
  • eyjolfuroeyjolfuro Member Posts: 1
    Hi. I am trying to do the same thing as
    xavigepe. I have been successful in creating a view on the
    sql server, and seing the data appear in a table/form in navision.
    The problem I'm having is that when data shown in the table exceeds
    a few screens, the form/table doesn't update properly when I use page down and
    sometimes shows the same entry on the whole screen until I scroll through all the entries
    on the screen, then the entries update with the correct values.

    I have tried to see the data in both the table and a list form but the update problem
    is the same

    Has anybody solved this problem?

    Any help would be greatly appreciated.
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    @Kine

    Does this linked table have to be in you license? Or is it possible to do something with a temporary record?
  • kinekine Member Posts: 12,562
    I think that you need to have this table in your license, because without this, you are not able to design it and read it. If you use it as temporary, I think that it will be empty, because no queries to DB backend will be sent.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    To bad, customers will need to buy a table for every view then. :(
  • bbrownbbrown Member Posts: 3,268
    When I try to change the key of that table in Navision a message "The table xxx does not exist in the database" appears. Is it possible to change the keys of tables related to views in SQL


    Turn off 'MaintainSQLIndex' when creating the key in Navision.
    There are no bugs - only undocumented features.
  • DenSterDenSter Member Posts: 8,304
    You need to be careful though, and keep the primary key the same as the one for the original SQL table, or you will get unexpected results when writing to those tables.

    For instance, I have a view into a SQL table that has an integer field ID as its primary key, a table that stores item information. I was playing around with this view, and changed the primary key in Navision to the Item number, to make it mimic Navision behavior.

    The issue here is that in the SQL table, you can have the same Item number more than once, as long as the ID field has a different value. Now when I do a GET on an Item number in Navision, and modify one f the fields, it does so in all records with the item number in the sql table, probably because it set a filter on the number or something. It is really weird when you look at records on a Navision form too, when the key you created in Navisino doesn't really exist in the SQL table.

    Be careful and test your view thoroughly.
  • Ian_WilsonIan_Wilson Member Posts: 25
    I am getting the same issue as eyjolfuro.

    Using 4.0 SP2 - any solutions?
  • ara3nara3n Member Posts: 9,255
    Ian Wilson. Have you tried on open form to store the records into a temporary record and display the temporary record. It will be a little slow on openinng the form, but aftewards it will fly like an eagle. :)
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • davmac1davmac1 Member Posts: 1,283
    I like to use views for Navision reports and table mode forms with no updates. It dramatically simplifies reporting and sorting.
    Modifying SQL Server views - I believe you can only change fields in one table. I would recommend using SQL Server views without allowing updates.
  • bbrownbbrown Member Posts: 3,268
    Updatable views can involve more than one table. (The 1 table restriction was from SQL 6.5).
    There are no bugs - only undocumented features.
  • davmac1davmac1 Member Posts: 1,283
    In SQL Server 2000 the following was added (I don't think Navision would like this.):

    Attaching triggers to a view
    An instead of trigger can be defined on a view. Using this, we can now update views when more than one underlying table is updated. In this simple example we will create a view on two related tables and insert both tables via one insert command on the view:

    Code Example 1:
    create table test1 (descr varchar(32))
    go
    insert test1
    select 'Quick demo of' union
    select 'instead of triggers'
    go
    create trigger tr_test1_io on test1 instead of update as
    BEGIN
    select descr as 'Inserted Columns' from inserted
    select descr as 'Deleted Columns' from deleted
    select descr as 'Table test1' from test1
    END -- trigger def
    go
    update test1 set descr = NULL
    go
    drop table test1
    Code Example 2:
    create table country (
    countryID int identity,
    countryName varchar(32),
    }
    go
    create table city (
    cityID int identity ,
    CityName varchar(32),
    countryID int,
    )
    go

    create view v_geography as
    select countryName, cityName
    from country inner join city on country.countryID = city.countryID
    go

    create trigger tr_v_geography_io on v_geography instead of insert as
    BEGIN
    insert country (countryName)
    select distinct inserted.countryName
    from inserted left join country on inserted.countryName = country.countryName
    where country.countryName IS NULL /*** Exclude countries already in the table ***/

    insert city (cityName, countryID)
    select distinct inserted.cityName, country.countryID
    from inserted inner join country on inserted.countryName = country.countryName
    left join city on inserted.cityName = city.cityName
    where city.cityName IS NULL /*** Exclude cities already in the table ***/

    END -- trigger def
    go

    insert v_geography
    select 'England', 'London' UNION
    select 'England', 'Manchester' UNION
    select 'Japan', 'Tokyo' UNION
    select 'Japan', 'Osaka' UNION
    select 'USA', 'Washington DC' UNION
    select 'USA', 'New York'
  • krikikriki Member, Moderator Posts: 9,094
    [Topic moved from 'NAV/Navision' forum to 'NAV Tips & Tricks' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • David_SingletonDavid_Singleton Member Posts: 5,479
    kriki wrote:
    [Topic moved from 'NAV/Navision' forum to 'NAV Tips & Tricks' forum]
    ummped.

    Grrrrrrrrrrrrrrrrrrrrrrr here was me writing a response to help someone out only to find that its a 4 year old thread that was bumped.

    I know it makes sense to move the threads, but the continual bumping is VERY annoying.
    David Singleton
Sign In or Register to comment.