Balance Column in G/L entries.

(0) Share

Report

Posted on by Krish-NAV

122

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?

Regards,
Krishnan

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (2)

Verified answer

AJAnsari 5,754 on at

Like (1)

Report
Copy link

Link copied!

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?"

Was this reply helpful? Yes No
Verified answer

Jens Glathe 6,092 on at

Like (1)

Report
Copy link

Link copied!

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.]

Was this reply helpful? Yes No
Krish-NAV 122 on at

Like (0)

Report
Copy link

Link copied!

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

Reagrds,

Krish

Was this reply helpful? Yes No
Suggested answer

Krish-NAV 122 on at

Like (0)

Report
Copy link

Link copied!

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

Regards,

Krish

Was this reply helpful? Yes No
Suggested answer

Jens Glathe 6,092 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Suggested answer

Krish-NAV 122 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Krish-NAV 122 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No