How to join GL Entry and Value Entry using table 5823

Business Central/NAV Developers Forum
1

Hello, I have a question. I would like to ask for help to learn how to join GL Entry table and Value entry table to be able to drill down to an Document Number, Item Number, etc.

We are using NAV 2013 and I have access to table 5823 - G/L - Item Ledger Relation, but my SQL skills are limited and I am getting multiple records, and I think this is caused by how I am doing the join of the tables.

If anyone would be kind to shoe me a simple example of how to correctly join the tables, that would be awesome. If I am asking this question in the wrong forum, please accept my apology.

Thank you much.

2

G/L Entry No field of 5823 table with Entry No field of G/L Entry (17 Table)

for one Value entry there will be two G/L Entries

3

I am joining this way:

FROM

NAV.[dbo].[G_L - Item Ledger Relation] rel – Relation table between GL Value Entry and Value Entry

FULL OUTER JOIN NAV.dbo.[G_L Entry] gle ON rel.[G_L Entry No_] = gle.[Entry No_]

FULL OUTER JOIN NAV.dbo.[Value Entry] ve ON rel.[Value Entry No_] = ve.[Entry No_]

Does this looks correct?

Thank you.

4

Dont use Full outer join for G/l Entry as unnecessary rows will be fetched, instead use inner join or left outer join

5

what I am getting is the results below, the only difference is that one row contains an item number and the other one not. and i just need the one entry with item.


2010-05-28 00:00:00.000	13110	NULL	SPI03164	-24995.95000000000000000000	NULL	NULL	NULL
2010-05-28 00:00:00.000	13110	10828	SPI03164	0.00000000000000000000	0.00000000000000000000	0.00000000000000000000	NULL
6

for one value entry there will be two rows in 5823 table and when you link 5823 to 17 table this is one to one relation

you need to do grouping on Item No if you want one row entry item wise

7

Let me show you examples of my problem.

Results using FULL OUTER JOIN, and include Item Number in the GROUP BY clause.

SELECT

gle.[Posting Date] AS PostingDate, gle.[G_L Account No_] AS GL_AccountNumber, gle.[Document No_] AS DocumentNumber, ve.[Item No_] AS ItemNumber, SUM(gle.Amount) GL_Balance, SUM(ve.[Item Ledger Entry Quantity]) AS Quantity, SUM(ve.[Cost Amount (Actual)]) + SUM(ve.[Cost Amount (Expected)]) AS Cost, SUM(ve.[Cost Amount (Actual)] + ve.[Cost Amount (Expected)]) / NULLIF(SUM(ve.[Item Ledger Entry Quantity]), 0) AS UnitCost

FROM

[G_L - Item Ledger Relation] rel

FULL OUTER JOIN [G_L Entry] gle ON rel.[G_L Entry No_] = gle.[Entry No_]

FULL OUTER JOIN [Value Entry] ve ON rel.[Value Entry No_] = ve.[Entry No_]

WHERE

gle.[G_L Account No_] IN (‘13110’) AND

gle.[Posting Date] BETWEEN ‘1753-01-01 00:00:00’ AND ‘2013-11-23 00:00:00’ AND

gle.[Document No_] = ‘SPI03115’

GROUP BY

gle.[Posting Date], gle.[G_L Account No_], gle.[Document No_], ve.[Item No_]

I get:

2010-05-25 00:00:00.000 13110 SPI03115 NULL -25305.05000000000000000000 NULL NULL NULL

2010-05-25 00:00:00.000 13110 SPI03115 10828 0.00000000000000000000 0.00000000000000000000 0.00000000000000000000 NULL

8

When using INNER JOIN.

I get just one row, but all empty cost columns:

2010-05-25 00:00:00.000 13110 SPI03115 10828 0.00000000000000000000 0.00000000000000000000 0.00000000000000000000 NULL

9

I am not posting the LEFT OUTER JOIN example, because it gives the same results as INNER JOIN

10

This example gives me the row I need (I think)

SELECT

– Group

gle.[Posting Date] AS PostingDate

, gle.[G_L Account No_] AS GL_AccountNumber

, gle.[Document No_] AS DocumentNumber

– Aggregation

, MAX(ve.[Item No_]) AS ItemNumber

, SUM(gle.Amount) GL_Balance

, SUM(ve.[Item Ledger Entry Quantity]) AS Quantity

, SUM(ve.[Cost Amount (Actual)]) + SUM(ve.[Cost Amount (Expected)]) AS Cost

, SUM(ve.[Cost Amount (Actual)] + ve.[Cost Amount (Expected)]) / NULLIF(SUM(ve.[Item Ledger Entry Quantity]), 0) AS UnitCost

FROM

NAV.[dbo].[CQC$G_L - Item Ledger Relation] rel – Relation table between GL Value Entry

FULL OUTER JOIN NAV.dbo.[CQC$G_L Entry] gle ON rel.[G_L Entry No_] = gle.[Entry No_]

