Business Central Musings

It’s funny how, with all this rush to get everything Copilot-ed, I became one of the few NAV experts in my team. And boy, there’s so much laugh when the NAV customer is casually asking: “So I want x and y to receive weekly pdfs via email with this report and that report exists, you just have to run it automatically and send the output via email. Can you do that?”. “Yes, of course”. The voice inside said, “Should be easy.”

To refresh my memory I started searching and found a few good blogs but nothing to close the task in 15-30 minutes. While it might seem backwardish (is this even a word?) I think there is value in blogging about it.

So here we are!

Yes, the reports exist, and are running with the same parameters every time.

So, will need a job queue, will pass the report filters/parameters to the “Parameters String” field on the job queue. Separate them by some separator so you don’t add more fields in the job queue entry table.

Will need a codeunit to run through these steps:

• read the Parameter String (all the fields/filters) in the request window of the report into a Text variable
• Then split the Text variable using .NET System.String and System.Array classes and drop all split values into an array.
• Pass the split values to a new global function within the report, and load these values as filters for the report, will name the global function SetParameters(…)
• Run report with RunAs ( …, ReportFormat::Pdf, …)
• Lastly, use the NAV standard SMTP Mail codeunit to send the email.

Here is the code in the codeunit:

  PROPERTIES
  {
    TableNo=472;
    OnRun=VAR
            SomeReport@1000000000 : Report 50028;
            FileName@1000000001 : Text;
            RepOStream@1000000003 : OutStream;
            RepIStream@1000000004 : InStream;
            TempBlob@1000000002 : Record 99008535;
            SMTPMail@1000000005 : Codeunit 400;
            SMTPMailSetup@1000000006 : Record 409;
          BEGIN
            // Split Param string
            IF Rec."Parameter String" <> '' THEN
              SplitParamString(Rec."Parameter String", SplitValues);
            //SplitParamString('silviuvirlan@yahoo.com;SomeItem;SomeLot;SomeLoco;SomeBin;SomeZone', SplitValues);
            SendTo := SplitValues.GetValue(0);
            Filter1 := SplitValues.GetValue(1);
            Filter2 := SplitValues.GetValue(2);
            Filter3 := SplitValues.GetValue(3);
            Filter4 :=SplitValues.GetValue(4);
            Filter5 := SplitValues.GetValue(5);

            // Run report
            SomeReport.SetParams(Filter1,Filter2,Filter3,Filter4,Filter5);
            SomeReport.USEREQUESTPAGE := FALSE;
            TempBlob.Blob.CREATEOUTSTREAM(RepOStream);
            SomeReport.SAVEAS('',REPORTFORMAT::Pdf,RepOStream);
            TempBlob.Blob.CREATEINSTREAM(RepIStream);
            FileName := STRSUBSTNO('Some report caption - %1.pdf',FORMAT(TODAY()));

            // Email
            SMTPMailSetup.GET;
            CLEAR(SMTPMail);
            SMTPMail.CreateMessage('',SMTPMailSetup."User ID",SendTo,'SomeReport caption','',TRUE);
            SMTPMail.AddAttachmentStream(RepIStream,FileName);
            SMTPMail.Send;
          END;

  }
  CODE
  {
    VAR
      Filter1@1000000000 : Text;
      Filter2@1000000001 : Text;
      Filter3@1000000002 : Text;
      Filter4@1000000003 : Text;
      Filter5@1000000004 : Text;
      SplitValues@1000000005 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array";
      SendTo@1000000006 : Text;
      TempBlob@1000000007 : Record 99008535;

    LOCAL PROCEDURE SplitParamString@1000000000(Input@1000000000 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.String";VAR SplitValues@1000000001 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array");
    VAR
      Separator@1000000002 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.String";
      i@1000000003 : Integer;
    BEGIN
      Separator := ';';
      SplitValues := Input.Split(Separator.ToCharArray());
    END;

You can also grab it from my github repository: SilviuVirlan/Run-Report-via-JQ-and-email (github.com)

Lastly, set up a job queue and run the codeunit above. That should run the report with the filters you need and send the email. Of course, you will need to setup your SMTP Mail.

For future posts and videos, join my youtube channel or just bookmark my blog site.