Search code examples
navisiondynamics-nav

Dimensions to [$Detailed Cust_ Ledg_ Entry] in Dynamics Nav

Is there any table with dimensions to the table [CompanyName$Detailed Cust_ Ledg_ Entry] in Dynamics-Nav. If so what is the table name or number and what the keys are to join tables. I would like to get [Service Type] to each transaction.

I use SQL Server to get the data.

Solution

  • In standard NAV, there are no separate dimension sets for Detailed Cust. Ledger Entries. Depending on the type of the entry, you can get dimensions from:

    1. Initial Cust. Ledger Entry: "Cust. Ledger Entry No." = "Customer Ledger Entry"."Entry No."
    2. Applied Cust. Ledger Entry: "Applied Cust. Ledger Entry No." = "Customer Ledger Entry"."Entry No."

    If the dimension you are looking for is a global one, there are two fields "Initial Entry Global Dim. 1", "Initial Entry Global Dim. 2" right in the table, which, as it goes from their names, contain values of first two dimensions of initial customer ledger entry.

    If you have NAV <= 2009, dimensions are stored in "Ledger Entry Dimension" table:

    [Ledger Entry Dimension].[Table ID] = 21 AND 
[Ledger Entry Dimension].[Entry No_] = [Detailed Cust_ Ledger Entry].[Cust_ Ledger Entry No_]

    If you have NAV >= 2013, dimensions are stored in "Dimension Set Entry" table:

    [Dimension Set Entry].[Dimension Set ID] = [Cust_ Ledger Entry].[Dimension Set ID], 
[Cust_ Ledger Entry].[Entry No_] = [Detailed Cust_ Ledger Entry].[Cust_ Ledger Entry No_]