Some time ago, I explained how you could use ADO to call stored procedures and stuff ... . Just another way to do stuff on SQL Server (or another database type). In this post, I will try to explain another cool feature.
What you see quite often in forums is people trying to find a way to display a view in NAV. It is actually quite well explained in the application developer's guide (w1w1adg.pdf on the Product CD) under the section "Linked Objects", but let's try to explain it in another way ... so you can choose what to use (hey, that rhymes …).
What is a view?
Good question. There is no need to explain how to display a view in NAV when you don't know what is a view, is there? Very simply said: a view is certain 'way' to look at one or multiple tables. It's actually some kind of "virtual table". It does not physically exist but it's created by a query by joining one or more tables, including only the columns that you like to see.
Why would I need a view?
You usually switch to a solution with SQL views when it's just not possible or easy enough anymore to do it with ADO, tables, C/AL code, ... anymore. Or, you just want to see the data, combined in a certain way, only a few columns, … . Several occasions that come up in my head:
Convinced? Sometimes you come across problems like that, don't you? Now, how can you solve it by using SQL Server Views?
First of all, I'm not going to give you too much information of how to write SQL. SQL knowledge is not really necessary for writing views (with query builders, you can come pretty far), although it's darn handy. What I will do is trying to build a very (VERY!) simple view, show it in a list form in NAV, and give some tips and tricks if you want 'to go beyond'. d' Accord? (pardon my French)
How to begin?
I always build my views with Microsoft SQL Server Management Studio. Just browse to your database, go to views and add a view (right mouse click) like this:
(You see above that I'm going to create a new view in my database "NAV50SP1_TEST2"). Doing this, you'll end up in the query builder - an easy-to-use tool for building your own SQL statements.
Let's start by building a very simple view on "Item Ledger Entry". I would like to know which location codes were used (in SQL-terms: a distinct on Location Code in the Item Ledger Entry). In SQL, the statement would look like this:
SELECT DISTINCT [Location Code]
FROM dbo.[CRONUS BELGIË NV$Item Ledger Entry]
I'm a little bit embarrassed because of the simplicity, but anyway ... this is what it would look like in the Query Builder (notice that I manually added the DISTINCT keyword):
Just save the View with the following name: "CRONUS BELGIË NV$Used Location Code View". As you might have guessed, I've created this view for company "CRONUS BELGIË NV", with company-specific data. If you would like this view to work for each company, you have to create a separate view for each company.
What do we have to do in NAV?
Not that difficult. As you see above, I have a view with only one field. First thing you have to do to show this view in NAV is to create a new table with one field (Location Code). Something like this:
Also change the table-property "LinkedObject" to "Yes". By this, you state that you want to "link" with an existing object (in this case a view) on SQL Server. Save the table with exactly the same name as the view, without the companyname. In our case, it's "Used Location Code View".
If you run the table, you get this:
Tip: I strongly recommend you to make all fields in a linked table "not editable" since we're working in a view here. I know it's possible to edit data in views, but this is not what you want to do on a NAV database (since there will be no validation whatsoever).
That's all what it involves to show a SQL Server View in Microsoft Dynamics NAV. Let me try to sum up the main points for your convenience. And I'll put it in bold, so it would get more attention then the rest of my blabla...:
It's all not that difficult, is it? Now, what if we want to go a little bit "beyond"? Let's include more columns from other tables and build it for all companies by unioning (not my most favorite way, but hey ... I just want to talk about views ;°) ).
First, I used the querybuilder (as I'm not that familiar with joins) for linking the Location table with my view. I added the table "Location" of the same company, dragged and dropped the field "Location Code" to "Code" (you can see the line) and checked the "name" field in the Location table. It looked like this:
You see that the SQL statement is somewhat more complicated.
To make this view available to all companies, would mean that I have to get data from the corresponding tables from all companies. Therefore, I exited the Query Builder, and started working in my own Query window in SQL Server Management Studio to create this statement:
SELECT DISTINCT 'CRONUS BELGIË NV' Companyname, dbo.[CRONUS BELGIË NV$Item Ledger Entry].[Location Code], dbo.[CRONUS BELGIË NV$Location].Name FROM dbo.[CRONUS BELGIË NV$Item Ledger Entry] INNER JOIN dbo.[CRONUS BELGIË NV$Location] ON dbo.[CRONUS BELGIË NV$Item Ledger Entry].[Location Code] = dbo.[CRONUS BELGIË NV$Location].Code UNION SELECT DISTINCT 'CRONUS BELGIQUE SA' Companyname, dbo.[CRONUS BELGIQUE SA$Item Ledger Entry].[Location Code], dbo.[CRONUS BELGIQUE SA$Location].Name FROM dbo.[CRONUS BELGIQUE SA$Item Ledger Entry] INNER JOIN dbo.[CRONUS BELGIQUE SA$Location] ON dbo.[CRONUS BELGIQUE SA$Item Ledger Entry].[Location Code] = dbo.[CRONUS BELGIQUE SA$Location].Code UNION SELECT DISTINCT 'CRONUS BELGIUM LTD_' Companyname, dbo.[CRONUS BELGIUM LTD_$Item Ledger Entry].[Location Code], dbo.[CRONUS BELGIUM LTD_$Location].Name FROM dbo.[CRONUS BELGIUM LTD_$Item Ledger Entry] INNER JOIN dbo.[CRONUS BELGIUM LTD_$Location] ON dbo.[CRONUS BELGIUM LTD_$Item Ledger Entry].[Location Code] = dbo.[CRONUS BELGIUM LTD_$Location].Code
It comes down to:
The output in SQL Server Management Studio was this:
This is exactly what I wanted. Now, let's show this in Microsoft Dynamics NAV. It's always about a similar procedure. I did the following:
Running this table gives me exactly what I wanted:
To sum up...
Well, that's a nice feature, isn't it? And it opens up nice possibilities. In this article, I was working on NAV tables, but that's not necessary. It's perfectly possible to create views on tables outside the NAV database, based on a third party application or whatever ... and show it in NAV by a view. Just beware of the conversion of data types. If you want, I can add this in another additional blog, but I didn't feel the need to add this here (as it's quite straight forward and the blog is already long enough ).
One thing I do want to give as a remark is that if you're taking a backup with the Microsoft Dynamics NAV Client and you try to restore, you'll be missing the view and the restore fails. Therefore, I would like to give you this tip: always create a script of the created view in the Management Studio and save it somewhere safe. You can create a script by right clicking on the view, then "Script View as" / "Create To" / "File..." . See below.
When you restore a database, you can use this script to restore the view.
That's it, hope you enjoyed it!
For LinkedObject=Yes, this is somewhat more difficult.
You first have to create/rename a view/table so it has the correct name (without the company).
Maybe you first have to create the new view/table before you can change the property. And after that delete the tables/views with the company name it it.