Monday, July 8, 2019

File Handling Part 2 in Business Central SaaS

Hello readers,
Today I'm going to continue on file handling in business central SaaS to cover the  remaining functions as shown in below screenshot.  

Export to Excel, see navision to business central little bit changed on creating the Book. 


In my demo i used all the standard code and table so it is simple for all. 

See for creating the excel book mostly used function is
Table370.CreateAndOpenBook(parameters...)

But unfortunately this function is discontinued in Business Central SaaS. 

Don't worry we can customize little bit as below. 

Create a Procedure CreateExcelBook()

In this function again call the standard functions from table 370 as shown below. 

local procedure CreateExcelBook()
  begin
  TempExcelBuffer.CreateNewBook('Customers');
    TempExcelBuffer.WriteSheet('Customers', CompanyName(), UserId());
    TempExcelBuffer.CloseBook();
    TempExcelBuffer.OpenExcel();
  end;

Note: for full code see in the bottom i have attached a report for exporting. 


//>>..........................................<<\\
SaveAs Report in PDF/Excel ....

Report.saveaspdf is not working in business central SaaS, but we can achieve this by Report.SaveAs function. 

This function takes parameters, like
Report format, Stream and so on. 

See the below code for saving the report in pdf format. 

action("Download Customer - Sales List")
                {
                    ApplicationArea = All;
                    Caption = 'Download Customer - Sales List (PDF)';
                    Image = ExportAttachment;
                    Promoted = true;
                    PromotedCategory = Process;
                    PromotedIsBig = true;
                    trigger OnAction()
                    var
                        NAVOutStream: OutStream;
                        Recref: RecordRef;
                        FileMgt: Codeunit "File Management";
                        TempBlob: Record TempBlob temporary;
                        CustomerRec: Record Customer;
                    begin
                        TempBlob.Blob.CreateOutStream(NAVOutStream);
                        CustomerRec.Reset();
                        CustomerRec.SetRange("No.", '10000', '50000');
                        IF CustomerRec.FindSet() then begin
                            Recref.GetTable(CustomerRec);
                            if Report.SaveAs(119, '', ReportFormat::Pdf, NAVOutStream, Recref) then
                                FileMgt.BLOBExport(TempBlob, 'CustomerSalesList.pdf', true)
                            else
                                Message('unable to download the report.');
                        end;
                    end;
                }

//>>.....................<<\\
Sending Report Attachments 


action("Send Customer - Sales List")
                {
                    ApplicationArea = All;
                    Caption = 'Send Customer - Sales List (PDF)';
                    Image = SendEmailPDF;
                    Promoted = true;
                    PromotedCategory = Process;
                    PromotedIsBig = true;
                    trigger OnAction()
                    var
                        NAVOutStream: OutStream;
                        NAVInStream: InStream;
                        Recref: RecordRef;
                        TempBlob: Record TempBlob temporary;
                        SMTPMail: Codeunit "SMTP Mail";
                        SMTPMailSetup: Record "SMTP Mail Setup";
                        CustL: Record Customer;
                        FileName: Text;
                    begin
                        CustL.Reset();
                        CustL.SetRange("No.", "No.");
                        if CustL.FindFirst() then;
                        FileName := StrSubstNo('Customer_%1_SalesList.pdf', "No.");
                        TempBlob.Blob.CreateOutStream(NAVOutStream);
                        Recref.GetTable(CustL);
                        Report.SaveAs(119, '', ReportFormat::Pdf, NAVOutStream, Recref);

                        TempBlob.Blob.CreateInStream(NAVInStream, TextEncoding::UTF8);
                        SMTPMailSetup.Get();
                        SMTPMail.CreateMessage('Binesh',
                                                SMTPMailSetup."User ID",
                                                "E-Mail",
                                                StrSubstNo('Sales List for Customer %1', "No."),
                                                StrSubstNo('Hello %1,', Name), true
                                            );

                        SMTPMail.AppendBody('<br><br>');
                        SMTPMail.AppendBody(StrSubstNo('Please find the sales list as of %1', Today));
                        SMTPMail.AppendBody('<br><br>');
                        SMTPMail.AppendBody('Thank You');
                        SMTPMail.AppendBody('<br><br>');
                        SMTPMail.AppendBody('NAV Admin');
                        SMTPMail.AppendBody('<br><br><hr>');
                        SMTPMail.AppendBody('This is a system generated mail, Please do not reply to this mail ID.');
                        SMTPMail.AppendBody('<br><br>');
                        SMTPMail.AddAttachmentStream(NAVInStream, FileName);
                        SMTPMail.Send();
                        Message('Sales List has been sent to customer %1.', Name);
                    end;
                }





//>>....................................<\\
Export into CSV Using XMLPort 
                action("Export Contact")
                {
                    ApplicationArea = All;
                    Caption = 'Export Contact using XMLPort';
                    Image = ExportContact;
                    Promoted = true;
                    PromotedCategory = Process;
                    PromotedIsBig = true;
                    trigger OnAction()
                    var
                        TmpTempBlob: Record TempBlob temporary;
                        OutStr: OutStream;
                        InStr: InStream;
                        FileName: Text;
                        ExpContact: Xmlport "Export Contact";
                    begin
                        TmpTempBlob.Blob.CreateOutStream(OutStr, TextEncoding::UTF8);
                        ExpContact.SetDestination(OutStr);
                        ExpContact.Export();
                        TmpTempBlob.Blob.CreateInStream(InStr, TextEncoding::UTF8);
                        FileName := 'ContactList.txt';
                        DownloadFromStream(InStr, 'Export', '', '', FileName);
                    end;
                }
            }


//>>..................End.....................<<\\
That's it Enjoy your Day. 
Hope you are feeling better now to add this skill in your skills. 


Below is the full code for exporting into Excel. 




4 comments:

  1. Thanks for posting such a Useful information. You done a great job.
    MS Dynamics AX Online Training

    ReplyDelete
  2. wonderful blog. Very interesting to read this blog.I would like to thank you for the efforts you had made for writing this awesome blog. Really the post is very unique.every concepts are captured nice. Thank you for sharing any good knowledge and thanks for fantastic efforts. Thank you for sharing such great information very useful to us.
    oracle training in chennai

    oracle training institute in chennai

    oracle training in bangalore

    oracle training in hyderabad

    oracle training

    hadoop training in chennai

    hadoop training in bangalore


    ReplyDelete
  3. Agradezco que este blog me brinde una comprensión especial y útil sobre este tema.

    MB-220: Microsoft Dynamics 365 Marketing

    ReplyDelete

Popular Posts