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

Community site session details

Session Id :
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,893

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

I have the same question (0)
  • Verified answer
    Mohana Yadav Profile Picture
    Mohana Yadav 60,961 Super User 2025 Season 2 on at
    RE: How to show a single line for every expired item

    As per your code it will go through each line of ILE and add to mail body.

    You can add Item loop before ILE loop and show the 1st record only.

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

    Agreed with Mohana.

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

    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
    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>');
    UNTIL Item.NEXT = 0
    END;

    This code will cater your requirement.

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

    Dear Sir,

    Now, I am trying with your suggestion but when try to run, getting the below error. I have attached the screenshots.

    Manish

    3173.UU.png

  • 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?

  • 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
    Amol Salvi Profile Picture
    Amol Salvi 18,698 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

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

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

  • 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;

  • 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;

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

    How to define the temporary variable in NAV 2013.

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 experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

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

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

#1
Saurav.Dhyani Profile Picture

Saurav.Dhyani 2 Super User 2025 Season 2

#2
RK-25090803-0 Profile Picture

RK-25090803-0 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans