Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV forum
Answered

Balance Column in G/L entries.

Posted on by 120
Hello,
Is it possible to add Balance or Balance at Date column to General Ledger Entries in NAV 2017?  One of our customer coming from Sage prefers seeing the Balance on a particular date along with the G/L Entry.
I tried adding the field from Dev Environment the column appears but the values on each line shows only zero. By the way am not a technical person so any suggestions?
glentry.JPG
Regards,
Krishnan
  • Krish-NAV Profile Picture
    Krish-NAV 120 on at
    RE: Balance Column in G/L entries.

    Mr. Glathe and Mr. Ansari,

    At last we designed a report to show Balance after posting in G/L Entries instead of displaying it in General Ledger Entries in the system as it takes too long to calculate. End user is also satisfied with the report. Once again thank you both for your contributions.

    Regards,

    Krish

  • Suggested answer
    Krish-NAV Profile Picture
    Krish-NAV 120 on at
    RE: Balance Column in G/L entries.

    Dear Mr. Glathe,

    I very much appreciate your effort to spend time to explain and provide the solution for my query. And I thank you very much for that. 

    Unfortunately am not a technical guy hence cannot fully understand the code part of the answer but will ask my friend who is into coding to have a look at your solution and implement the same. 

    Once again thank you and appreciate your efforts.

    Regards,

    Krish

  • Suggested answer
    Jens Glathe Profile Picture
    Jens Glathe 6,090 on at
    RE: Balance Column in G/L entries.

    Talk about hard... This running total thing looks simple, but it is not, because NAV doesn't really support it in a page. There were versions of NAV, though (Navision 3.x text-based of the 1990s) that supported a sum for a column. It was a nice feature. It disappeared with the first Windows GUI version.

    However, you can actually implement a running total on current NAV that respects the selected key and the filters. You have to calculate the running total in your own function, and store it in a buffer table. And you need to take provisions for a change of key, ascending/descending order of columns, and filters. There are practically no triggers that notify you when these things happen. Your only clue is that OnAfterGetRecord() or OnAfterGetCurrRecord() are called.

    I've made an implementation that appears to be working well. The main issue I have with it is that it's a hack, and will consume lots of processing power when you have many entries. So please use it with caution. It is based on NAV2017 W1 RTM.

    [View:/cfs-file/__key/communityserver-discussions-components-files/34/GL_5F00_RunningTotal.zip:32:24]

    The core component, function GetRunningTotal() in Page 20 does several things:

    - checks if there is a change in filters,
    - checks if for every entry in the filter range there is a calculated running total,
    - adds a starting balance if a date filter is set,
    - verifies if the order of entries is still the same (key change),
    - recalculates the whole running totals set if required,
    - returns the running total value for the current entry no. the page is actually preparing to display.

    For larger datasets this might need to change a little. One should try to calculate only the set for the displayed records. For the worst case scenario, though, the number of calculations would be the same as if the whole dataset needs to be fetched and running totals calculated. Improvements are welcome.

    This is what it looks like now:

    GLEntryRunningTotals.png

    with best regards

    Jens

  • Suggested answer
    Krish-NAV Profile Picture
    Krish-NAV 120 on at
    RE: Balance Column in G/L entries.

    Thank you Mr. Glathe. Would discuss the possibility with my developer.

    Regards,

    Krish

  • Krish-NAV Profile Picture
    Krish-NAV 120 on at
    RE: Balance Column in G/L entries.

    Thank you Mr. Ansari. Will discuss with my developer.

    Reagrds,

    Krish

  • Verified answer
    Jens Glathe Profile Picture
    Jens Glathe 6,090 on at
    RE: Balance Column in G/L entries.

    Hi Krishnan,

    this should be doable. The simplest way to do it would be a flow field in the g/l entries that calculates what you require. The catch is: You would need an appropriate field for the entry no. filter, this needs to be generated at posting time (by modifying the OnInsert() trigger of the G/L Entry table). Just to make sure, I've tried it myself. It works :)

    GLEntryBalanceAtDate.png

    If you change the sorting of the g/l entries, you will see interesting (though correct, sort of) effects. The "Balance at Date" field calculates via "G/L Account No." and with "Entry No." filtered with "..Entry No.". So, the Balance At Date field will be only consecutive when the entry nos are in consecutive order. And this is not how the user might count in this situation. To get running totals (like in Report 4, "Detail Trial Balance") you would need to do the calculation in the page. Which will be quite a challenge, IMO. You can do this with a function, but it will be expensive, performance wise.

    with best regards

    Jens

    [Edit: To get a running total seems to be hard and/or expensive.]

  • Verified answer
    AJAnsari Profile Picture
    AJAnsari 5,752 on at
    RE: Balance Column in G/L entries.

    Hi Krish,

    The short answer is yes - but it will require a developer. You may either need to create a flowfield, or add code to the field that calculates the value. And depending on how you create the field on the table, you may need to add CALCFIELDS code to the General Ledger Entries to run the code when the page displays.

    Bottomline, if you work with your NAV developer, you can accomplish this with ease.

    I hope this helps. If my response has answered your question, please verify by clicking Yes next to "Did this answer your question?"

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,989 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,588 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans