Saturday 31 August 2024

Streamlining Report Substitution in Business Central: A Dynamic Alternative to OnAfterSubstituteReport Code Changes

 

In Microsoft Dynamics 365 Business Central, the OnAfterSubstituteReport event is commonly used to substitute one report for another at runtime. While this event handler is powerful, it can become cumbersome when you frequently update report substitutions. For each report change, you must modify the code, which can be time-consuming, and you must wait to move the changes to the production environment.

It can grow big like the example below:

    [EventSubscriber(ObjectType::Codeunit, Codeunit::ReportManagement, 'OnAfterSubstituteReport', '', false, false)]

    local procedure OnAfterSubstituteReport(ReportId: Integer; RunMode: Option; RequestPageXml: Text; RecordRef: RecordRef; var NewReportId: Integer);

begin

        case ReportId of

            1305:

                NewReportId := 10075;

            299:

                NewReportId := 50107;

            393:

                NewReportId := 50108;

            120:

                NewReportId := 50140;

            10040:

                NewReportId := 50140;

            108:

                NewReportId := 50154;

            10048:

                NewReportId := 50141;

            5802:

                NewReportId := 50147;

            10049:

                NewReportId := 50142;

        end;

end;


An alternative approach to managing report substitutions dynamically involves creating a custom table that stores the mappings between old report IDs and new report IDs. By leveraging this table in conjunction with the OnAfterSubstituteReport event, you can simplify the process of updating report substitutions without modifying the underlying code each time.

Step 1: Create a Custom Table and Page for Report Mappings

First, create a custom table to store the mappings between old and new report IDs. This table should have at least two fields: one for the old report ID and another for the new report ID. Here’s an example:

 

table 88100 "MPA Dynamic Rep. Subs. Mapping"

{

    Caption = 'Dynamic Report Substitution Mapping';

    DataClassification = CustomerContent;

 

    fields

    {

        field(1; ReportId; Integer)

        {

            DataClassification = CustomerContent;

            TableRelation = AllObjWithCaption."Object ID" WHERE("Object Type" = CONST(Report));

            trigger OnValidate()

            begin

                Calcfields("Report Caption");

            end;

        }

        field(2; NewReportId; Integer)

        {

            DataClassification = CustomerContent;

            TableRelation = AllObjWithCaption."Object ID" WHERE("Object Type" = CONST(Report));

            trigger OnValidate()

            begin

                Calcfields("New Report Caption");

            end;

        }

        field(4; "Report Caption"; Text[250])

        {

            CalcFormula = lookup("Report Metadata".Caption where(ID = field(ReportId)));

            Caption = 'Report Name';

            Editable = false;

            FieldClass = Flowfield;

        }

        field(5; "New Report Caption"; Text[250])

        {

            CalcFormula = lookup("Report Metadata".Caption where(ID = field(NewReportId)));

            Caption = 'New Report Name';

            Editable = false;

            FieldClass = Flowfield;

        }

    }

 

    keys

    {

        key(Key1; ReportId)

        {

            Clustered = true;

        }

    }

 

}

This page will allow you to maintain a list of report substitutions that can be easily updated through the UI, without needing to touch any code.

page 88100 "MPA Dynamic Rep. Subs. Mapping"

{

    Caption = 'Dynamic Report Substitution Mappings';

    PageType = List;

    UsageCategory = Lists;

    ApplicationArea = All;

    SourceTable = "MPA Dynamic Rep. Subs. Mapping";

 

    layout

    {

        area(Content)

        {

            repeater(Group)

            {

                field(ReportId; Rec.ReportId)

                {

                    ApplicationArea = All;

                }

                field("Report Caption"; Rec."Report Caption")

                {

                    ApplicationArea = All;

                }

                field(NewReportId; Rec.NewReportId)

                {

                    ApplicationArea = All;

                }

                field("New Report Caption"; Rec."New Report Caption")

                {

                    ApplicationArea = All;

 

                }

            }

 

        }

    }

}

 

Step 2: Populate the Table with Mappings

Once the table is created, you can populate it with your report mappings. For example, if you want to replace Report 100 with Report 200, simply add an entry with Old Report ID = 100 and New Report ID = 200.



Step 3: Modify the OnAfterSubstituteReport Event

Now, modify the OnAfterSubstituteReport event to reference the custom table you created. This will enable the event to dynamically substitute reports based on the mappings stored in the table:

 

codeunit 88100 "MPA Report Management Subs"

{

 

    [EventSubscriber(ObjectType::Codeunit, Codeunit::ReportManagement, 'OnAfterSubstituteReport', '', false, false)]

    local procedure OnAfterSubstituteReport(ReportId: Integer; RunMode: Option; RequestPageXml: Text; RecordRef: RecordRef; var NewReportId: Integer);

    var

        SubstituteReports: Record "MPA Dynamic Rep. Subs. Mapping";

    begin

        if SubstituteReports.Get(ReportId) then

            NewReportId := SubstituteReports.NewReportId;

    end;

 

}

This code checks the custom table for a matching Old Report ID and, if found, assigns the corresponding New Report ID to the NewReportID parameter. This substitution happens without the need to hard-code any report IDs, making your solution much more flexible and easier to maintain.

Step 4: Manage Substitutions Dynamically

With this setup, managing report substitutions becomes straightforward. Whenever you need to substitute a report, simply update the mapping in the custom table—no code changes are required. This approach allows for quick adjustments in a production environment without the need for a new deployment.

Conclusion

Using a custom table to manage report substitutions in Business Central is a powerful alternative to hard-coding substitutions in the OnAfterSubstituteReport event. By separating the logic from the code and storing it in a table, you gain flexibility, reduce maintenance overhead, and simplify the process of updating report mappings.

 

You can find the code here

pmohanakrishna/DynamicReportSubstitutions (github.com)

 


Wednesday 29 May 2024

Resolving the DML Statement Error During the Business Central Upgrade

Upgrading to Business Central can sometimes throw unexpected errors, and one such error we recently encountered involved a DML statement issue with the 'Access Control' table. Here, we’ll walk you through the problem and how we resolved it.


The Error

During an upgrade attempt from BC14 to BC23, the Event log reported the following error related to the 'Access Control' table:

 

The target table 'BC14PROD_UPG.dbo.Access Control’ of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.




Investigation and Findings

Upon investigating the error, we discovered that the 'Access Control' table had several triggers enabled. Triggers in SQL Server are special types of stored procedures that automatically run when specific actions occur in the database.



Resolution

The upgrade process failed because the DML statement included an OUTPUT clause without an INTO clause, which conflicted with the enabled triggers. To resolve this, we deleted the triggers on the 'Access Control' table.

Steps to Resolve the Error

1.    Identify Triggers: First, identify any triggers associated with the 'Access Control' table.

2.      Delete Triggers: Next, delete the identified triggers.

3.      Re-run the Upgrade: After deleting the triggers, we re-ran the upgrade process, which completed successfully.


Conclusion

If you encounter the DML statement error during your BC upgrade, check for and remove any triggers on the associated table. This straightforward solution allowed our upgrade to proceed without further issues.

 

We hope this insight helps others facing similar upgrade challenges. If you have any questions or need further assistance, feel free to reach out in the comments below.

 

Happy upgrading!


Tuesday 12 March 2024

Page Scripting Tool in #msdyn365bc (BC24 Preview)


The Page Scripting Tool offers users a more efficient way to validate their key scenarios. By capturing user interactions and replaying them seamlessly within the UI, the tool eliminates the need for complex external testing environments, reducing both time and effort.
This simplicity not only accelerates the testing process but can be used for training and replication of bugs for developers.



Monday 11 March 2024

View incoming documents in archived quotes and orders (BC24)

The capability to access incoming documents within sales and purchase quotes and orders, even after they have been archived, significantly enhances document management efficiency. This feature ensures uninterrupted access to crucial information, facilitates well-informed decision-making, and contributes to a reduction in administrative overhead.

Once you archive purchase and sales quotes and orders, whether through manual or automatic processes, the archived versions of these documents retain the incoming documents that were attached before the archival. This ensures that the historical record includes all pertinent incoming document information for reference.

 

As an illustration, let's initiate the creation of a purchase order, attach a relevant incoming document to it, and subsequently manually archive the order.



 





Upon accessing the Purchase List Archive page and reviewing the archived versions of a purchase order, note that the Incoming Document FactBox will display attachments that were originally linked to the initial document. This ensures comprehensive visibility into the attached incoming documents even within the archived records.

 


By default, the Incoming Documents FactBox is hidden; however, you have the flexibility to personalize pages and add them according to your preferences.


Sunday 10 March 2024

Connect Field Service operations with #msdyn365bc (BC24)


This integration is helpful for businesses offering professional services. It combines service task management, consumption tracking, and financial handling into a single, seamless system, benefiting technicians, managers, and finance departments. It streamlines work order and consumption management in Field Service, allowing for efficient invoicing and fulfillment through Business Central.



Sunday 31 December 2023

Print directly to local printers from the Job Queue with E-Print in Business Central SaaS

 

We cannot select "Report Output Type" as "Print" in "Job Queue" to print a report in Business Central SaaS

We get the following message.

You cannot select a printer from this online product. Instead, save it as PDF, or another format, which you can print later.

The output type has been set to PDF.


 

We need to make the below code change to achieve this.

[EventSubscriber(ObjectType::Codeunit, Codeunit::"Job Queue Start Report", 'OnBeforeRunReport', '', false, false)]

local procedure "Job Queue Start Report_OnBeforeRunReport"(ReportID: Integer; var JobQueueEntry: Record "Job Queue Entry"; var IsHandled: Boolean);

begin

        if JobQueueEntry."Job Queue Category Code" = 'PRINT' then

            JobQueueEntry."Report Output Type" := JobQueueEntry."Report Output Type"::Print;

end;


Based on that, I created a “PRINT” Job Queue Category and changed the Report Output Type before running the report.

 

Next, Navigate to the “Printer Management” page and set up an “Email Printer”.

https://learn.microsoft.com/en-us/dynamics365/business-central/admin-printer-setup-email

 

Enter your printer's E-Mail address as shown below.

 


Select the “Email printer” for the report you want to print on the “Printer Selection” page.

 


Create a “Job Queue Entry” as per your requirements.

NOTE: do not forget to set “Job Queue Category Code” as “PRINT”

 


You can ignore “Report Output Type” here as we change it via code.

Or you can create an action on the “Job Queue Entry” page to change it to "Print" as well.

When the Job Queue has successfully executed then an email will be sent to the printer's email address which will be taken care of.

 


 


 






What is E-Print?

https://www.hp.com/us-en/shop/tech-takes/what-is-hp-eprint

 

Thursday 30 November 2023

How to customize the #msdyn365bc theme for your organization


The organization theme takes center stage in the top navigation bar for all members within your organization. As administrators, you hold the power to craft multiple themes tailored to different segments of your organization, giving you the flexibility to curate unique experiences. This includes the ability to establish a default theme that universally applies or create up to four distinct group themes assignable to various Microsoft 365 groups.