web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / How to get debit balan...
Microsoft Dynamics NAV (Archived)

How to get debit balance and credit from sql server in dynamIC NAV 2009

(0) ShareShare
ReportReport
Posted on by ahmed barbary 4,548

Can you help me in write view object IN SQL SERVER 2005 TO GET 
Sum of debit balance to every customer from (details customer ledger entry table)
AND
Sum of credit balance to every customer from (details customer ledger entry table) 
AND
Customer No from (customer table) 
AND
Customer Name from (customer table) 
Relation between customer table and details customer ledger entry table

I need this view object from sql to show data in dynamic nav 

i can get this from dynamic nav programming

but i need to do it from sql to get data in from based on linked object 

can any one help me

*This post is locked for comments

I have the same question (0)
  • mmv Profile Picture
    mmv 11,471 on at

    Hi,

    Both "Debit Amount" and "Credit Amount" are the summation of "Debit Amount" and "Credit Amount" from the "G/L Entry" table.

    SELECT GLAcc.[No_], SUM(GLEntry.[Debit Amount]) AS DebitAmount, SUM(GLEntry.[Credit Amount])

    FROM [dbo].[CRONUS International Ltd_$G_L Account] GLAcc

    INNER JOIN [dbo].[CRONUS International Ltd_$G_L Entry] GLEntry

    ON GLAcc.[No_] = GLEntry.[G_L Account No_]

    GROUP BY GLAcc.[No_]

    Best Regards,

    MMV

  • ahmed barbary Profile Picture
    ahmed barbary 4,548 on at

    Thank you for reply

    we get sum(debit) and sum(credit) but until now not get customer no and customer name

    how to get this from query

  • mmv Profile Picture
    mmv 11,471 on at

    Hi,

    You may use the "Source No." stored in the G/L Entry table and can be linked to the Customer table to get the Customer Name.

    Best Regards,

    MMV

  • ahmed barbary Profile Picture
    ahmed barbary 4,548 on at

    Thank you for reply

    i make this query but it give me wrong result

    SELECT     dbo.[Aramco$G_L Entry].[Debit Amount], dbo.[Aramco$G_L Entry].[Credit Amount], dbo.[Aramco$G_L Entry].[Source No_], dbo.Aramco$Customer.Name
    FROM         dbo.[Aramco$G_L Entry] INNER JOIN
                          dbo.Aramco$Customer ON dbo.[Aramco$G_L Entry]. [Source No_]=dbo.Aramco$Customer.No_

    what is wrong in this query and how to recorect

  • ahmed barbary Profile Picture
    ahmed barbary 4,548 on at

    Can any one answer fro me if possible

  • mmv Profile Picture
    mmv 11,471 on at

    Hi,

    You need to add AND dbo.[Aramco$G_L Entry].[Source Type] = 1.

    Best Regards,

    MMV

  • ahmed barbary Profile Picture
    ahmed barbary 4,548 on at

    Thank you for reply

    i make the view query as following 

    SELECT sum(GL.[Debit Amount])as Debit,sum(GL.[Credit Amount])as Credit, GL.[Source No_], CUS.Name,GL.[Source No_]
    FROM dbo.[Jeddah-Live$G_L Entry]as GL INNER JOIN
    dbo.[Jeddah-Live$Customer] AS CUS ON GL.[Source No_]=CUS.No_ AND GL.[Source Type] = 1 group by GL.[Source No_]

    when i execute query in query analyzer

    it give me no syntax error

    but it give me error 

    when execute

    Msg 8120, Level 16, State 1, Line 1
    Column 'dbo.Jeddah-Live$Customer.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  • mmv Profile Picture
    mmv 11,471 on at

    Apart from the aggregate fields in the query, all the other columns need to be in the GROUP BY clause.  In the above case, the GROUB BY must be GROUP BY GL.[Source No_], CUS.Name

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

#1
HoangNam Profile Picture

HoangNam 7

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans