Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / How to show a single l...
Microsoft Dynamics NAV (Archived)

How to show a single line for every expired item

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

1440.UU.png

Hello All,

I am trying to get the expired item contents from Item ledger entry. I have manually trace the data filter through using 'Expiration date'..but it is showing the multiple line for single item...Is there any way we can see the distinct filter item..I mean I want to show in my mail body a single line for any item..
You can understand the actual issue with my screenshots.

Manish

I have developed the below code:


SMTPMAIL.CreateMessage('MKU Limited','nav@mku.com','manish.yadav@mku.com','ILE Current Item Expiry Statistics','',TRUE);
SMTPMAIL.AppendBody('Dear Sir / Madam,');
SMTPMAIL.AppendBody('<br>');
SMTPMAIL.AppendBody('Please Find The Item Ledger Details:');
SMTPMAIL.AppendBody('<br>'); //new
SMTPMAIL.AppendBody('<HR>');
SMTPMAIL.AppendBody('<table border="1">');
SMTPMAIL.AppendBody('<tr>');

SMTPMAIL.AppendBody('<th>Item No.</th>');
SMTPMAIL.AppendBody('<th>Description</th>');
SMTPMAIL.AppendBody('<th>Unit of Measure</th>');
SMTPMAIL.AppendBody('<th>Expiry Date</th>');
SMTPMAIL.AppendBody('<th>Item Category</th>');
SMTPMAIL.AppendBody('<th>Location</th>');
SMTPMAIL.AppendBody('<th>Lot No</th>');
SMTPMAIL.AppendBody('<th>Unit</th>');
SMTPMAIL.AppendBody('</tr>');

//ItemLedgerEntry.SETRANGE("Expiration Date",(CALCDATE('<-WD2>', TODAY)));
ItemLedgerEntry.SETRANGE("Expiration Date", TODAY);
IF ItemLedgerEntry.FINDFIRST THEN
BEGIN
REPEAT
SMTPMAIL.AppendBody('<tr>');
SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Item No.")+'</td>');

recItem.GET(ItemLedgerEntry."Item No.");
SMTPMAIL.AppendBody('<td>'+recItem.Description+'</td>');

SMTPMAIL.AppendBody('<td align="center">'+FORMAT(ItemLedgerEntry."Unit of Measure Code")+'</td>');
SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Expiration Date")+'</td>');
ItemCategory.RESET;

ItemCategory.SETRANGE(Code,ItemLedgerEntry."Item Category Code");
IF ItemCategory.FINDFIRST THEN BEGIN
SMTPMAIL.AppendBody('<td>'+FORMAT(ItemCategory.Description)+'</td>');
END;

SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Location Code")+'</td>');
SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Lot No.")+'</td>');
SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Global Dimension 1 Code")+'</td>');
SMTPMAIL.AppendBody('</tr>');
UNTIL(ItemLedgerEntry.NEXT=0);
END;

SMTPMAIL.AppendBody('</table>');
SMTPMAIL.AppendBody('<br>');
SMTPMAIL.AppendBody('<HR>');
SMTPMAIL.AppendBody('<br><br>');
SMTPMAIL.AppendBody('Regards');
SMTPMAIL.AppendBody('<br>');
SMTPMAIL.AppendBody('MKU Stock Inventory Department');
SMTPMAIL.AppendBody(' ');
SMTPMAIL.AppendBody('<br><br>');
SMTPMAIL.Send;

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to show a single line for every expired item

    I am curious after reading this article...where does the data from the expiration date field in item ledger come from?  The purchase invoice?

    Thanks,

    Debbie Greene

  • manish.yadav Profile Picture
    manish.yadav 1,835 on at
    RE: How to show a single line for every expired item

    thank you Amol Sir. I have done it.

    manish

  • Amol Salvi Profile Picture
    Amol Salvi 18,694 on at
    RE: How to show a single line for every expired item

    How to define a temporary table:

    1). Click on Tools >> Object Designer>> create a new table.

    2). Then go to View >> C/AL Globals or C/AL Locals. If you  have selected   C/AL Globals, the C/AL Globals window appears.

    Definig a temporary table variable

    3). Write a name for the temporary table variable, and select Record as the data type ,Select the desired table in Subtype field.

    4). Select your variable line and click View>> Properties, It display the Properties window.

    Changing property

    5). Make the value of the Temporary property to Yes.

  • manish.yadav Profile Picture
    manish.yadav 1,835 on at
    RE: How to show a single line for every expired item

    How to define the temporary variable in NAV 2013.

  • Binesh Profile Picture
    Binesh 7,885 on at
    RE: How to show a single line for every expired item

    Hello Manish,

    Check your code, we are not aware about your code, but according to screenshot you want to skip the same item line. for that i wrote the bellow solution try this.

    You have to declare one ItemNoL local variable of type code size 20 or your itemNo size.

    SMTPMAIL.CreateMessage('MKU Limited','nav@mku.com','manish.yadav@mku.com','ILE Current Item Expiry Statistics','',TRUE);
    SMTPMAIL.AppendBody('Dear Sir / Madam,');
    SMTPMAIL.AppendBody('<br>');
    SMTPMAIL.AppendBody('Please Find The Item Ledger Details:');
    SMTPMAIL.AppendBody('<br>'); //new
    SMTPMAIL.AppendBody('<HR>');
    SMTPMAIL.AppendBody('<table border="1">');
    SMTPMAIL.AppendBody('<tr>');
    SMTPMAIL.AppendBody('<th>Item No.</th>');
    SMTPMAIL.AppendBody('<th>Description</th>');
    SMTPMAIL.AppendBody('<th>Unit of Measure</th>');
    SMTPMAIL.AppendBody('<th>Expiry Date</th>');
    SMTPMAIL.AppendBody('<th>Item Category</th>');
    SMTPMAIL.AppendBody('<th>Location</th>');
    SMTPMAIL.AppendBody('<th>Lot No</th>');
    SMTPMAIL.AppendBody('<th>Unit</th>');
    SMTPMAIL.AppendBody('</tr>');
    //ItemLedgerEntry.SETRANGE("Expiration Date",(CALCDATE('<-WD2>', TODAY)));
    //IF ItemLedgerEntry.FINDFIRST THEN BEGIN
    //REPEAT
    Item.RESET;
    Item.SETCURRENTKEY("No.");
    Item.SETRANGE("No.");
    IF Item.FIND('-') THEN BEGIN
    REPEAT
    ItemLedgerEntry.SETRANGE("Expiration Date", TODAY);
    ItemLedgerEntry.SETRANGE("Item No.", Item."No.");
    IF ItemLedgerEntry.FIND('-') THEN
    REPEAT
    IF ItemLedgerEntry."Item No." <> ItemNoL THEN BEGIN
    ItemNoL := ItemLedgerEntry."Item No.";
    SMTPMAIL.AppendBody('<tr>');
    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Item No.")+'</td>');
    Item.GET(ItemLedgerEntry."Item No.");
    SMTPMAIL.AppendBody('<td>'+Item.Description+'</td>');
    SMTPMAIL.AppendBody('<td align="center">'+FORMAT(ItemLedgerEntry."Unit of Measure Code")+'</td>');
    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Expiration Date")+'</td>');
    ItemCategory.RESET;
    ItemCategory.SETRANGE(Code,ItemLedgerEntry."Item Category Code");
    IF ItemCategory.FINDFIRST THEN BEGIN
    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemCategory.Description)+'</td>');
    END;
    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Location Code")+'</td>');
    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Lot No.")+'</td>');
    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Global Dimension 1 Code")+'</td>');
    SMTPMAIL.AppendBody('</tr>');
    END;
    UNTIL ItemLedgerEntry.NEXT=0;
    UNTIL Item.NEXT = 0;
    END;
    SMTPMAIL.AppendBody('</table>');
    SMTPMAIL.AppendBody('<br>');
    SMTPMAIL.AppendBody('<HR>');
    SMTPMAIL.AppendBody('<br><br>');
    SMTPMAIL.AppendBody('Regards');
    SMTPMAIL.AppendBody('<br>');
    SMTPMAIL.AppendBody('MKU Stock Inventory Department');
    SMTPMAIL.AppendBody(' ');
    SMTPMAIL.AppendBody('<br><br>');
    SMTPMAIL.Send;

  • Andrea Antoniazzi Profile Picture
    Andrea Antoniazzi 285 on at
    RE: How to show a single line for every expired item

    Hi,

    I think you can try this solution

    the TMPItem must be a TEMPORARY Variable!

    You can fin my code with //***

    SMTPMAIL.CreateMessage('MKU Limited','nav@mku.com','manish.yadav@mku.com','ILE Current Item Expiry Statistics','',TRUE);

    SMTPMAIL.AppendBody('Dear Sir / Madam,');

    SMTPMAIL.AppendBody('<br>');

    SMTPMAIL.AppendBody('Please Find The Item Ledger Details:');

    SMTPMAIL.AppendBody('<br>'); //new

    SMTPMAIL.AppendBody('<HR>');

    SMTPMAIL.AppendBody('<table border="1">');

    SMTPMAIL.AppendBody('<tr>');

    SMTPMAIL.AppendBody('<th>Item No.</th>');

    SMTPMAIL.AppendBody('<th>Description</th>');

    SMTPMAIL.AppendBody('<th>Unit of Measure</th>');

    SMTPMAIL.AppendBody('<th>Expiry Date</th>');

    SMTPMAIL.AppendBody('<th>Item Category</th>');

    SMTPMAIL.AppendBody('<th>Location</th>');

    SMTPMAIL.AppendBody('<th>Lot No</th>');

    SMTPMAIL.AppendBody('<th>Unit</th>');

    SMTPMAIL.AppendBody('</tr>');

    //ItemLedgerEntry.SETRANGE("Expiration Date",(CALCDATE('<-WD2>', TODAY)));

    TMPItem.RESET;  //***

    TMPItem.DELETEALL;  //***

    ItemLedgerEntry.SETRANGE("Expiration Date", TODAY);

    IF ItemLedgerEntry.FINDFIRST THEN

    BEGIN

    REPEAT

    IF NOT TMPItem.GET(ItemLedgerEntry."Item No.") THEN BEGIN //***

    SMTPMAIL.AppendBody('<tr>');

    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Item No.")+'</td>');

    recItem.GET(ItemLedgerEntry."Item No.");

    SMTPMAIL.AppendBody('<td>'+recItem.Description+'</td>');

    SMTPMAIL.AppendBody('<td align="center">'+FORMAT(ItemLedgerEntry."Unit of Measure Code")+'</td>');

    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Expiration Date")+'</td>');

    ItemCategory.RESET;

    ItemCategory.SETRANGE(Code,ItemLedgerEntry."Item Category Code");

    IF ItemCategory.FINDFIRST THEN BEGIN

    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemCategory.Description)+'</td>');

    END;

    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Location Code")+'</td>');

    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Lot No.")+'</td>');

    SMTPMAIL.AppendBody('<td>'+FORMAT(ItemLedgerEntry."Global Dimension 1 Code")+'</td>');

    SMTPMAIL.AppendBody('</tr>');

    TMPItem.INIT; //***

    TMPItem."No." := ItemLedgerEntry."Item No.";  //***

    TMPItem.INSERT; //***

    END;  //***

    UNTIL(ItemLedgerEntry.NEXT=0);

    END;

    SMTPMAIL.AppendBody('</table>');

    SMTPMAIL.AppendBody('<br>');

    SMTPMAIL.AppendBody('<HR>');

    SMTPMAIL.AppendBody('<br><br>');

    SMTPMAIL.AppendBody('Regards');

    SMTPMAIL.AppendBody('<br>');

    SMTPMAIL.AppendBody('MKU Stock Inventory Department');

    SMTPMAIL.AppendBody(' ');

    SMTPMAIL.AppendBody('<br><br>');

    SMTPMAIL.Send;

  • manish.yadav Profile Picture
    manish.yadav 1,835 on at
    RE: How to show a single line for every expired item

    I am using the base data item as " Item Ledger Entry".

  • Suggested answer
    Amol Salvi Profile Picture
    Amol Salvi 18,694 on at
    RE: How to show a single line for every expired item

    What is base dataitem you are using in this report ?

    I mean how report getting the Item No for searching in ILE

  • Suggested answer
    Nareshwar Raju Vaneshwar Profile Picture
    Nareshwar Raju Vane... 5,596 on at
    RE: How to show a single line for every expired item

    Hi,

    Please debug to find the exact issue. Since there are two or more possibilities, it is hard to judge. Hope it helps.

  • Suggested answer
    Alexander Ermakov Profile Picture
    Alexander Ermakov 28,094 on at
    RE: How to show a single line for every expired item

    What is the business task you have? Do I understand correctly that your task is actually to show one item per line, with the oldest expiration date? Or, do you need just to show the total quantity of each item that already expired, regardless the date of expiration?

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans