The last couple of days I came across some posts that triggered my memory on using SQL views in NAV. After ages having done little to none with this I decided to pick up this gauntlet and spend some time diving in my past experiences. As little as they were - for sure I am not a SQL query expert! - they might be of value to others.
I could recall that a SQL view could be linked to a NAV table object and actually that's all that is needed. Have a look at this example:
Let's have a look at the existing views in NAV (using NAV 2009 SP1). Seems to be a long list with a lot SIFT related matters. Skip that and look at the last ones:
We'll use the dbo.Database File view. Now in NAV we create a table with the following fields:
On of the prerequisites to link a NAV table to a SQL view is that the fields do match with respect to naming and data typing. But do not worry, if they do not match you will be prompted by the system like this:
Now before saving the table definition two things more need to be set: the table properties DataPerCompany (= No) and LinkedObject (=Yes).
OK, save (and close) the table definition and be sure to use exact the same name as the view:
Ready and run the table from the Object Designer and see yourself:
In this we way we have a new table in NAV which can be used like any other table to build forms on, etc.
So now we know how to make SQL views available, we can take it a little bit further. For example build a SQL view using typical SQL features like joins, like this one:
SELECT dbo.[CRONUS International Ltd_$Customer].Name, dbo.[CRONUS International Ltd_$Detailed Cust_ Ledg_ Entry].[Posting Date], dbo.[CRONUS International Ltd_$Detailed Cust_ Ledg_ Entry].AmountFROM dbo.[CRONUS International Ltd_$Customer] INNER JOIN dbo.[CRONUS International Ltd_$Detailed Cust_ Ledg_ Entry] ON dbo.[CRONUS International Ltd_$Customer].No_ = dbo.[CRONUS International Ltd_$Detailed Cust_ Ledg_ Entry].[Customer No_]
This query is combining data from the Customer and Detailed Cust. Ledg. Entry tables.
[Note that the '.' used in NAV object names has been replaced by '_'.]
Ready and run:
Note that you even can edit the data through this table, but you might ask yourself if that is wanted ...
In this way you could even access data in other databases.
... there is more to it so you might want to read Using Linked Objects on MSDN or in the technical online help for NAV.
... only today (December 19, 2009) I stumbled across Waldo's blog on the same topic. Good to read that for sure.
Actually, I just got by this issue by removing:
So where is the Table that you create within the development environment located in SQL? The reason is that I'm trying to use this process within an extension 2.0. It was working fine and then I had to make a change to the view and the table and now it is complaining with this:
What's the issue? You can still apply filters. OK, as you do not have any keys defined the filtering might be slow, but you can apply them just as always (using F7 etc.).
It's a good article, but if i want to filter data dynamically on the form on run time just like we use to do it on standard forms using F7 or Ctrl F7, how can i do it with tables showing data from views.
You're doing a great job, Luc! And thanks for linking me :-)