Small and medium business | Business Central, N...

NAV 2016 - How are G_L Entries (SQL Table) linked to GL Register (Table)

(1) Share

Report

Posted on by Ron.McV

Ok novice question or just clueless, How are the G_L Entries (SQL Table) linked to the G_L Register (Table)?
I have provided GL Entries to Auditor's before but 1st time that this needs to include G_L Register too in the same table.
And I am not sure the JOIN between the two table at the SQL level. I see the From Entry and the To Entry.,

if you've done this before or know, your help is appreciated. (a light bulb moment).
Thanks.

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

DAnny3211 11,397 on at

Like (0)

Report

hi

DAniele

Was this reply helpful? Yes No
Ron.McV 98 on at

Like (0)

Report

Thank you and I understand that but I intending to create a SQL View to JOIN the G_L Entries to the G_L Register; and so, needing to craft a JOIN between the two so that to the list (view) of G_L Entries I display, five (5) of the G_L Register columns with the G_L Entry columns.

Example is that G_Entry No 1034789 is in the From Entry No and G_Entry No 1034790 is in the To Entry No

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,729 Super User 2025 Season 2 on at

Like (0)

Report

Hi, Just recommending a site that hopefully will give you some more hints.

https://dynamicsdocs.com/nav/2018/w1/table/gl-register

Thanks.

ZHU

Was this reply helpful? Yes No
Ron.McV 98 on at

Like (0)

Report

thanks for reply. The join for G_L Entries and G_L Register is does the G_L Entrie No_ exist BETWEEN G_L Register Columns From Entry No AND To Entry No.

I don’t know how to that JOIN. Has anyone done that?

Was this reply helpful? Yes No
ADZ2023 15 on at

Like (0)

Report

Hi, I am trying to do the same in Power bi from Business Central and have been struggling. Did you manage to get a solution yet? Should I get it I will also share.

Was this reply helpful? Yes No
Suggested answer

Jun Wang 8,202 Super User 2025 Season 2 on at

Like (0)

Report

the g/l entries has each an entry no. as the below example

from the g/l register page, it shows the from entry and to entry no which correspond to the specific g/l entry as below example. whenever a posting happens that impacts the GL, it creates G/L register and the corresponding entries. you could export both for the auditor for their reference.

Was this reply helpful? Yes No
ADZ2023 15 on at

Like (0)

Report

Thank you so much for the prompt response. I have multiple companies. Is there no way I can build a report in Power Bi or Excel such that I can select the range and it pulls all the entries for me? What I want is the entries that were posted after GL ocked.

Was this reply helpful? Yes No
Suggested answer

Ghanshyam Gupta 5 on at

Like (0)

Report

you can write the below query to join G/L Register table with G/L Entry table.

Was this reply helpful? Yes No
ADZ2023 15 on at

Like (0)

Report

Hi,

Thank you for the below!

I would like to join these in Power Query or Power Bi DAX if you do not mind.

Brgds

Was this reply helpful? Yes No
Ron.McV 98 on at

Like (1)

Report

This was my answer or what work for us and I hope it helps you too

---------

So, I bring to the Power BI report two dataset: G/L Entries and G_L Register. This can be through a SQL View or a SQL Script as it is not always necessary to have all the columns from both source (NAV) tables. The me I add a column [Entry No_] from G/L Entries table and this is done by a INNER JOIN of G_L Register table. I need multiple company, but you may not so as a backdrop, I have UNION ALL the individual companies together for each: G/L Entries and G_L Register.

The INNER JOIN is the [Entry No_] BETWEEN the [From Entry No_] AND [To Entry No_]. Again, because I am multicompany I have added a column to both called [Parent] or you could call [Company} and so in the JOIN the two [Parent] columns are joined so that Register entries are unique across all companies. If for you one company just leave this off.

Again I am using a view and not a SQL Script directly in Power BI for the datasets, but a SQL Script could be used as well. This then requires either the Personal Gateway or the On-Premise Gateway so that you can refresh these if you publish to Power BI Services (to a Workspace). Else if you are local and just using Power BI Desktop then the gateway comment is not necessary. Lastly, I add to custom columns before I use merge queries in Power BI Query Editor. It is the same column to each of the datasets. only because I am multicompany. This is the Entry No which is now in both dataset and the [Parent] column which for me is the company. that way they are unique. The join is a LEFT OUTER (all from First and matching from second). If you are not multicompany then you'll just JOIN on the [Entry No_].

If you do this all in Power Query from the Merge Queries it is still complicated, but the JOIN does not give you natively the BETWEEN for both columns in the Register dataset. and you have to insert a custom script.

example (generic): if [Entry No_] >= [From Entry No_] and [Entry No_] <= [To Entry No_] then true else false

I prefer to do most if not all the hard work in SQL either is a script or a SQL view.

ps. I hope this helps. Your partner or your DBA or a data analyst might be able to help you further.

For me the goal was to add these columns from the Register dataset to the G_L Entries dataset.

Was this reply helpful? Yes No