Friday, 8 November 2024

Ensuring a Smooth Upgrade to Business Central 25 – SQL Server Full-Text Search Requirement

We encountered this error during a recent Business Central 25 upgrade.

Error: "Text optimized index cannot be created/queried because the SQL Server Full-Text Search component is not installed. Please install the SQL Server Full-Text Search component and try again."

 

This error message, which some may encounter during their upgrade process, indicates that the SQL Server Full-Text Search component must now be installed for Business Central 25.

Upgrading Unmodified C/AL Application to version 25 - Business Central | Microsoft Learn

Here’s how to add the Full-Text Search component to your SQL Server for Business Central 25:

 

Steps to Install SQL Server Full-Text Search Component

1.      Open SQL Server Installation Center.
If your installation media is accessible, launch the SQL Server Installation Center.

2.      Navigate to Installation.
Click on Installation from the sidebar.

3.      Choose “Stand-Alone or Add Features Option.”
Select New SQL Server stand-alone installation or add features to an existing installation

 



 

4.      Add Features to an Existing Instance.

             Click Next through the initial dialogs until you see the Installation Type dialog.                   Here, choose Add features to an existing instance of SQL Server.

5.      Select Full-Text and Semantic Extractions for Search.

             Under Database Engine Services, check Full-Text and Semantic Extractions for              Search to enable the Full-Text Search component.

 



 



 

6.      Complete Installation.

              Follow the remaining prompts to finalize the installation of this feature.

 



  

After installation, you should be able to proceed with the Business Central 25 upgrade without encountering the Full-Text Search error.

 


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