Using SQL Views in Navision

Did you know that it is possible to use views in Navision?

If you often are collecting/viewing data from several tables it can be usefull to use a SQL View. Created SQL Views can then afterwards be used from Navision.

First you have to create the SQL View. This is can be done in 2 ways on the SQL Server.

One way is to use the Microsoft SQL Server Management. Here you right click on View select New where you afterwards can select tables, joins, fields etc.

A second ways is by using T-SQL. Here you can use the command CREATE VIEW.

Ex.
CREATE VIEW dbo.[MyCompany$Contact Customer] AS
SELECT [dbo].[MyCompany$Contact].[No_] as [Contact No_],
[dbo].[MyCompany$Contact].[Name] as [Contact Name],
[dbo].[MyCompany$Customer].[No_] as [Customer No_],
[dbo].[MyCompany$Customer].[Name] as [Customer Name]
FROM [dbo].[MyCompany$Customer]
INNER JOIN [dbo].[MyCompany$Contact]
ON [dbo].[MyCompany$Customer].[Contact] = [dbo].[MyCompany$Contact].[No_]


This will create a view with the fields:

Contact No_, Contact Name, Customer No_, Customer Name

Now that you got the view its time to make Navision use it. This is done by creating a table.

In the table, the fields must be called the same as in the View.

In the view created in the above example the fields was called Contact No_, Contact Name, Customer No_, Customer Name. This means that in Navision, the fields must be called:

Contact No., Contact Name, Customer No., Customer Name

On the table the property LinkedObject must be set to Yes. This will open the link to the SQL View.

Next you have to store the table – when saving the table, remember it must be given the same name as the View. Which means in our example case the table must be called Contact Customer.

That’s all – now you have created a table based on a SQL View 🙂

If you want the View to be global – then created it without the company name and when creating the table set the property DataPerCompany to No.

So in short terms, these are the steps for using SQL Views in Navision:

1. Create the View on the SQL Server

2. Save it as either [Company$View Name] (company specific) or [View Name] (global)

3. Create a new table in Navision (dont save it yet)

4. Create Fields – the Fields must be Name exactly like in the View

5. Set the Table property LinkedObject to Yes

6. Set the Table property DataPerCompany

– If it is a Company Specific view – then set it to Yes

– If it is a global view – then set it to No

7. Save the table with same Name as the View – [View Name]