mibuso.com

Microsoft Business Solutions online community
It is currently Sun May 19, 2013 3:38 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 18 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Using views in Navision SQL option
PostPosted: Tue Feb 08, 2005 2:25 pm 
Offline

Joined: Thu Feb 03, 2005 8:00 pm
Posts: 115
Location: Barcelona
Country: Spain (es)
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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Feb 08, 2005 3:34 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Thu Oct 16, 2003 8:50 am
Posts: 12265
Location: Brno
Country: Czech Republic (cz)
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.


Top
 Profile E-mail WWW  
 
 Post subject:
PostPosted: Wed Feb 09, 2005 7:11 pm 
Offline

Joined: Thu Feb 03, 2005 8:00 pm
Posts: 115
Location: Barcelona
Country: Spain (es)
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<


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 10, 2005 8:23 am 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Thu Oct 16, 2003 8:50 am
Posts: 12265
Location: Brno
Country: Czech Republic (cz)
I think no. Because it assume that it is connected to table and automatically call alter table (but you need alter view....) :-)


Top
 Profile E-mail WWW  
 
 Post subject:
PostPosted: Thu Feb 10, 2005 6:26 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7931
Location: Howell, MI
Country: United States (us)
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.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject:
PostPosted: Thu Sep 22, 2005 5:52 pm 
Offline

Joined: Thu Sep 22, 2005 5:42 pm
Posts: 1
Location: Iceland
Country: Iceland (is)
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Sep 22, 2005 7:14 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Tue Jul 19, 2005 4:49 pm
Posts: 3832
Location: Olst
Country: Netherlands (nl)
@Kine

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

_________________
Mark Brummel | Freelance Dynamics NAV (Navision) Developer
Author of the book : Microsoft Dynamics NAV 2009 Application Design

MY BLOG : http://www.brummelds.com


Top
 Profile E-mail WWW  
 
 Post subject:
PostPosted: Thu Sep 22, 2005 7:34 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Thu Oct 16, 2003 8:50 am
Posts: 12265
Location: Brno
Country: Czech Republic (cz)
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.


Top
 Profile E-mail WWW  
 
 Post subject:
PostPosted: Thu Sep 22, 2005 8:12 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Tue Jul 19, 2005 4:49 pm
Posts: 3832
Location: Olst
Country: Netherlands (nl)
To bad, customers will need to buy a table for every view then. :(

_________________
Mark Brummel | Freelance Dynamics NAV (Navision) Developer
Author of the book : Microsoft Dynamics NAV 2009 Application Design

MY BLOG : http://www.brummelds.com


Top
 Profile E-mail WWW  
 
 Post subject:
PostPosted: Fri Sep 23, 2005 1:44 am 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2573
Location: MA
Country: United States (us)
Quote:
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.


Top
 Profile E-mail  
 
 Post subject:
PostPosted: Fri Sep 23, 2005 2:25 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7931
Location: Howell, MI
Country: United States (us)
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.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject:
PostPosted: Fri Aug 18, 2006 3:03 pm 
Offline

Joined: Mon Mar 12, 2001 8:01 am
Posts: 24
Location: York
Country: United Kingdom (uk)
I am getting the same issue as eyjolfuro.

Using 4.0 SP2 - any solutions?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Aug 18, 2006 3:36 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 8699
Location: 3rd rock from sun
Country: United States (us)
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. :)

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject:
PostPosted: Fri Aug 18, 2006 6:47 pm 
Offline

Joined: Fri Aug 06, 2004 9:30 pm
Posts: 899
Location: Richardson
Country: United States (us)
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sat Aug 19, 2006 4:19 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2573
Location: MA
Country: United States (us)
Updatable views can involve more than one table. (The 1 table restriction was from SQL 6.5).

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 18 posts ]  Go to page 1, 2  Next

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: