Microsoft Dynamics NAV flow fields

Business Central/NAV Developers Forum
1

Good day all

I wrote some sql script to monitor overdue customer and their amounts outstanding. However I am unable to detect the correct Amount / Amount LCY value. I am querying the

[Detailed Cust_ Ledg_ Entry] table as the main source and also [Cust_ Ledger Entry] table. how can I work around this issue to get the correct value as on NAV. below is a screenshot of the values and flow field in NAV as well as my SQL script (which do not display the correct value)

pastedimage1503993306405v1.png
pastedimage1503993306405v1.png1345×257 13.2 KB

pastedimage1503994100918v2.png
pastedimage1503994100918v2.png1537×152 7 KB

SELECT

b.[Posting Date], --generally the posting date will be the same as the document date or after the document date

a.[Document Date],

a.[Due Date],

a.[Closed at Date],

b.[Entry Type],

CAST(c.[Blocked] AS NVARCHAR(5)) AS [Blocked],

CAST(b.[Document Type] AS NVARCHAR(10)) AS [Document Type],

CAST(b.[Customer No_] AS NVARCHAR(20)) AS [Customer No_],

–CAST(a.[Sell-to Customer No_] AS NVARCHAR(20)) AS [Sell-to Customer No_],

CAST(c.[Name] AS NVARCHAR(60)) [Customer Name],

CAST(b.[Document No_] AS NVARCHAR(10)) AS [Document No_],

CAST(a.[Description] AS NVARCHAR(50)) AS [Description],

CAST(b.[Source Code] AS NVARCHAR(10)) AS [Source Code],

CAST(b.[Currency Code] AS NVARCHAR(10))[Currency Code],

CAST(c.[Salesperson Code] AS NVARCHAR(10)) AS [Salesperson Code],

CAST(c.[Country_Region Code]

2

Hello, here you have a simple example which shows the Balance and Overdue Amounts.

SELECT [CRONUS AG$Customer].No_, [CRONUS AG$Customer].Name, SUM([CRONUS AG$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) AS [Amount(LCY)], sum(case when [CRONUS AG$Cust_ Ledger Entry].[Due Date]<getdate() then [CRONUS AG$Detailed Cust_ Ledg_ Entry].[Amount (LCY)] else 0 end) as [OverDueAmount(LCY)]
FROM     [CRONUS AG$Cust_ Ledger Entry] INNER JOIN
                  [CRONUS AG$Detailed Cust_ Ledg_ Entry] ON [CRONUS AG$Cust_ Ledger Entry].[Entry No_] = [CRONUS AG$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_] INNER JOIN
                  [CRONUS AG$Customer] ON [CRONUS AG$Cust_ Ledger Entry].[Customer No_] = [CRONUS AG$Customer].No_
GROUP BY [CRONUS AG$Customer].No_, [CRONUS AG$Customer].Name
HAVING ([CRONUS AG$Customer].No_ = '20000')

Hope it helps you. . .