Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / using sql query trying...
Small and medium business | Business Central, N...
Suggested answer

using sql query trying to find related invoice no for a credit memo

(0) ShareShare
ReportReport
Posted on by dvcoop 30

I am using SQL queries into Nav 2017 to pull data out for PowerBI reports. I'm trying to find the related records for credit memos.

I can clearly see in NAV on the About This Page - Applied Customer Entries (Page: Applied Customer Entries (61)) that it is using the Source Table Cust. Ledger Entry (21) and there is a Field for Document Type = Invoice and Document No = 322233 (the invoice the credit memo is applied to), but when I go to the SQL table [...Cust. Ledger Entry] neither the Document Type and Document No fields are there.

Well not quite true of the Document No, that is in the table but it is the Credit Memo Document No not the Invoice No.

Below is a screen shot of the About This Page, but can't find those two fields in the SQL Table [...Cust. Ledger Entry]

I found a few examples of this were the About This Table Fields is listing fields that are not in the actual SQL table, clearly there is a relationship that i'm not seeing.

applied-cust-entries.jpg

It is even stranger, when I query the above table with the statement below I get zero results


SELECT *
FROM [dbo].[xxx$Cust_ Ledger Entry]
WHERE [Applies-to Doc_ No_] = '322233'

  • Suggested answer
    dvcoop Profile Picture
    dvcoop 30 on at
    RE: using sql query trying to find related invoice no for a credit memo

    Okay, figured out what the issue is.

    Unless at the time of the credit memo creation the user enters which invoice it applies to, there will be no entries in the [Applies-to Doc_ No_] field for the Credit Memo Customer Ledger entry.

    What I found is that in the Invoice related Customer Ledger record there is a [Closed by Entry No_] that points back to the Credit Memo Customer Ledger entry.

    So you basically just left join the Cust_ Ledge Entry] table to itself referencing the [Closed by Entry No_]

    SELECT

    *

    FROM [dbo].[xxx$Cust_ Ledger Entry] as CL

    LEFT JOIN [dbo].[xxx$Cust_ Ledger Entry] as CB ON CL.[Entry No_]=CB.[Closed by Entry No_]

    WHERE CL.[Document Type] = '3'

    So the above query will give you all the credit memos and the records they relate back too.

  • RockwithNav Profile Picture
    RockwithNav 6,562 on at
    RE: using sql query trying to find related invoice no for a credit memo

    Transaction no will help you out!!

  • Gearoid Profile Picture
    Gearoid on at
    RE: using sql query trying to find related invoice no for a credit memo

    Hi,

    The following works for me in the Standard Cronus (DE) database:

    SELECT *

    FROM [dbo].[CRONUS AG$Cust_ Ledger Entry]

    WHERE [Document No_] = '103005'

    and [Document Type] = 2 -- 2=Invoice

    Hope that helps!

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans