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 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
thank you Amol Sir. I have done it.
manish
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.
How to define the temporary variable in NAV 2013.
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;
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;
I am using the base data item as " Item Ledger Entry".
What is base dataitem you are using in this report ?
I mean how report getting the Item No for searching in ILE
Hi,
Please debug to find the exact issue. Since there are two or more possibilities, it is hard to judge. Hope it helps.
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?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,219 Super User 2024 Season 2
Martin Dráb 230,056 Most Valuable Professional
nmaenpaa 101,156