Please help me Re:Sum(Amount)

nhel

Hi guys,

Anybody who have idea on how i get the sum of a particular field. Without coding like DRAmt := DRAmt + DRAmtTemp;

Example: "Gen. Journal Line"."Debit Amount" to get this summation.
But this is not a FlowField.How can i make this field to be a flowfield so that I can apply this code "Gen. Journal Line".Calcfields("Debit Amount")

Thanks in advance.

rajpatelbca

if you want to make field as a flow filed. flowfield is a actually user for faster calculation. it is based on sumindexfield technology. fo eg.

if you want to add make field as flow filed then change fieldcalss property of field. use appropriate method like sum,avg,min,max,exist.lookup,etc.

in this you have to define Table,field,tablefilter

here you will calculate flowfield for a field of table by using table filter.

so, finally u have to add that flterfield in a key list of table and field in sumindex field of defined table.

if you want more help then read Develper I.


Regards,

Administrator

[Topic moved from Navision Tips & Tricks to Navision forum]

nhel

Hi Rajesh..

I am just new with this navision.
And thanks for your time accomodating my questions.

But it seems i cant follow your instructions how should i declare this flieldclass as flowfield.Are you talking of table object properties or the Report Section properties?

As i take a look into those two object both table and Section of the report i dont see this fieldclass property for i would change into flowfield so that I can continue with my summation of amount.

Thank you very much Rajesh,

Anyway where can I find this Delver I.Is it online manual or a book that can be bought online.

Warm Regards,

Nhel

krzychub83

You set a FlowField on a Table.
Focus on a row , and Shift+F4.
In property window You will find FieldClass.
GoodLuck

nhel

I followed you suggestion but i encountered error during compiling the table "Gen Journal Line" "You cannot delete or change the type of the Debit Amount field (In the Gen. Journal Line Table) before the value in the field is reduce to 0 (zero) or blank in all records. This message occured because a nonzero values found for the record Journal Template Name = 'CASHRCPT' ,Journal Batch Name = 'BANK', Line No, "10000" in company Cronus Philippines Ltd."

I have check the Journal Template table and Journal Batch i cant find the 'Cashrcpt' column, or related to this.

Thanks,

nhel

And also when i look the Gen. Journal Line."Debit amount" values are all numeric.

rajpatelbca

Anyway where can I find this Delver I.Is it online manual or a book that can be bought online.

you can buy it or download with your partner login from microsoft site. there is one more document available in your product CD in Doc folder Named "Application Designer Guide".

in your case you have to do it DRAmt := DRAmt + DRAmtTemp; this way.

regards,

nhel

I think so, that would be a work around solution for this.
Because this standard table "Gen Journal Line" cannot be update because of error occurs.

Thanks again with your time.And sharing knowledge.

And hoping we will met halfway thru this disccussions regarding NAV informations.

Best regards,
Nhel

krzychub83

You already have some entries in this column, so You can not change column data type/class ...

Other way to make some calculations, is to use Rec."CalcSums" function. Read more in NAV. Help about it, and on this forum of course .

Quick Guide:
1. Add the column name in SumIndexFields of the Table Key, which You want to use.
2. Then when You will need this sum You can code:

GenJurnRec.SETCURENTKEY(KeyWithYourColumnNameInSumIndexField);
GenJurnRec.SETFIELTER()...
GenJurnRec.CALCSUMS(YourColumnNameToSumIt);

Now You have sum of records in Gen. Jurnal line. You can use it by:
GenJurnRec."YourColumnNameToSumIt"

GoodLuck

nhel

Hi krzychub83,

I have tried your quick guide but i cannot find this SumIndexFields both section properties and table properties.

How should i do this?Setting the "Debit Amount" as SumIndexFields characteristics

Thanks in advance.

krzychub83

Go to Keys of the Table
View -> Keys

nhel

Hi krzychub83,

I notice theres an existing Key in table "Gen. Journal Line" and Existing SumIndexFields "Balance(LCY)" Then i Use to add the "Debit Amount" beside the Balance(LCY).

This is original set - up of keys
Key SumindexFields
"Journal Template Name.Journal Batch Name,Line No." Balance(LCY)

What I did Is include "Debit Amount" besides Balance(LCY)


Then I attached a control with SourceExpression = "Gen. Journal Line".Calcsums("Debit Amount") but i notice the output is boolean.

It returns "YES". Do i need to create a new keys?then assign SumIndexFields for "Debit Amount".

And should i have to code the following?

"Gen. Journal Line".SETCURENTKEY(KeyWithYourColumnNameInSumIndexField);
"Gen. Journal Line".SETFIELTER()...

"Gen. Journal Line".CALCSUMS("Debit Amount"); //Since this line is an expression of control object attached to the report.And i am thinking no need for this code Gen. Journal Line".CALCSUMS("Debit Amount");

Thanks in advance.

krzychub83

Hi nhel

I wasn't able to answer sooner. I see that my solution probably wont help You to solve the problem.

1. Case

This solution is working for a TextBox Control.
(I used CalcSums in CodeUnits - not in Form, but it should work)

Define 2 globals:
SumOfCol, DECIMAL
GenRec, RECORD, Table81

You SourceExpresion on Control has to be SumOfCol. If You dont use the PrimaryKey of GenRec, then remember to ad GenRec.SETCURRENTKEY(...)

a) Then if it has to be only a global sum then put something like this in the
OnOpenForm trigger:

GenRec.CALCSUMS("Amount");
SumGlobal :=GenRec."Amount";

b) If sum has to be dependent from Rec values, then You should put it in the OnAfterGetCurrRec trigger:

GenRec.SETFILTER(...)
GenRec.CALCSUMS("Amount");
SumGlobal :=GenRec."Amount";

2. Case (Much more properly)

You made mistake, which I should see sooner(sorry for this):

I followed you suggestion but i encountered error during compiling the table "Gen Journal Line" "You cannot delete or change the type of the Debit Amount field (In the Gen. Journal Line Table) before the value in the field is reduce to 0 (zero) or blank in all records. This message occured because a nonzero values found for the record Journal Template Name = 'CASHRCPT' ,Journal Batch Name = 'BANK', Line No, "10000" in company Cronus Philippines Ltd."

NEVER EVER change colums properties not made by You - You can then make a lot of errors on forms, report, and and... (ok, You can play with it, when You will have more experience. )

Just make a new column in the table 81. It ID should be proper to You licenses (I think it >=50000). Then set all properties of this column like rajpatelbca posted.
( Example
FieldClas: FlowField
CalcFormula: Sum("Gen. Journal Line"."Amount" WHERE (Posting Date=CONST(01-01-01)))
)

This should work, and it is a very good way to solve Your problem.
I won't be able to help You more today.
GoodLuck

nhel

Hi krzychub83,

Thanks with your advice.
And thanks for your time today.

Ill just following this procedures today. And hope this will work for my current report i am doing.

Thank you very much guyz..

nhel

Hi krzychub83,

Thanks for helping me this summation concern.
This case helps me to come up the correct answer.

1. Case

This solution is working for a TextBox Control.
(I used CalcSums in CodeUnits - not in Form, but it should work)

Define 2 globals:
SumOfCol, DECIMAL
GenRec, RECORD, Table81

You SourceExpresion on Control has to be SumOfCol. If You dont use the PrimaryKey of GenRec, then remember to ad GenRec.SETCURRENTKEY(...)

a) Then if it has to be only a global sum then put something like this in the
OnOpenForm trigger:

GenRec.CALCSUMS("Amount");
SumOfCol:=GenRec."Amount";

Thanks again for your time and sharing knowledge.
=D>