Most of the data in NAV is stored in tables, but you can also display data from other sources. This recipe will explain how to show data from a SQL View in NAV.
Open SQL Server Management Studio.
Select your database and open a new query window.
Execute the following code:
CREATE VIEW [Customer Ledger View] AS SELECT "Customer No_","Initial Entry Due Date","Posting Date", COUNT_BIG(*) "$Cnt", SUM("Amount") "SUM$Amount", SUM("Amount (LCY)") "SUM$Amount (LCY)" FROM "CRONUS USA, Inc_$Detailed Cust_ Ledg_ Entry" GROUP BY "Customer No_", "Initial Entry Due Date", "Posting Date"
Create a new table from Object Designer.
Add the following fields to the table:
Field Name
Data Type
Length
Customer No_
Code
20
Initial Entry Due Date
Date
Posting Date
Date
$Cnt
BigInteger
SUM$Amount
Decimal
SUM$Amount (LCY)
Set the following properties on the table.
Property
Value
DataPerCompany
No
LinkedObject
Yes
LinkedInTransaction...