Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Total sum problem
Microsoft Dynamics NAV (Archived)

Total sum problem

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

Dear Experts,

I have write a mail code for show the total cost based on its inventory posting group and unit(global dimension code 1). But in output always comes up the single value. Please help me to short this. My base table is "Value Entry". In value entry there have multiple  inventory posting group but in my output only come the single posting group. 

Please find the cost details summary:

Item Group

Unit

Total Cost Amount

Raw Material

101

822,918,144.31

My code is below:

Mail.CreateMessage('ABC Limited','nav@ABC.com','manish.yadav@abc.com','Stock Valuation Report','',TRUE) ;
Mail.AppendBody('Dear Sir / Madam,');
Mail.AppendBody('<br><br>');
Mail.AppendBody('Please find the cost details summary:');
Mail.AppendBody('<br><br>');
Mail.AppendBody('<table border="1">');
Mail.AppendBody('<tr>');

Mail.AppendBody('<th align="center">Item Group</th>');
Mail.AppendBody('<th align="center">Unit</th>');
Mail.AppendBody('<th align="right">Total Cost Amount</th>');
Mail.AppendBody('</tr>');

TotalCostValue:=0;
ValueEntry.RESET;
ValueEntry.SETFILTER(ValueEntry."Posting Date",'%1..%2',0D,TODAY);

IF ValueEntry.FINDFIRST THEN BEGIN
ValueEntry.CALCSUMS("Cost Amount (Actual)");
ValueEntry.CALCSUMS("Cost Amount (Expected)");
TotalCostValue := ValueEntry."Cost Amount (Actual)"+ValueEntry."Cost Amount (Expected)";
END;

InventoryPostingGroup.RESET;
InventoryPostingGroup.SETRANGE(Code, ValueEntry."Inventory Posting Group");
IF InventoryPostingGroup.FINDFIRST THEN BEGIN
Mail.AppendBody('<td align="center">'+FORMAT(InventoryPostingGroup.Description)+'</td>');
END;

Mail.AppendBody('<td align="center">'+FORMAT(ValueEntry."Global Dimension 1 Code")+'</td>');
Mail.AppendBody('<td align="right">'+FORMAT(TotalCostValue)+'</td>');
Mail.AppendBody('</tr>');

Mail.AppendBody('</table>');
Mail.AppendBody('<br>');
Mail.AppendBody('<b>From</b>');
Mail.AppendBody('<br>');
Mail.AppendBody('NAVISION (ERP) System');
Mail.AppendBody('<HR>');
Mail.AppendBody('This is a system generated mail. Please do not reply to this email ID.');
Mail.Send;

*This post is locked for comments

  • manish.yadav Profile Picture
    manish.yadav 1,835 on at
    RE: Total sum problem

    let me try and will give you the feedback on tomorrow.

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,162 Most Valuable Professional on at
    RE: Total sum problem

    To loop for each InventoryPostingGroup record, you need to use FINDSET. I don't understand what you want to do with your above code, but I can imagine there are logical errors:

    1) You sum the first retrieved Value Entry

    2) You filter a single InventoryPostingGroup record by filtering for ValueEntry."Inventory Posting Group" value.

    It's quite obvious that you have only one record with this code.

    Maybe you want to do something like this?

    InventoryPostingGroup.RESET;

    InventoryPostingGroup.SETRANGE(Code, ValueEntry."Inventory Posting Group");

    IF InventoryPostingGroup.FINDSET THEN //<-- HERE we loop through each Inventory Posting Group record

    REPEAT

     ValueEntry.RESET;

     ValueEntry."SETRANGE(Inventory Posting Group", InventoryPostingGroup.Code);  //<-- Here you select ALL the Value Entry record for this Inventory Posting Group

     ValueEntry.SETFILTER(ValueEntry."Posting Date",'%1..%2',0D,TODAY);

     IF ValueEntry.FINDFIRST THEN BEGIN

       ValueEntry.CALCSUMS("Cost Amount (Actual)");

       ValueEntry.CALCSUMS("Cost Amount (Expected)");

       TotalCostValue := ValueEntry."Cost Amount (Actual)"+ValueEntry."Cost Amount (Expected)";

     END;

    //here you have the totals for an Inventory Posting Group, so do what you need...

    UNTIL InventoryPostingGroup.NEXT=0;

  • manish.yadav Profile Picture
    manish.yadav 1,835 on at
    RE: Total sum problem

    Thank you for fast reply.

    I have try with FIND('-') and findset...but always comes only 1 row. But I have many lines.

    Inventory Posting Group Global Dimension 1 Code           Value
    FG 202 747021.8
    SFG 101 14305374.67
    TRD 201 -374438
    RM 102 56010442.72
    CS 202 0
    PM 103 48675
    SFG 102 10582328.26
    CS 101 -6980705.67
    OP 102 442852.56
    FG 101 210438276.7
    OP 101 26595735.62
    RM 101 387677390.2
    FG 102 79847791.34
    PM 101 1487799.39

    the upper output I have develop through the sql server. 

    select [Inventory Posting Group],[Global Dimension 1 Code],SUM([Cost Amount (Actual)]+[Cost Amount (Expected)]) from [ABC Private Limited$Value Entry]
    Group by [Inventory Posting Group],[Global Dimension 1 Code]
    having [Global Dimension 1 Code]<>''

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,162 Most Valuable Professional on at
    RE: Total sum problem

    You're using FINDFIRST so you retrieve only 1 record here.

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

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,240 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans