mibuso.com
Home› NAV/Navision Classic Client

Calculate total on a variable of type record filtered

andy76andy76 Member Posts: 616
edited 2008-10-29 in NAV/Navision Classic Client
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

Comments

  • DaveTDaveT Member Posts: 1,039
    Hi Andy,

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

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
  • andy76andy76 Member Posts: 616
    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
  • DaveTDaveT Member Posts: 1,039
    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.
    Dave Treanor

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
  • andy76andy76 Member Posts: 616
    Ok, so it is perfect... but can I sum everyfields? or only some types (as flowfield...)?

    Thank you very much
  • DaveTDaveT Member Posts: 1,039
    Hi Andy,

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

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
  • andy76andy76 Member Posts: 616
    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
  • DaveTDaveT Member Posts: 1,039
    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.
    Dave Treanor

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
Sign In or Register to comment.