FULL OUTER JOIN NAV.dbo.[CQC$Value Entry] ve ON rel.[Value Entry No_] = ve.[Entry No_]

WHERE

gle.[G_L Account No_] IN (‘13110’) AND

gle.[Posting Date] BETWEEN ‘1753-01-01 00:00:00’ AND ‘2013-11-23 00:00:00’ AND

gle.[Document No_] = ‘SPI03115’

GROUP BY

gle.[Posting Date]

, gle.[G_L Account No_]

, gle.[Document No_]

–, ve.[Item No_]

I get:

2010-05-25 00:00:00.000 13110 SPI03115 10828 -25305.05000000000000000000 0.00000000000000000000 0.00000000000000000000 NULL

11

I am just not sure of the join type and the use of the MAX function. I rarely use it in the past.

12

Did you use inner join at both the places

13

I apologize, the first example (Thu, Dec 5 2013 11:22) was the INNER JOIN example, I just wrote FULL OUTER because I was not paying attention.

To answer your question, yes I used INNER JOIN in both.

14

Inner join —> it will fetch only common rows of the tables based on join

Left outer join—> it will fetch all the rows of left side table and corresponding rows of right hand side table, if there is no record in right hand side null value will be shown.

Right outer join → opposite of left outer join

Full outer join —> combination of left and right

what exactly you are trying to achieve by linking all this tables?

15

get the gl balance, but be able to drill down to the document number, the gl account, and the item number.

16

the right, left and inner join give me the row that has the item,but no gl balance, nor costs.

17

Please try this

SELECT

gle.[Posting Date] AS PostingDate, gle.[G_L Account No_] AS GL_AccountNumber, gle.[Document No_] AS DocumentNumber, ve.[Item No_] AS ItemNumber, SUM(gle.Amount) GL_Balance, SUM(ve.[Item Ledger Entry Quantity]) AS Quantity,SUM(ve.[Cost Amount (Actual)]) + SUM(ve.[Cost Amount (Expected)]) AS Cost, SUM(ve.[Cost Amount (Actual)] + ve.[Cost Amount (Expected)]) / NULLIF(SUM(ve.[Item Ledger Entry Quantity]), 0) AS UnitCost

FROM

[G_L Entry] gle

inner JOIN [G_L - Item Ledger Relation] rel ON gle.[Entry No_] = rel.[G_L Entry No_]

inner JOIN [Value Entry] ve ON rel.[Value Entry No_] = ve.[Entry No_]

WHERE

gle.[G_L Account No_] IN (‘13110’) AND

gle.[Posting Date] BETWEEN ‘1753-01-01 00:00:00’ AND ‘2013-11-23 00:00:00’ AND

gle.[Document No_] = ‘SPI03115’

GROUP BY

gle.[Posting Date], gle.[G_L Account No_], gle.[Document No_], ve.[Item No_]

18

Thank you Nandesh.

I tried a similar query and that gives me only one row, the problem is that it gives me the row without any values in the balance.

SELECT

gle.[Posting Date] AS PostingDate

, gle.[G_L Account No_] AS GL_AccountNumber

, gle.[Document No_] AS DocumentNumber

, ve.[Item No_] AS ItemNumber

, SUM(gle.Amount) GL_Balance

, SUM(ve.[Item Ledger Entry Quantity]) AS Quantity

, SUM(ve.[Cost Amount (Actual)]) + SUM(ve.[Cost Amount (Expected)]) AS Cost

, SUM(ve.[Cost Amount (Actual)] + ve.[Cost Amount (Expected)]) / NULLIF(SUM(ve.[Item Ledger Entry Quantity]), 0) AS UnitCost

FROM

NAV.dbo.[G_L Entry] gle

INNER JOIN NAV.[dbo].[G_L - Item Ledger Relation] rel

ON gle.[Entry No_] = rel.[G_L Entry No_]

INNER JOIN NAV.dbo.[Value Entry] ve

ON rel.[Value Entry No_] = ve.[Entry No_]

WHERE

gle.[G_L Account No_] IN (‘13110’) AND

gle.[Posting Date] BETWEEN ‘1753-01-01 00:00:00’ AND ‘2013-11-23 00:00:00’ AND

gle.[Document No_] = ‘SPI03115’

GROUP BY

gle.[Posting Date]

, gle.[G_L Account No_]

, gle.[Document No_]

, ve.[Item No_]

2010-05-25 00:00:00.000 13110 SPI03115 10828 0.00000000000000000000 0.00000000000000000000 0.00000000000000000000 NULL

and I know that the other row (second row), does contain a gl balance value. I do not know how to force the single row to be returned to be the one with a gl balnce value.

Look at the first example I shared.

Thank you so much for your help.

19

you have put account no filter…please check all the filters

20

I did, isn’t this one?: gle.[G_L Account No_] IN (‘13110’)