You’re offline. This is a read only version of the page.
1,835
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
Report should come the below format:
*This post is locked for comments
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
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;
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;
Item ledger entry.
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.
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.
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.
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (