How to do that....Item.CALCSUMS("Sales (LCY)")???

voodoo

I have a report and I want to do Item.CALCSUMS("Sales (LCY)"), but Sales (LCY) has complicated CalcFormula, and in Item table there aren't a key for that filter in CalcFormula.

Anyone knows solution for that???

Thnx

Marije_Brummel

Why do you think you need a key in the Item table. :?

Flowfields are only calculated if the keys and sumindexes are present, otherwise you will receive a runtime error.

Have you tried to use it? What error do you get (If any)

voodoo

This is the error:

The sum of the values in the Sales (LCY) field cannot be calculated because the currnet key does not contain all the fields being filtered.
You must select a key that contain all the fields in the filter. The order of the field is unimportant.

Filters: Date filter.....
Table: Item
Key Fileds: No.

???

Marije_Brummel

Ah, I thought it would be something like that.

The only way to get this to work is to create the key Navision asks for.

Nomaly this flowfield is calculated from the Value Entries.

Have you checked if the key is present? Maybe it is not active.
Otherwise just create the key in the Value Entry table.

voodoo

Aha, i thought that i need to create a key in Item table, and that confuse me, because these fields are not exist in Item table.

Marije_Brummel

Ok, that is clarified then.

Succes 8)

voodoo

Well, still have the same problem.......let me explain.......In report one of dataitem is Item, and in OnAfterGetRecord I want to do CalcSums(Sales (LCY), in value entry table I checked key with fields in CalcFormula of Sales (LCY), but still have the same message.

Marije_Brummel

Have you defined the sumindexfield?

voodoo

You mean in Value Entry table??? Yes, in VE table Sales Amount (Actual) is SumIndexfield

....

Key in VE:
Item No.,Item Ledger Entry Type,Global Dimension 1 Code,Global Dimension 2 Code,Location Code,Drop Shipment,Variant Code,Bin Code,Posting Date
SumIndexFiled: Sales Amount (Actual)


CalcFormula for Sales (LCY) in Item table:
Sum("Value Entry"."Sales Amount (Actual)" WHERE (Item Ledger Entry Type=CONST(Sale),Item No.=FIELD(No.),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),Location Code=FIELD(Location Filter),Drop Shipment=FIELD(Drop Shipment Filter),Variant Code=FIELD(Variant Filter),Bin Code=FIELD(Bin Filter),Posting Date=FIELD(Date Filter)))

What is the problem with this
:oops:

Marije_Brummel

Could you please give some details about the code you have created?

I am affraid the error is in the way the calcsums is used, not in the key (anymore).

voodoo

Well, like I said before, only code is in OnAfterGetRecord(Item - Dataitem):
CalcSums(Sales (LCY))

Marije_Brummel

Try to replace Calcsums by Calcfields

Sorry I did not see that earlier. I need weekend I guess

voodoo

It works with Calcfields, but can u tell me what is the problem with Calcsums???

Tnhx, and sorry if i'm interrupting you, I know the weekend is so close

Marije_Brummel

Calcsums is intended to calculate the sums of more than one record in a filter.

e.g.

ValueEntry.SETCURRENTKEY("Item No.");
ValueEntry.SETRANGE("Item No.", '10000');
ValueEntry.CALCSUMS(Amount);

this calculates the total amount of all valueentries where Item No. = 10000

NOTE: you need to define a key that contains all fields you filter on.

Calcfields is used for flowfields

e.g.

Item.GET('10000');
Item.CALCFIELDS(Amount);

If the flowfield relates to the value enties the result is the same as in example 1.

I hope this answers your question.

voodoo

Yes this is the answer, but I need to calculate the sums for all items for selected classes, and result which I get isn't good(with calcfields)

Pawel_Mr

I'm not sure but I think that You can't use CalcSums with FlowField. You should use something like this:
Declare a variable, for example "Total" and then:

total:=0;
table.Setrange(....);
...
...
IF table.find('-') THEN
repeat
table.calcfields("Sales(LCY)");
total:=total+table."Sales(LCY)";
until table.next()=0;

I think it should work ok.

Marije_Brummel

This is true.

You can also filter on the valueentries for all your items and do the calcsums,

ValueEntry.SETCURRENTKEY("Item No.");
ValueEntry.SETRANGE("Item No.", '10000', '50000');
ValueEntry.CALCSUMS(Amount);

kine

May be that problem is, that you want to use calcsums on flowfields, and it is not possible... :-) if you need sums of flowfields, you need to use the flowfield definition to apply filters to the slave table and call calcsums to this table, not to the master...

example: if the flowfield definition is

Sum("Value Entry"."Sales Amount (Actual)" WHERE (Item Ledger Entry Type=CONST(Sale),Item No.=FIELD(No.),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),Location Code=FIELD(Location Filter),Drop Shipment=FIELD(Drop Shipment Filter),Variant Code=FIELD(Variant Filter),Bin Code=FIELD(Bin Filter),Posting Date=FIELD(Date Filter)))

You need to open "Value Entry" table, apply all filters as are in the definition and add you own filters you need (filtered fields must be in some key), and call "Value Entry".CALCSUMS("Sales Amount (Actual)").
Than you will have sums, you need...