Calculate total on a variable of type record filtered

andy76

Hello,

I have a report based on a dataitem of type "Item".

In this report I set various variables and filters and at the end I have a variable of type record "Sales Invoice Line" filtered for various field dynamically setted.
I want to calculate some total as for example Quantity or Amount. How is that possible? "Sales Invoice Line" is not a dataitem but only a record variable.
Do I have to loop for each single line to add the values?

I am developing a very complex analysis report.

Thank you very much

DaveT

Hi Andy,

have a look at the CALCSUMS function - it should solve a lot of your problems.

andy76

I though the same thing but CALCSUMS is not for recalculate the field of a single record? not for a total of a range?#-o

Thanks

DaveT

Hi Andy,

I think you're mixing this up with CALCFIELDS which is for flowfields.

See the example in the help

Example
This example shows how to use the CALCSUMS function.

"Cust. Ledger Entry".SETCURRENTKEY("Customer No.","Date");
"Cust. Ledger Entry".SETRANGE("Customer No.", 'AAA 1050');
"Cust. Ledger Entry".SETRANGE("Date", 010196D, 123196D);
"Cust. Ledger Entry".CALCSUMS("Amount");

The first line selects a key. The second and third lines set filters for the fields Customer No. and Date in the Cust. Ledger Entry record so the total is only calculated within the specified range. The CALCSUMS function then finds the net change in account AAA 1050 for 1996. The Amount field will show the result of the calculation.

andy76

Ok, so it is perfect... but can I sum everyfields? or only some types (as flowfield...)?

Thank you very much

DaveT

Hi Andy,

This command works with Sumindexfields which are setup on the keys tab on the table you are using.

andy76

For example, I have to make sums on a variable record of table "Sales Invoice Line".

On this table the only SumIndex keys that I see under Key windows are : Amount,Amount Including VAT

1) Can't I use CALCSUMS for sum Quantity field?
2) do I have to use primary key "Document No.,Line No." or can I use a secondary key as "Type,No.,Sell-to Customer No." to be able to use CALCSUMS?
3) to get the value calculated in your previous example:

"
"Cust. Ledger Entry".SETCURRENTKEY("Customer No.","Date");
"Cust. Ledger Entry".SETRANGE("Customer No.", 'AAA 1050');
"Cust. Ledger Entry".SETRANGE("Date", 010196D, 123196D);
"Cust. Ledger Entry".CALCSUMS("Amount");
"

I have to read the value "Cust. Ledger Entry".Amount where there is the total ?

Thank you

DaveT

Hi Andy,

1) Yes you can use the Quantity field but you will have to add it to an index.
2) You can add the sumindexfield to any of the key - use the one which is best for you as this can have a performance implication (look at the sales line table 37 for an example)
3) The total is in the value of "Cust. Ledger Entry".Amount after the call to the command. If you are using the line values in a report then Currreport.CreateTotals is the way to go.

Hope this helps.