
How to create a new Dynamics NAV User from PowerShell
Current Windows User must have admin permission to the SQL Server

I am using two examples to explain the logical background in regard to retrieving values of the Flow Fields.
I am using two examples to explain the logical background in regard to retrieving values of the Flow Fields.
Example 1 – SQL View for Customer Table
In Customer (No 18 of Dynamics NAV DB) Table, ‘Balance’ and the ‘Balance LCY’ fields have been defined as Flow Field, therefore during the run time it will pull records from the Detail Customer Ledger Entry table (No 379 of Dynamic NAV DB ) to show the ‘Balance’ and ‘Balance LCY’ on the Customer Card. When you create a SQL view for customer table, you will also have to apply the same principle. In other words, you have to join ‘Detail Customer Ledger Entry’ with Customer table to retrieve the corresponding values.
USE [Demo Database NAV (6-0)]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_Customer]
AS
SELECT TOP (100) PERCENT dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_, dbo.[CRONUS Australia Pty_ Ltd_$Customer].Name,
SUM(dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].Amount) AS AMOUNT,
SUM(dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) AS AMOUNT_LCY
FROM dbo.[CRONUS Australia Pty_ Ltd_$Customer] LEFT OUTER JOIN
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry] ON
dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_ = dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Customer No_]
GROUP BY dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_, dbo.[CRONUS Australia Pty_ Ltd_$Customer].Name
ORDER BY dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_
GO
Please note that I am using Dynamics NAV 2009 SP1 Asutralian & New Zealand version for my examples. Therefore when you test this example, you may be required to replace the compnay name ‘CRONUS Australia Pty_ Ltd’ with ‘CRONUS International Pvt Ltd.’ As appropriately. Further if you’re testing this scripts with a different database file, instead of ‘Demo Database NAV (6-0’, please replace the database name correctly.
Example 2 – SQL View for Customer Ledger Entry Table
In the second example, I am creating a SQL view for Customer Ledger Entry Table. Again, you know that ‘Amount’ and ‘Remaining Amount’ are defined as Flow Fields by placing a look up to the Detail Customer Ledger Entry Table. Therefore in the SQL View definition, you would notice that a JOIN command is used to establish the link between the Customer r Ledger Entry (18) & Detail Customer Ledger Entry Table (379). More importantly, JOIN command has been used for two times in order for calculating ‘Amount’ and ‘Remanning Amount’ respectively. In the first case, Detail Customer Leger Entry is joined only based on the Customer Ledger Entry No. field, whereas in the second case Detail Customer Leger Entry is joined based on both Entry No & Entry Type fields.
USE [Demo Database NAV (6-0)]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_Cust_LedgerEntry]
AS
SELECT dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Customer No_],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Posting Date], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document Type],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].Description,
SUM(dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].Amount) AS Amount, SUM([CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry_1].Amount)
AS Remaning_Amount
FROM dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry] LEFT OUTER JOIN
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry] ON
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_] = dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_] AND
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Entry Type] = 1 LEFT OUTER JOIN
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry] AS [CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry_1] ON
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_] = [CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry_1].[Cust_ Ledger Entry No_]
GROUP BY dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Customer No_],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Posting Date], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document Type],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].Description
GO
When you need to create SQL views for other ledger entry tables, you will have to follow the same principles as explained above. For example, in the case of creating a SQL view for Item or Item Ledger Entry Tables, you will have to link either ‘Item Leger Entry’ or ‘Value Entry’ or both as appropriately depending on the required fields available in the SQL view.

Premanath Kumarage
Author

Current Windows User must have admin permission to the SQL Server

By default which database to connect is stored in a config file called ClientUserSettings.Config file.

Table locking is a normal but often confusing reality in any multi-user ERP system. In Microsoft Dynamics NAV, locking issues are especially common in manufacturing and service-based organisations, particularly during month-end processing.
Start here
A working session, not a sales call. Bring the process your current system fights you on, and we will tell you plainly what it would take to fix it.
Newsletter
Stay ahead with Microsoft Business Applications.
Insights, implementation advice and practical ideas to help your business get more from Microsoft technology.
By subscribing, you acknowledge MicroCloud 360’s Privacy Policy.