Sum Amount Until Record n

how do i code if i want to sum amount until record n?? My Standard Journal is Like This : Debit Amount Credit Amount Amount 10 10 10 10 20 -20 20 20 I want my general journal like this : Debit Amount Credit Amount Amount Total Amount 10 10 10 10 10 20 20 -20 0 20 20 20

Are you talking about forms or reports?

hmmmmm I’m Talking About Form

Form General journal Exactly…

I want to Sum Amount like Balance , but just until line N

Hi Stan,

The Balance is a sum from the first line, down to the one active.
The Total Balance is a sum of all lines in the journal.

What is N?
How do You define wich line N represents?
Is N dynamic or static?

I would create a new function in table 81, that can calculate the value.
But I can’t be more specific without knowing more about N.

Line N, I mean Like this Debit 100 and then Amount = 100 , → I want To add a new Field,let’s say Total Amount → first line Total Amount = 100 After that line 2, credit 50… so amount = -50 I want in Total Amount = 50 → it came from (100 + (-50)) Line 3 credit amount 30… so amount = -30 In Total Amount it should be = 20 → it came from (100 + (-50) + (-30)) and so on…

I’m sorry Stan, but I don’t understand what it is You want to do…

What You descripe, to me, sounds like the default behavior of “Balance” and “Total Balance”

If I enter the 3 lines You descripe, I have the value 20 presented in “Total Balance” on form 39.
Depending on wich line i put the cursor, I have the values 100, 50, 20 presented in “Balance”

hehe sori i made you confused Sounds maybe like total balance & balance but balance and total balance returns a single value Total Amount that i want returns one or many rows… so until line 2, Total Amount 10 line 3, Total Amount could different… if line 3 then there are SUM by Line 1,Line 2,Line 3 if line 4 then there are SUM by Line 1,Line 2,Line 3,Line 4 And so on…

Think I get it now. [<:o)]

So if You have 3 lines in the Journal, You want 3 values returned…
If You have 4 lines in the Journal, You want 4 values returned…

Is that correct?

I must say that my, apparently, poor imagination, can’t imagine what You want that for.
Why not just instruct the user to move the cursor up or down in the Journal, and keep an eye on Balance.

yes it is… so if they are 4 lines, it means line 1 + line 2 + line 3 + line 4,
if 5 lines, it means line 1 + line 2 + line 3 + line 4 + line 5… hmmmm i don’t know exactly what’s that for… our user in my company ask me to add in the general journal form do you have any ide , what shoul i add in code or form?

I would start by asking the user if what he/she wants can be optained by moving cursor up/down.

I dont see how that info can be presented properly on the existing form, since if I have 10 lines, and modify amount in line 5, it will have effect on lines 5 through 10.
I can’t see how that can be updated properly.
There is no way that You can use a variable on the form, as You don’t know the upper limit of the array.
Therefore I only see this happening by using a table to store the values, as that is the way to build the “one to many”-kind relation, in my opinion.
Either a completely new table, or just a new field in table 81.

I would add a new field to table 81, say we call it “Balance2 (LCY)”

Then I would add code to the existing function “UpdateLineBalance” on table 81, so that my new field is filled.
l_JournLineRec.SETRANGE(“Journal Template Name”,“Journal Template Name”);
l_JournLineRec.SETRANGE(“Journal Batch Name”,“Journal Batch Name”);
l_JournLineRec.SETRANGE(“Line No.”,1,“Line No.”);
l_JournLineRec.CALCSUMS(“Balance (LCY)”);
“Balance2 (LCY)” := l_JournLineRec.“Balance (LCY)”;

Finally I would create a new tabular form to present the values to the user.
That form has table 81 as SourceTable.
In the OnAfterGetRecord-trigger of the form, I would call “UpdateLineBalance”-function.

Personally I think this is somewhat a crappy solution, but that’s the best I can come up with.

Someone else might have a better idea?

Thanks sander7! It’s working… I don’t know either, but they look assisted with that field :slight_smile:

sorry I have follow up question

how about if i just want to add amount, just for Account Type = Bank Account

JournalLineRec.SETRANGE(“Journal Template Name”,“Journal Template Name”);
JournalLineRec.SETRANGE(“Journal Batch Name”,“Journal Batch Name”);
JournalLineRec.SETRANGE(“Line No.”,1,“Line No.”);
JournalLineRec.SETRANGE(“Account Type”,3);
“Saldo Amount” := JournalLineRec.Amount;
JournalLineRec.CALCSUMS(“Amount (LCY)”);
“Saldo Amount (LCY)” := JournalLineRec.“Amount (LCY)”;

it couldn’t , because Account type is not the primary keys

what should i do?

Hi Stan,

To make the CALCSUMS-function work, you need a key (not nessecarily the primary key) that includes all the fields you filter on, and that have the fields you want to sum defined as SumIndexFields.

In table 81 only the primary key have SumIndexField defined.
BUT modifying this key is a No Go.

So if you want to be able do what you want, you need to create a new key on table 81, that includes all the fields you filter on, and that have the needed fields as SumIndexFields.
Keep in mind though, that adding keys will affect the performance, and every single posting-routine in NAV uses table 81 at some point.