web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Unable to do sum of th...
Microsoft Dynamics NAV (Archived)

Unable to do sum of the quantity.

(0) ShareShare
ReportReport
Posted on by manish.yadav 1,893

Dear All,

I have developed a mail report to one of the company concern person. Through this report I want to send the daily output report. But here I am unable to add the quantity. Here i want to show the sum of the quantity. here multiple lines are coming. What i am doing wrong. Please help me to resolve it.

I have attached the screenshot. and related code.

Mail.CreateMessage('XYZ Limited','nav@XYZ.com','test2@XYZ.com','OUTPUT Details','',TRUE);
Mail.AppendBody('Dear Sir / Madam,');
Mail.AppendBody('<br>');
Mail.AppendBody('Please find the daily Output details:');
Mail.AppendBody('<HR>');
Mail.AppendBody('<table border="1">');
Mail.AppendBody('<tr>');
Mail.AppendBody('<th>RPO No.</th>');
Mail.AppendBody('<th>Output Dt.</th>');
Mail.AppendBody('<th>Item No.</th>');
Mail.AppendBody('<th>Description</th>');
Mail.AppendBody('<th>Variant Code</th>');
Mail.AppendBody('<th>Qty.</th>');
Mail.AppendBody('<th>Unit</th>');
Mail.AppendBody('</tr>');

ILE.RESET;
ILE.SETRANGE(ILE."Posting Date",TODAY);
ILE.SETRANGE(ILE."Entry Type",ILE."Entry Type"::Output);
ILE.SETRANGE(ILE."Order Type",ILE."Order Type"::Production);

IF ILE.FINDSET THEN
REPEAT
CLEAR(grandtotal);

recILE.RESET;
recILE.SETRANGE("Entry No.",ILE."Entry No.");
recILE.SETRANGE("Item No.",ILE."Item No.");
IF recILE.FIND('-') THEN
REPEAT
grandtotal+= recILE.Quantity;
UNTIL recILE.NEXT = 0;

Mail.AppendBody('<tr>');
Mail.AppendBody('<br>');
Mail.AppendBody('<td align="center">'+FORMAT(ILE."Order No.")+'</td>');
Mail.AppendBody('<td align="center">'+FORMAT(ILE."Posting Date")+'</td>');
Mail.AppendBody('<td align="center">'+FORMAT(ILE."Item No.")+'</td>');

Item.RESET;
Item.SETRANGE("No.", ILE."Item No.");
IF Item.FINDFIRST THEN BEGIN
Mail.AppendBody('<td>'+FORMAT(Item.Description)+'</td>');
END;

Mail.AppendBody('<td align="center">'+FORMAT(ILE."Variant Code")+'</td>');
Mail.AppendBody('<td align="center">'+FORMAT(grandtotal)+'</td>');
Mail.AppendBody('<td align="center">'+FORMAT(ILE."Global Dimension 1 Code")+'</td>');
Mail.AppendBody('</tr>');
UNTIL ILE.NEXT = 0;

Mail.AppendBody('</table>');
Mail.AppendBody('<HR>');
Mail.AppendBody('<br>');
Mail.AppendBody('Regards');
Mail.AppendBody('<br>');
Mail.AppendBody('Production Department');
Mail.AppendBody(' ');
Mail.AppendBody('<br><br>'); //// e-mail code
Mail.Send;

NOte: here "grandtotal" is a decimal type variable.

Output is coming in this form

tt.png

Report should come the below format:

tt2.png

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,166 Most Valuable Professional on at

    It's not clear what you want to do and what are the tables in use, but it seems that in the first step you filter the ILE table, then for every record of the returned filter (result set) you apply again another filter (with a different Record variable) on the same table. This is your code in the second part:

    recILE.RESET;

    recILE.SETRANGE("Entry No.",ILE."Entry No.");

    recILE.SETRANGE("Item No.",ILE."Item No.");

    IF recILE.FIND('-') THEN

    REPEAT

    grandtotal+= recILE.Quantity;

    UNTIL recILE.NEXT = 0;

    Maybe filtering by Entry No. returns only a single record and so the sum of the quantity is always 1?

    Just a supposition, it's difficult without knowing what tables are that :) I suggest you to debug the code.

  • manish.yadav Profile Picture
    manish.yadav 1,893 on at

    My report is correct. Only problem is that I want to show the total quantity. as of now there have multiple lines how can show the total quantity in one line.

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,166 Most Valuable Professional on at

    Here the grandtotal variable is the total for every line.

    You can use a second variable (called total) and sum it without clearing:

    IF ILE.FINDSET THEN

    REPEAT

    CLEAR(grandtotal);

    recILE.RESET;

    recILE.SETRANGE("Entry No.",ILE."Entry No.");

    recILE.SETRANGE("Item No.",ILE."Item No.");

    IF recILE.FIND('-') THEN

    REPEAT

    grandtotal+= recILE.Quantity;

    total += recILE.Quantity;

    UNTIL recILE.NEXT = 0;

    At the end of this, grandtotal is the total for every recILE record, total is the general total.

  • manish.yadav Profile Picture
    manish.yadav 1,893 on at

    I am not getting.

    In my report every line is coming against the RPO no. with its quantity. We are using the serial no.(item tracking) type output. for example if we have generated the 500 qty output then 500 lines will generated in ILE. But in my report i want to show total sum of the quantity in single line.

  • ManishS Profile Picture
    ManishS 86 on at

    What is base data item of you report ?

  • manish.yadav Profile Picture
    manish.yadav 1,893 on at

    Item ledger entry.

  • ManishS Profile Picture
    ManishS 86 on at

    So for each item ledger entry you want to send emails ?

  • Suggested answer
    RockwithNav Profile Picture
    RockwithNav 8,625 Super User 2025 Season 2 on at

    Why not to use Query or CALCSUMS for this??

    recILE.RESET;

    recILE.SETRANGE("Entry No.",ILE."Entry No.");

    recILE.SETRANGE("Item No.",ILE."Item No.");

    IF recILE.FIND('-') THEN

    REPEAT

    grandtotal+= recILE.Quantity;

    UNTIL recILE.NEXT = 0;

  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,243 Super User 2025 Season 2 on at

    Hello,

    In your query, you are filtering for the same Entry No. in your inner loop which will result in only one entry, because the entry no. is the key for the ILE table.

    The best way to do is create a variable of type ILE and set it to temporary and insert the records into the table as you loop through the ledger entry table. Before insert into temporary table check if there is a record already exists with the same Item No. and Prod. Order No. if so, update the quantity instead of insert. 

    That way at the end of the loop you should have a temporary table with summary information which you will use to build the HTML email.

    recILE.RESET;

    recILE.SETRANGE("Entry No.",ILE."Entry No."); ///  This is wrong filter you need to use Item No. and Prod Order No. as the filter. Anyhow use the temporary table.

    recILE.SETRANGE("Item No.",ILE."Item No.");

    IF recILE.FIND('-') THEN

    REPEAT

    grandtotal+= recILE.Quantity;

    UNTIL recILE.NEXT = 0;

  • Community Member Profile Picture
    Community Member on at

    Hi Mky,

    map the qty field and in first line  and hide that row after that in second row  call the running value of qty from first line. you will get the total of qty only.

    qty Expression(First line)

    =First(Fields!Qty.Value,"DataSet1")

    qty Expression(Second  line)

    =RunningValue(Fields!Qty.Value,sum,"DataSet1")

    now hide the first line and now you will get the total of qty only.

    Thanks & Regards,

    Ashwini.E

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans