Keep Suggestions

Step 6 of 6

This section of the workshop focuses on applying the field mappings to import data from an Excel file into the Dynamics 365 Business Central using a custom table designed to hold session information. Here’s how to finalize the import process effectively.

1. Review Key Components

Before proceeding with the data import, familiarize yourself with several key components:

Sessions Table

Review the structure of the GPT Sessions table located at src\3-ImportExcelCopilot\Sessions\Session.Table.al. Each session record is linked to a specific project, which corresponds to a job in Business Central.

Sessions List Page

The list page for viewing sessions linked to projects is prebuilt and found at src\3-ImportExcelCopilot\Sessions\Sessions.Page.al, integrated into the job list through an extension located at src\3-ImportExcelCopilot\Sessions\SessionsProjectsExt.PageExt.al.

Example Excel File

Use the provided example Excel file src\3-ImportExcelCopilot\Sessions\Examples\Sessions.xlsx for testing, or you may use your own file containing relevant data such as sessions, events, or workshops.

2. Implement Keep Suggestions

Confirm Import

When the user decides to finalize the import by clicking the "Import" action, ensure that the following code is correctly implemented in your PromptDialog page:

trigger OnQueryClosePage(CloseAction: Action): Boolean
begin
    if CloseAction = CloseAction::OK then
        ApplyImportOfSessions();
end;

Apply Import

Now, implement the ApplyImportOfSessions procedure.

• Objective: This procedure triggers when the user confirms their satisfaction with the field mappings and chooses to import the data into the Business Central database.
• Functionality: It retrieves the field mapping proposals from the user interface, then calls InsertSessionsUsingProposedFieldMapping() procedure in the GPT Import Excel Copilot Impl. codeunit to insert session data based on these mappings.

Sessions Saving

Let's break down the tasks performed by the InsertSessionsUsingProposedFieldMapping procedure:

• Iterate Through Excel Data: Starting from the second row (the first row contains headers), the procedure goes through each row in the Excel file.

  procedure InsertSessionsUsingProposedFieldMapping(var Project: Record Job; var ExcelBuffer: Record "Excel Buffer"; var FieldMappingProposal: Record "GPT Field Mapping Proposal")
  var
      MaxRowNo: Integer;
      RowNo: Integer;
  begin
      ExcelBuffer.Reset();
      ExcelBuffer.FindLast();
      MaxRowNo := ExcelBuffer."Row No.";
  
      for RowNo := 2 to MaxRowNo do begin
          ExcelBuffer.SetRange("Row No.", RowNo);
          InsertSession(Project, ExcelBuffer, FieldMappingProposal);
      end;
  end;
  

• Mapping Application: For each row, it uses the field mappings to determine which column data should go into which field of the GPT Sessions table.

• Session Creation: Creates a new session record for each row, filling in fields according to the mappings, and links it to the appropriate project.

Insert Session

• Record Handling: Opens a record reference for the GPT Sessions table and inserts or modifies data based on the Excel data and field mappings.
• Field Value Assignment: For each mapped field, retrieves the value from the correct Excel column and assigns it to the corresponding field in the session record.

local procedure InsertSession(var Project: Record Job; var ExcelBuffer: Record "Excel Buffer"; var FieldMappingProposal: Record "GPT Field Mapping Proposal")
var
    Session: Record "GPT Session";
    RecRef: RecordRef;
    FieldRef: FieldRef;
begin
    RecRef.Open(Database::"GPT Session");

    FieldMappingProposal.FindSet();
    repeat
        FieldRef := RecRef.Field(FieldMappingProposal."Target Field No.");
        FieldRef.Value := GetValueByCellNo(FieldMappingProposal, ExcelBuffer);
        if not RecRef.Insert() then
            RecRef.Modify();
    until FieldMappingProposal.Next() = 0;

    FieldRef := RecRef.Field(Session.FieldNo("Project Code"));
    FieldRef.Value := Project."No.";
    RecRef.Modify();
end;

Retrieve Excel Values

• Purpose: Fetches values from specified columns in the Excel buffer based on the source column number indicated in the field mapping proposal.
• Return: Returns the value found in the specified Excel column for use in session record creation.

    local procedure GetValueByCellNo(var FieldMappingProposal: Record "GPT Field Mapping Proposal"; var ExcelBuffer: Record "Excel Buffer"): Variant
    begin
        ExcelBuffer.SetRange("Column No.", FieldMappingProposal."Source Column No.");
        if ExcelBuffer.FindSet() then
            exit(ExcelBuffer."Cell Value as Text");

        exit('');
    end;

Conclusion

By completing this task, you have successfully integrated the "Keep Suggestions" feature into your Import Any Excel Copilot, allowing users to overview and confirm the field mappings before importing data into Dynamics 365 Business Central. Thorough testing is recommended to ensure seamless integration of all components.

Congratulations!

Now you've implemented the Import Any Excel Copilot, an AI-powered assistant that helps you import data from any Excel into Business Central by automating the field-mapping process. This copilot streamlines the data import process and enhances the user experience by providing flexibility and robust error handling.