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
  • Not that I know of, actually.  I never had that issue, allthough we are linking with other (exotic) systems as well... .

    Select permission on both tables (and the view) should theoretically be enough .. sorry.

  • Hi Waldo

    thanks for the detailed article! is there any documentation about the necessary sql permissions for a linkedobject-scenario?

    setup via the admin-account works fine. but as a standard user tries to access the view via the NAV Client (Classic 2009 SP1), which is set in the NAV database and references to another database on the same server, NAV crashes with a permission error -

    even if the user is granted db_owner permissions on both databases.

  • Great blog, Waldo. I only just found it after I had written mine (dynamicsuser.net/.../using-sql-views-in-nav.aspx) as I apparently did not succeed to find yours at that time.

    Have added reference to your blog post to mine.

  • Thank you for this information.  I created the SQL view and then created the table and all worked great.  I went back to the SQL view and added another field and then went to the same NAV table and added the field.  I get the error "The x table does not exist as the required object name of x in this Database"

    Is it possible to add a new field to the same view & table or do you have to delete the NAV Table and start all over?

    thanks in advance. Angela

  • Sorry man, I really don't get what you mean.  Can you give me more details?

  • Hi guy can u actually tell me how to hold a SQL server for many user from different network system to access the database and to work on some database alone.

  • Thanks a lot! Great Blog! It really help me!

  • Smile

    I use that post always as a reference myself... .  That way, my blog is my tiny little knowledge base Wink.

  • BTW : talking about ADO: thanks for your blog on it. It was very helpful to get started!

  • Nice blog!

    I do know of the possibility from the beginning, but because of the problems involved, I try to avoid them.

    The way I would do it, is to create a new DB (NOT NAV!). In this DB I define all the views (and maybe also tables if needed). In NAV I use ADO to read the tables/views in that other DB. This way the NAV-DB remains 'clean'.

    Also if the views are on another DB, I do this. This way, with ADO I just need to access that DB and not multiple others. This way also the other databases remain 'clean'.

    If I need the view on a form, I read the data through ADO in a temptable and then show the temptable. Of course, this is only possible if there are not too many records and the view-query is fast enough (here indexed views can help).

  • Thanks for the comments, guys.

    We use it for the same things... .  Throwing in some ADO, SP's, Views ... combination of  all... .  But, as mentioned before, I always try to build it 'as generic as possible'.  I mean, if a new company is created .. the new "views" have to be created as well.

    It's not only powerful, it's also interesting.  At the moment, we're designing a solution where we use SQL Triggers.  Just a design phase, but interesting enough to explore the possibilities...

  • The "cool" thing about this, is that it has actually been possible to do at least since version 3.60, so it's not new. But I think not many developers know this. We used it to display values of other SQL databases directly in Navision.

  • Hi Eric!

    Great BLOG! In my projects I oftenly use Views & Linked Objects, e.g. to easily display JOINED or AGGREGATED data, which would be a real "pain" to do in C/SIDE ...

    Regarding "performance", indeed, it could be quite poor - depending on the View. But since SQL 2005 Views could be INDEXED to speed up performance - as shown with VSIFT in NAV 5.0 SP1!

    Using Views & Linked Objects gives us LOTS of possibilities: you could create a View on a "Linked Server", which e.g. could be an Excel sheet (ODBC source) or a remote Database etc.. For example, you could link a - separate - DB with historical data; thus using historical info TOGETHER with current NAV data ... or transfer/migrate master data directly from an Excel sheet (which would be eveb faster when doing a INSERT / SELECT in SQL) ... and much more!

    SQL Server IS POWERFUL! And besides MSADO, Views & Linked Objects are a GREAT option to use this POWER in NAV! (IMHO)

    Best regards,

    Jörg

  • You're right, sorry.  You should create the compay first before creating the view, but then, you should delete (or rename) the company before you restore the backup.  Anyway ... different workarounds, one conclusion: no one straight way to do it.

Related
Recommended