Skip to main content

Notifications

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

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

Posted on by 98

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.

  • Ron.McV Profile Picture
    Ron.McV 98 on at
    RE: NAV 2016 - How are G_L Entries (SQL Table) linked to GL Register (Table)

    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.

  • ADZ2023 Profile Picture
    ADZ2023 15 on at
    RE: NAV 2016 - How are G_L Entries (SQL Table) linked to GL Register (Table)

    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

  • Suggested answer
    Ghanshyam Gupta Profile Picture
    Ghanshyam Gupta 5 on at
    RE: NAV 2016 - How are G_L Entries (SQL Table) linked to GL Register (Table)

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

    pastedimage1683693315800v1.png

  • ADZ2023 Profile Picture
    ADZ2023 15 on at
    RE: NAV 2016 - How are G_L Entries (SQL Table) linked to GL Register (Table)

    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.

  • Suggested answer
    Jun Wang Profile Picture
    Jun Wang 7,433 Super User 2024 Season 2 on at
    RE: NAV 2016 - How are G_L Entries (SQL Table) linked to GL Register (Table)

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

    pastedimage1683637249744v1.png

    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.

    pastedimage1683637338149v2.png

  • ADZ2023 Profile Picture
    ADZ2023 15 on at
    RE: NAV 2016 - How are G_L Entries (SQL Table) linked to GL Register (Table)

    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.

  • Ron.McV Profile Picture
    Ron.McV 98 on at
    RE: NAV 2016 - How are G_L Entries (SQL Table) linked to GL Register (Table)

    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?

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,171 Super User 2024 Season 2 on at
    RE: NAV 2016 - How are G_L Entries (SQL Table) linked to GL Register (Table)

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

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

    pastedimage1674084267811v1.png

    pastedimage1674084288576v2.png

    Thanks.

    ZHU

  • Ron.McV Profile Picture
    Ron.McV 98 on at
    RE: NAV 2016 - How are G_L Entries (SQL Table) linked to GL Register (Table)

    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  

  • Suggested answer
    DAnny3211 Profile Picture
    DAnny3211 9,272 Super User 2024 Season 1 on at
    RE: NAV 2016 - How are G_L Entries (SQL Table) linked to GL Register (Table)

    hi

    pastedimage1674059288179v1.png

    DAniele

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!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 229,993 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans