mibuso.com

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.

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.

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.

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

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.

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.

@Kine

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

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.

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

Turn off 'MaintainSQLIndex' when creating the key in Navision.

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.

I am getting the same issue as eyjolfuro.

Using 4.0 SP2 - any solutions?

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.

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.

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