120
*This post is locked for comments
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
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
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:
with best regards
Jens
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 :)
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.]
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?"
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (