Tuesday, August 4, 2020

Business Central --> Sent Email with Attachment of Report (using report selection of RDLC , Word , Customer Layout anything)

Hello Friends,

You can create customized email sending functionality in business central and attach standard document report from report selection with RDLC, Word and any custom layout

See the following example of sending Posted Sales Invoice email to client.

    procedure EmailSalesInvoice_gFnc(SalesInvoiceHeader_iRec: 
Record "Sales Invoice Header"): Boolean
    var

        Customer: Record Customer;
        CompanyInfo: Record "Company Information";
        EmailInStream: InStream;
        EmailOutStream: OutStream;
        tempBlob: Codeunit "Temp Blob";
        receipent: List of [Text];
        ShipmentNo: Code[20];
        SalesShipmentHeader: Record "Sales Shipment Header";
    begin
        Clear(Customer);
        Customer.Get(SalesInvoiceHeader_iRec."Sell-to Customer No.");
        if Customer."E-Mail" = '' then
            exit;

        Clear(CompanyInfo);
        CompanyInfo.Get();

        SMTPMailSetup.Get();
        SMTPMailSetup.TestField("User ID");

        Clear(receipent);
        receipent.Add(Customer."E-Mail");

        Clear(SMTPMail);
        SMTPMail.CreateMessage(CompanyInfo.Name, SMTPMailSetup."User ID", receipent,
 'Sales Invoice - ' + SalesInvoiceHeader_iRec."No.", '', true);

        SMTPMail.AppendBody('<html>');
        SMTPMail.AppendBody('<head>');
        SMTPMail.AppendBody('<meta charset="utf-8">');
        SMTPMail.AppendBody('<title>A simple, clean, and responsive HTML
 invoice template</title>');

        SMTPMail.AppendBody('<style>');
        SMTPMail.AppendBody('.invoice-box {');
        SMTPMail.AppendBody('max-width: 800px;');
        SMTPMail.AppendBody('margin: auto;');
        SMTPMail.AppendBody('padding: 30px;');
        SMTPMail.AppendBody('border: 1px solid #eee;');
        SMTPMail.AppendBody('box-shadow: 0 0 10px rgba(0, 0, 0, .15);');
        SMTPMail.AppendBody('font-size: 16px;');
        SMTPMail.AppendBody('line-height: 24px;');
        SMTPMail.AppendBody('font-family: ''Helvetica Neue'', ''Helvetica'',
 Helvetica, Arial, sans-serif;');
        SMTPMail.AppendBody('color: #555;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table {');
        SMTPMail.AppendBody('width: 100%;');
        SMTPMail.AppendBody('line-height: inherit;');
        SMTPMail.AppendBody('text-align: left;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table td {');
        SMTPMail.AppendBody('padding: 5px;');
        SMTPMail.AppendBody('vertical-align: top;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table tr td:nth-child(2) {');
        SMTPMail.AppendBody('text-align: right;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table tr.top table td {');
        SMTPMail.AppendBody('padding-bottom: 20px;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table tr.top table td.title {');
        SMTPMail.AppendBody('font-size: 45px;');
        SMTPMail.AppendBody('line-height: 45px;');
        SMTPMail.AppendBody('color: #333;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table tr.information table td {');
        SMTPMail.AppendBody('padding-bottom: 40px;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table tr.heading td {');
        SMTPMail.AppendBody('background: #eee;');
        SMTPMail.AppendBody('border-bottom: 1px solid #ddd;');
        SMTPMail.AppendBody('font-weight: bold;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table tr.details td {');
        SMTPMail.AppendBody('padding-bottom: 20px;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table tr.item td{');
        SMTPMail.AppendBody('border-bottom: 1px solid #eee;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table tr.item.last td {');
        SMTPMail.AppendBody('border-bottom: none;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table tr.total td:nth-child(2) {');
        SMTPMail.AppendBody('border-top: 2px solid #eee;');
        SMTPMail.AppendBody('font-weight: bold;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('@media only screen and (max-width: 600px) {');
        SMTPMail.AppendBody('.invoice-box table tr.top table td {');
        SMTPMail.AppendBody('width: 100%;');
        SMTPMail.AppendBody('display: block;');
        SMTPMail.AppendBody('text-align: center;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.invoice-box table tr.information table td {');
        SMTPMail.AppendBody('width: 100%;');
        SMTPMail.AppendBody('display: block;');
        SMTPMail.AppendBody('text-align: center;');
        SMTPMail.AppendBody('}');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('/** RTL **/');
        SMTPMail.AppendBody('.rtl {');
        SMTPMail.AppendBody('direction: rtl;');
        SMTPMail.AppendBody('font-family: Tahoma, ''Helvetica Neue'', ''Helvetica'',
 Helvetica, Arial, sans-serif;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.rtl table {');
        SMTPMail.AppendBody('text-align: right;');
        SMTPMail.AppendBody('}');

        SMTPMail.AppendBody('.rtl table tr td:nth-child(2) {');
        SMTPMail.AppendBody('text-align: left;');
        SMTPMail.AppendBody('}');
        SMTPMail.AppendBody('</style>');
        SMTPMail.AppendBody('</head>');

        SMTPMail.AppendBody('<body>');
        SMTPMail.AppendBody('<div class="invoice-box">');
        SMTPMail.AppendBody('<table cellpadding="0" cellspacing="0">');
        SMTPMail.AppendBody('<tr class="top">');
        SMTPMail.AppendBody('<td colspan="2">');
        SMTPMail.AppendBody('<table>');
        SMTPMail.AppendBody('<tr>');
        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody('');
        SMTPMail.AppendBody('</td>');

        SMTPMail.AppendBody('<td class="title">');
        SMTPMail.AppendBody('<b><font size = "5">Sales Invoice</font></b><br>');
        SMTPMail.AppendBody('</td>');
        SMTPMail.AppendBody('</tr>');
        SMTPMail.AppendBody('</table>');
        SMTPMail.AppendBody('</td>');
        SMTPMail.AppendBody('</tr>');

        SMTPMail.AppendBody('<tr class="information">');
        SMTPMail.AppendBody('<td colspan="2">');
        SMTPMail.AppendBody('<table>');
        SMTPMail.AppendBody('<tr>');
        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody(CompanyInfo.Name + '<br>');
        SMTPMail.AppendBody(CompanyInfo.Address + '<br>');
        SMTPMail.AppendBody(CompanyInfo."Address 2" + '<br>');
        SMTPMail.AppendBody(CompanyInfo.City + '<br>');
        SMTPMail.AppendBody(CompanyInfo."Post Code" + '<br>');
        SMTPMail.AppendBody(CompanyInfo."Phone No." + '<br>');
        SMTPMail.AppendBody('</td>');

        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody(CompanyInfo."Contact Person" + '<br>');
        SMTPMail.AppendBody(CompanyInfo."E-Mail" + '<br>');
        SMTPMail.AppendBody('Created Date:' + Format(CurrentDateTime()) + '<br>');
        SMTPMail.AppendBody('</td>');
        SMTPMail.AppendBody('</tr>');
        SMTPMail.AppendBody('</table>');
        SMTPMail.AppendBody('</td>');
        SMTPMail.AppendBody('</tr>');

        SMTPMail.AppendBody('<tr class="heading">');
        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody('Account No.');
        SMTPMail.AppendBody('</td>');

        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody(Customer."No.");
        SMTPMail.AppendBody('</td>');
        SMTPMail.AppendBody('</tr>');

        SMTPMail.AppendBody('<tr class="details">');
        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody('Name');
        SMTPMail.AppendBody('</td>');

        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody(Customer.Name);
        SMTPMail.AppendBody('</td>');
        SMTPMail.AppendBody('</tr>');

        SMTPMail.AppendBody('<tr class="heading">');
        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody('Details:');
        SMTPMail.AppendBody('</td>');

        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody('');
        SMTPMail.AppendBody('</td>');
        SMTPMail.AppendBody('</tr>');

        SMTPMail.AppendBody('<tr class="item">');
        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody('Document No.');
        SMTPMail.AppendBody('</td>');

        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody(SalesInvoiceHeader_iRec."No.");
        SMTPMail.AppendBody('</td>');
        SMTPMail.AppendBody('</tr>');

        SMTPMail.AppendBody('<tr class="item">');
        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody('Order Date');
        SMTPMail.AppendBody('</td>');

        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody(format(SalesInvoiceHeader_iRec."Order Date"));
        SMTPMail.AppendBody('</td>');
        SMTPMail.AppendBody('</tr>');

        SMTPMail.AppendBody('<tr class="item last">');
        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody('');
        SMTPMail.AppendBody('</td>');

        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody('</td>');
        SMTPMail.AppendBody('</tr>');

        SMTPMail.AppendBody('<tr class="heading">');
        SMTPMail.AppendBody('<td>');
        SMTPMail.AppendBody('Total');
        SMTPMail.AppendBody('</td>');

        SMTPMail.AppendBody('<td>');
        SalesInvoiceHeader_iRec.CalcFields("Amount Including VAT");
        SMTPMail.AppendBody(format(SalesInvoiceHeader_iRec."Amount Including VAT"));
        SMTPMail.AppendBody('</td>');
        SMTPMail.AppendBody('</tr>');

        SMTPMail.AppendBody('</table>');
        SMTPMail.AppendBody('</div>');
        SMTPMail.AppendBody('</body>');
        SMTPMail.AppendBody('</html>');
        SMTPMail.AppendBody('<br><br>Kindly go through attached Document for more 
details.');
        SMTPMail.AppendBody('<br><br>Regards,<br><b>');
        SMTPMail.AppendBody(CompanyInfo."Contact Person" + '<br>');
        SMTPMail.AppendBody(CompanyInfo.Name + '<br>');
        SMTPMail.AppendBody(CompanyInfo.Address + '<br>');
        SMTPMail.AppendBody(CompanyInfo."Address 2" + '<br>');
        SMTPMail.AppendBody(CompanyInfo.City + ',');
        SMTPMail.AppendBody(CompanyInfo."Post Code" + '<br>');
        SMTPMail.AppendBody(CompanyInfo."Phone No." + '<br>');
        SMTPMail.AppendBody(CompanyInfo."E-Mail" + '<br></b>');


        PostedInvoice(SalesInvoiceHeader_iRec);
       


        if SMTPMail.Send() then begin
            exit(true);
        end;
    end;

    procedure PostedInvoice(var SalesInvHeader_iRec: Record "Sales Invoice Header")
    var
        TempBlob_lCdu: Codeunit "Temp Blob";
        Out: OutStream;
        Instr: InStream;
        RecRef: RecordRef;
        FileManagement_lCdu: Codeunit "File Management";
        ReportSelection_lRec: Record "Report Selections";
        ReportID: Integer;
        SalesInvHeader_lRec: Record "Sales Invoice Header";
        MyPath: Text;
        ReprotLayoutSelection_lRec: Record "Report Layout Selection";
        CustomReportLayout_lRec: Record "Custom Report Layout";
    begin
        SalesInvHeader_lRec.Reset;
        SalesInvHeader_lRec.SetFilter("No.", SalesInvHeader_iRec."No.");
        SalesInvHeader_lRec.FindFirst;

        ReportSelection_lRec.reset;
        ReportSelection_lRec.SETRANGE(Usage, ReportSelection_lRec.Usage::"S.Invoice");
        IF ReportSelection_lRec.FindFirst then begin
            ReportID := ReportSelection_lRec."Report ID";
        end;

        IF ReportID > 0 then begin
            ReprotLayoutSelection_lRec.Reset;
            ReprotLayoutSelection_lRec.SetRange("Report ID", ReportID);
            if ReprotLayoutSelection_lRec.FindFirst then begin
                case ReprotLayoutSelection_lRec.Type of
                    ReprotLayoutSelection_lRec.Type::"RDLC (built-in)":
                        begin
                            TempBlob_lCdu.CreateOutStream(Out);
                            RecRef.GetTable(SalesInvHeader_lRec);
                            REPORT.SaveAs(ReportID, '', REPORTFORMAT::Pdf, Out, RecRef);
                            TempBlob_lCdu.CREATEINSTREAM(Instr);
                            MyPath := STRSUBSTNO('%2_%1.pdf', 
SalesInvHeader_lRec."No.", 'Posted Sales Invoice');
                            SMTPMail.AddAttachmentStream(Instr, MyPath);
                        end;
                    ReprotLayoutSelection_lRec.Type::"Word (built-in)":
                        begin
                            TempBlob_lCdu.CreateOutStream(Out);
                            RecRef.GetTable(SalesInvHeader_lRec);
                            REPORT.SaveAs(ReportID, '', REPORTFORMAT::Word, Out, 
RecRef);
                            TempBlob_lCdu.CREATEINSTREAM(Instr);
                            MyPath := STRSUBSTNO('%2_%1.docx', 
SalesInvHeader_lRec."No.", 'Posted Sales Invoice');
                            SMTPMail.AddAttachmentStream(Instr, MyPath);
                        end;
                    ReprotLayoutSelection_lRec.Type::"Custom Layout":
                        begin
                            ReprotLayoutSelection_lRec.CalcFields("Report Layout Description");
                            CustomReportLayout_lRec.Reset();
                            CustomReportLayout_lRec.SetRange(Code,
 ReprotLayoutSelection_lRec."Custom Report Layout Code");
                            if CustomReportLayout_lRec.FindFirst then begin
                                case CustomReportLayout_lRec.Type OF
                                    CustomReportLayout_lRec.Type::RDLC:
                                        begin
                                            TempBlob_lCdu.CreateOutStream(Out);
                                            RecRef.GetTable(SalesInvHeader_lRec);
                                            REPORT.SaveAs(ReportID, '', 
REPORTFORMAT::Pdf, Out, RecRef);
                                            TempBlob_lCdu.CREATEINSTREAM(Instr);
                                            MyPath := STRSUBSTNO('%2_%1.pdf',
 SalesInvHeader_lRec."No.", 'Posted Sales Invoice');
                                            SMTPMail.AddAttachmentStream(Instr, MyPath);
                                        end;
                                    CustomReportLayout_lRec.Type::Word:
                                        begin
                                            TempBlob_lCdu.CreateOutStream(Out);
                                            RecRef.GetTable(SalesInvHeader_lRec);
                                            REPORT.SaveAs(ReportID, '', REPORTFORMAT::Word, Out, RecRef);
                                            TempBlob_lCdu.CREATEINSTREAM(Instr);
                                            MyPath := STRSUBSTNO('%2_%1.docx',
 SalesInvHeader_lRec."No.", 'Posted Sales Invoice');
                                            SMTPMail.AddAttachmentStream(Instr, MyPath);
                                        end
                                end
                            END
                        end;
                end;
            END ELSE begin
                TempBlob_lCdu.CreateOutStream(Out);
                RecRef.GetTable(SalesInvHeader_lRec);
                REPORT.SaveAs(ReportID, '', REPORTFORMAT::Pdf, Out, RecRef);
                TempBlob_lCdu.CREATEINSTREAM(Instr);
                MyPath := STRSUBSTNO('%2_%1.pdf', 
SalesInvHeader_lRec."No.", 'Posted Sales Invoice');
                SMTPMail.AddAttachmentStream(Instr, MyPath);
            end;

        end

    end;




Sample output of this email.




















I hope this help someone for email functionality development.

Please write the comment for any query

Keep Sharing....Keep growing.....


Posted by at No comments:
Labels:

Sunday, July 26, 2020

OData Web Services - Filter Option

Hello Friends

NAV/BC OData v4 service can easily accessbile any external system provide the result in json format.

Method for apply different types of filter:

 Filter Expressions

 To add a filter to an OData URI, add $filter= to the end of the name of the published web service.

 For example, the following URI filters the City field in the Customer page to return all customers who are located in “Milano”:
Example: (Access with USER\SERVICE KEY)
Customer > Published Odata Service
https://api.businesscentral.dynamics.com/v2.0/tenant-ID/Production/ODataV4/Company(‘CompanyName’)/Customer?$filter=City%20eq%20%27Milano%27

 Available Filters
The following table shows the filters that are supported in Business Central OData web services and the equivalent C/AL filter expressions.

 All examples are based either on page 21, Customer (published as Customer), or on page 20, General Ledger Entry (published as GLEntry).
Filter Expressions
DefinitionExample and explanationC/AL exp.
Select a range of valuesfilter=Entry_No gt 610 and Entry_No lt 615
Query on GLEntry service. Returns entry numbers 611 through 614.
..
Andfilter=Country_Region_Code eq ‘ES’ and Payment_Terms_Code eq ’14 DAYS’
Query on Customer service. Returns customers in Spain where Payment_Terms_Code=14 DAYS.
&
Orfilter= Country_Region_Code eq ‘ES’ or Country_Region_Code eq ‘US’
Query on Customer service. Returns customers in Spain and the United States.
Alert: You can use OR operators to apply different filters on the same field. However, you cannot use OR operators to apply filters on two different fields.
|
Less thanfilter=Entry_No lt 610
Query on GLEntry service. Returns entry numbers that are less than 610.
<
Greater thanfilter= Entry_No gt 610
Query on GLEntry service. Returns entry numbers 611 and higher.
>
Greater than or equal tofilter=Entry_No ge 610
Query on GLEntry service. Returns entry numbers 610 and higher.
>=
Less than or equal tofilter=Entry_No le 610
Query on GLEntry service. Returns entry numbers up to and including 610.
<=
Different from (not equal)filter=VAT_Bus_Posting_Group ne ‘EXPORT’
Query on Customer service. Returns all customers with VAT_Bus_Posting_Group not equal to EXPORT.
<>
endswithfilter=endswith(VAT_Bus_Posting_Group,’RT’)
Query on Customer service. Returns all customers with VAT_Bus_Posting_Group values that end in RT.
*
startswithfilter=startswith(Name, ‘S’)
Query on Customer service. Returns all customers names beginning with “S”.
substringoffilter=substringof(Name, ‘urn’)
Query on Customer service. Returns customer records for customers with names containing the string “urn”.
indexoffilter=indexof(Location_Code, ‘BLUE’) eq 0
Query on Customer service. Returns customer records for customers having a location code beginning with the string BLUE.
replacefilter=replace(City, ‘Miami’, ‘Tampa’) eq ‘CODERED’
substringfilter=substring(Location_Code, 5) eq ‘RED’
Query on Customer service. Returns true for customers with the string RED in their location code starting as position 5.
tolowerfilter=tolower(Location_Code) eq ‘code red’
toupperfilter=toupper(FText) eq ‘2ND ROW’
trimfilter=trim(FCode) eq ‘CODE RED’
concatfilter=concat(concat(FText, ‘, ‘), FCode) eq ‘2nd row, CODE RED’
roundfilter=round(FDecimal) eq 1
floorfilter=floor(FDecimal) eq 0
ceilingfilter=ceiling(FDecimal) eq 1

Referencing Different Data Types in Filter Expressions
You must use the appropriate notation for different data types with filter expressions.
  • String values must be delimited by single quotation marks.
  • Numeric values require no delimiters.
Known Limitations
Filters
You can specify filters in OData web services in general that are not supported in Business Central , such as using an OR operator to filter on “two different fields.
In those cases, you will see the following error:
“An error occurred while processing this request. The ‘OR’ operator is not supported on distinct fields on an OData filter”
Lambda operators
Lambda operators are not supported by Business Central OData APIs. If lambda operators are used, the filter expression will be ignored.
Deep insert
Business Central supports deep insert, but not deep patching. Multiple requests will need to be issued when patching nested entities.
Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)