Using views in Navision SQL option

xavigepe

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.

kine

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.

xavigepe

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<

kine

I think no. Because it assume that it is connected to table and automatically call alter table (but you need alter view....) :-)

DenSter

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.

eyjolfuro

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_Brummel

@Kine

Does this linked table have to be in you license? Or is it possible to do something with a temporary record?

kine

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.

Marije_Brummel

To bad, customers will need to buy a table for every view then.

bbrown

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.

DenSter

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_Wilson

I am getting the same issue as eyjolfuro.

Using 4.0 SP2 - any solutions?

ara3n

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.

davmac1

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.

bbrown

Updatable views can involve more than one table. (The 1 table restriction was from SQL 6.5).

davmac1

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'

kriki

[Topic moved from 'NAV/Navision' forum to 'NAV Tips & Tricks' forum]

David_Singleton

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.