please help to convert flow field to SQL

Business Central/NAV Developers Forum
1

I need to build a Trial balance in SSRS from NAV.

There is a ‘Balance at Date’ flow field I could probably use for Opening Balances.

Here is this flow field code:
Sum(“G/L Entry”.Amount WHERE (G/L Account No.=FIELD(No.),G/L Account No.=FIELD(FILTER(Totaling))
,Business Unit Code=FIELD(Business Unit Filter)
,Global Dimension 1 Code=FIELD(Global Dimension 1 Filter)
,Global Dimension 2 Code=FIELD(Global Dimension 2 Filter)
,Posting Date=FIELD(UPPERLIMIT(Date Filter))))

Please, advise how this flow field could look in SQL.
It seems as it’s just a SUM of the account Amount up to the date. I’ll probably go with this.
Thanks

2

Flow fields are not avaialbe in Sql becuase it is logical /calculated field

You need to write query to achieve the Flow field result.

3

Hi

Flow fields will not be available in SQL it will be populated at run time

First get the Acct no, Posting Date, Business unit code, Glbl Dim 1 and Glbl Dim 2 as input from user (you can get range of input too )

Sum(“G/L Entry”.Amount)from

(select Amount from “G/L Entry” where

Global Dimension 1 Code = ‘User Input’ and

Global Dimension 2 Code = ‘User Input’ and

Business Unit Code = ‘user Input’ and

Posting Date between ‘User Input Date1’ and ‘User Input Date 2’ and

“G/L Entry”.G/L Account No. in

(Select “No.” from “G/L Account” where

“G/L Account”.Account Type = ‘total’ and

“No.” in (‘User Input Acct no’)))

as i dont have sql in my system the syntax may be different please adjust

Thanks

Jerome Marshal. J

4

Thank you so much, Marshal, for your response.

It’s actually by departments and accounts our user want to see grouping with totals. I’d also need to show running totals for detailed (by entry) account balances grouped by departments and accounts. So, I’d need to show the running totals for balances. Do you have any advice how I could do it with the NAV data? I’ll probably need to post to SQL forum with the running balances question.

Thanks

5

Hi Alex

use " GROUP BY G/L Entry.Department Code, G/L Entry.G/L Account No. " at end so that this will give you the sum by group.

Thanks

Jerome Marshal. J