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.
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'
