Running Balance as a Column? Table 17 and Form 20

Doc9

Go easy on me, I've only been working with nav for 1 year and 1 month including training.

I have been trying to create a column in form 20 for use when viewing ledger data. It should show a running balance of sorts. I have no problem as a developer in thinking though the logic required to do this simple task but I am having a lot of trouble in understanding how to get this done in Nav5. I am a cside certified developer (I, II) and certified in financials (I, II) so I have a good heads up on how to do this, but again I'm just not getting the results I need. I have tried/experimented with the on validate trigger for new field on table 17, flow fields, code on the form with a few variables and a function. The best I have so far is some code that can total the running balance when you force a refresh or update controls. I know its lame.

I am obviously lacking in experience and general direction. I have been searching around the objects in nav, dynamics partner source and the forms. I have been referencing cside application development with no avail.

Over all I'm just unsure of where the correct starting point and correct usage NAV tools would be for this request.

Thank you for any post or information, any one needs some help with sql, C, VB or PERL/WEB let me know i'm happy to help. #-o

garak

I have been trying to create a column in form 20 for use when viewing ledger data. It should show a running balance of sorts

What do you mean with this sentence?

ssingla

If I understood correctly you want something like this :

Entry No. G/L Account Code Amount Total
1000 1150 1500 1500
1002 1150 3000 4500
1004 1150 4000 8500

Just think what if user changes the sorting of keys and the performance.

Doc9

garak wrote:

I have been trying to create a column in form 20 for use when viewing ledger data. It should show a running balance of sorts

What do you mean with this sentence?

Please see the attached xls screen shot file for a visual explanation. See the calculation in column D.

Copy and paste of xls
Entry No. G/L Account No. Amount Running Balance
72249 41100 -67.38 -67.38
72276 41100 -295 -362.38
72283 41100 -70 -432.38
72287 41100 -3,784.00 -4216.38
72538 41100 -50 -4266.38
72539 41100 -6,468.00 -10734.38
72562 41100 -1,130.22 -11864.6
72510 41100 -60 -11924.6
72586 41100 -288 -12212.6
72591 41100 -1,706.34 -13918.94
72601 41100 -4,925.52 -18844.46
72714 41100 -2,399.00 -21243.46
72720 41100 -1,752.00 -22995.46
72897 41100 0 -22995.46
72907 41100 -1,315.00 -24310.46
72918 41100 0 -24310.46
72919 41100 -1,264.00 -25574.46
72924 41100 -372 -25946.46
72932 41100 -387 -26333.46
73055 41100 -435 -26768.46
73062 41100 0 -26768.46
73066 41100 0 -26768.46
73104 41100 -50 -26818.46
73105 41100 -2,532.00 -29350.46
73248 41100 -1,065.00 -30415.46
73258 41100 -760 -31175.46
73265 41100 -136 -31311.46
73269 41100 -1,364.78 -32676.24
73409 41100 -3,482.00 -36158.24
73417 41100 -1,156.00 -37314.24
73424 41100 -87.8 -37402.04
73761 41100 -50 -37452.04
73762 41100 -7,680.00 -45132.04
73768 41100 -3,225.00 -48357.04
73704 41100 -100 -48457.04
73705 41100 -34,106.62 -82563.66
73909 41100 -1,914.20 -84477.86
73941 41100 -2,499.00 -86976.86
73982 41100 -948 -87924.86
73996 41100 -50 -87974.86
73997 41100 -3,957.00 -91931.86
74015 41100 -742 -92673.86
74152 41100 -547 -93220.86
74164 41100 -85.12 -93305.98
74169 41100 -1,320.00 -94625.98
74292 41100 -60 -94685.98
74293 41100 -280 -94965.98
74298 41100 -2,921.00 -97886.98
74313 41100 0 -97886.98
74315 41100 0 -97886.98
74321 41100 0 -97886.98
74759 41100 -2,805.00 -100691.98
74792 41100 -1,301.20 -101993.18

garak

ah, ok like the SIFT logic.

You can do following in the table, but note, it can bring you in performance trouble if you show the window (20) and there are a lot of entries.

In the Table:

New fields:

"Run Balance" -> FlowField Decimal -> Sum("G/L Entry".Amount WHERE (G/L Account No.=FIELD(G/L Account No.),Entry No.=FIELD(Entry No Filter),O'THERFIELDSLIKEPOSTDATEORDIMENSION))

Entry No Filter -> FlowFilter -> Integer.

OnForm 20 you add field "Run Balance" and write in

Form - OnAfterGetRecord()
//Here the other FlowField that in your CalcFormula of Runbalance and, may be a copyfilters, test it self what you need
setfilter("Entry No Filter",'<=%1',"Entry No.");
calcfields("Run Balance");


Regards

Doc9

Sweet deal, your information worked well. =D>

All I can say is there are a lot of knacks to development within MS Dynamics products :?: . Is there a good source for information out side of these forums that you have come across?

garak

some books (most are written by users of the community, the "Application Designer Guide" as starting point for a beginner and, learning by doing with logical thinking and many customer projects with, if possible, different requirements to always be better ...... and, of course, MiBuSo :-)

airam

hi all,

i have a similar problem and i used your solution which solved part of my task. but i still have other 2 problems..

i implemented the solution for the item ledger entries table and form by adding these two fields on the table:

g_RunningBalance-->Decimal-->FlowField--> Sum("Item Ledger Entry".Quantity WHERE (Item No.=FIELD(Item No.),Posting Date=FIELD(g_PostingDateFilter)))
g_PostingDateFilter-->Date-->FlowFilter

and OnAFterGetRecord of the Form i inserted:

SETFILTER(g_PostingDateFilter,'<=%1',"Posting Date");
CALCFIELDS(g_RunningBalance);

now the first problem here is that where there are 2 or more entries done in the same date, the total for the day is outputted.

the other problem is that when i tried the same solution on the Customer Ledger Entries by doing:

g_RunningBalance -->Decimal-->FlowField--> Sum("Detailed Cust. Ledg. Entry".Amount WHERE (Cust. Ledger Entry No.=FIELD(Entry No.),Posting Date=FIELD(g_Filter)))

the field (g_RunningBalance) is not holding the value, always reset to 0, therefore it is outputting the value of the Amount, not the total

thanks in advance