How-to: Use SQL Views in NAV

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:

Example #1 

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.

Example #2

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].Amount
FROM  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 ‘_’.]

1. Create a new view on SQL Server with the query shown above
2. Save the view as View_Detailed_Cust_Ledg_Entry
3. In NAV create a new table with fields
   Field Name  Data Type Length
   Name            Text           30
   Posting Date Date
   Amount          Decimal
4. Set the following table properties:
   DataPerCompany = No
   LinkedObject = Yes
5. Save the table as View_Detailed_Cust_Ledg_Entry (take whatever id that suites)
   Note: you really have to make sure that the name of the NAV table is equal to the name of the view you want to link it to!

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.

And further…

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