How to display an SQL Server View in Microsoft Dynamics NAV

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 …Smile).

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:

  • You want to display data from another database directly into a NAV form.
  • One view of all records of all tables of the different companies of a Dynamics NAV database (of of of of...)
  • A DISTINCT view of different tables, showing the unique values of a certain field in a table, or multiple tables
  • INNER and OUTER joining tables
  • ...

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...:

  • Create a view in SQL Server Management Studio
  • Create a table in Microsoft Dynamics NAV
    • Create exactly the same fields in the same order in that table
    • Set the LinkedObject property to Yes
  • Naming:
    • If the data in your view is company-dependent:
      • Then name the view like: "YourCompanyName$YourViewName".
      • Then name the table in NAV like: "YourViewName" and leave the table "DataPerCompany" = Yes.
    • If the data in your view is company-dependent:
      • Then just give it a name without the companyname, like: "YourViewName".
      • Then name the table in NAV like: "YourViewName" and set the table "DataPerCompany"= No.

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:

  • Writing similar statements for each company (in my case 3 companies)
  • UNION these statements to get the data in the same output

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:

  • I saved the view as "Used Location" (notice I didn't use a company name).
  • In Microsoft Dynamics NAV, I created a table with following properties:
    • DataPerCompany = No
    • LinkedObject = Yes
    • Matching the fields like this:

    • Saved the table with exactly the same name as the view "Used Location"

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 Wink).

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!

   

Comment List
  • best thing is to test this ;-) - I never cared about that property..

  • Does the field requirement include that the null/not null property be the same as well (as NAV will not allow not null)?

  • I'm afraid I haven't tried this before .. so I'm not able to help you with this.

    Isn't it possible to port your Access-db to a SQL Server-db?

  • I have created a linked server to an Access database. The Access database currently resides on the same server as the SQL Server. I have created a view to the Access databse and then in NAV created a table accessing the view.  Everything works great!  I want to put the Access database on another server but am running into permission problems with the linked server.  The linked server cannot access the Access database when it is on another server. Thinking that this might be related to Access, I tried creating a linked server to an Excel spreadsheet on another server. It too is receiving the same permission errors as the Access database.  Have you ever created a linked server to an Access database or Excel spreadsheet that are phyically on different server?

  • You're welcome, Neel!

  • Thanx Waldo

    Now my report running from view

  • 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.

  • you can find it at the table properties.

    to get tot the table properties:

    in table designer: first click "Edit"/"Select Object"

    then go to properties.

    you could also select an empty line in (under the last field) and then go to properties.

  • pls tell me how to set properties of table in Nav like DataPerCompany = No

    LinkedObject = Yes

  • as far as I know .. you only need to have read permissions on the view?  Why do you think dbowner is needed?

  • Is there any way around having to be a dbo on the databases in order for views to work?

  • Are you sure you followed all the steps?  Did you create a company-specific table in NAV and therefore did you add the company in the name of the view?

    everything should be in sync perfectly before you'll be able to show it as a table in NAV.

  • Hi,

    I created view in company db and created the table in navision.i am able to run the view from sql query analysier.But while running from navision it "Tabledata <object ID> does not exit" message is showing.did anyone can help me.

    thank

  • You're welcome!

    Glad it's useful :-)

  • I want to create a bin contents with lot information, so this would probably work really well. Cheers excellent post.

Related
Recommended