Skip to content

AI Logic

Step 5 of 6

This guide covers the implementation of the AI logic in the Import Any Excel Copilot. You will enable Azure OpenAI to analyze Excel headers and suggest field mappings to Dynamics 365 Business Central table fields.

Code Structure

Start by adding necessary blocks of code to interact with Azure OpenAI, ensuring that the AI can receive input, process information, and return useful outputs.

1. Open Implementation Codeunit

  • Location: Navigate to the "src\3-ImportExcelCopilot\Implementation\ImportExcelCopilotImpl.Codeunit.al" file.

2. Understand Code Structure

The provided code skeleton includes placeholders where you will add specific blocks of code to interact with Azure OpenAI. These blocks are crucial for setting up the AI logic that processes and generates the project plan based on user descriptions.

3. Integration with Azure OpenAI

Implement these Azure OpenAI integration steps to set up authorization, send user inputs, and handle the AI's responses. Use your knowledge from previous tasks to complete this step.

  • Key Areas for Implementation:
  • Authorization setup with Azure OpenAI.
  • Sending and receiving data to and from Azure OpenAI.

  • Error handling based on the AI operation's response.

    Info

    Use the GetSystemMessage() procedure to define the system message that outlines the copilot's capabilities and limitations. Use GenerateUserMessage() to dynamically generate user messages based on the uploaded Excel file's headers. You will implement these functions in the next steps.

4. System Message

The system message guides the AI in its task. It defines the expected input, output formats, and constraints. Ask yourself the following questions to define the system message:

  • Instructions: What is the AI supposed to do?
  • Expected Input: What type of content should the AI process?
  • Output Format: What format should the AI's response take? (for this copilot, it should be JSON array with field mappings)

Now, when you answer these questions, you can define the system message for the AI to follow. Here is an example:

    // Example system message setup
    SystemMessage.AppendLine('You are an AI assistant tasked with mapping Excel column headers to Dynamics 365 Business Central table fields...');
    SystemMessage.AppendLine('Expected input: ');
    SystemMessage.AppendLine('- A comma-separated list of Excel headers.');
    SystemMessage.AppendLine('- A JSON array with Business Central table fields, each containing a field number and name.');
    SystemMessage.AppendLine('Output format: ');
    ...
    SystemMessage.AppendLine('Example output structure for clarity:');
    SystemMessage.AppendLine('[');
    SystemMessage.AppendLine('    {');
    SystemMessage.AppendLine('        "SourceColumnNo": 1,');
    SystemMessage.AppendLine('        "SourceColumnName": "Session Code",');
    SystemMessage.AppendLine('        "TargetFieldName": "Session ID",');
    SystemMessage.AppendLine('        "TargetFieldNo": 1');
    SystemMessage.AppendLine('    },');
    ...
    SystemMessage.AppendLine('No comments are allowed. Response should be in JSON format.');

This is just an example to guide you in crafting your system message. You should customize it and experiment.

Info

The difference between this and the system message in the previous task is that here we explicitly define the expected output format as a JSON array with specific field mappings. This is crucial for the AI to generate structured responses that can be post-processed reliably.

Tip

See the last sentence in the system message example above? This is to ensure that the response is strictly in JSON array format "[...]", as sometimes the response can contain additional characters like json" or " ` ` `json", or even phrases like Sure, here is a Json array..., which we want to avoid.

Info

Starting from Business Central version 24.2, you should be able to explecitely define the Json response format, as part of the PR#719

5. Generating User Message

Instead of static text inputs, dynamically generate user message based on uploaded Excel file column names and target table fields. This approach uses structured data (comma-separated lists for simple data, JSON for complex data) to enhance the AI's understanding and processing.

  • GenerateUserMessage: Create a user message that includes Excel headers and Business Central field names.
  • GetExcelHeaders and GetTableFields: Extract headers from the Excel buffer and field names from Business Central to construct the user message.
    local procedure GenerateUserMessage(var ExcelBuffer: Record "Excel Buffer"): Text
    var
        UserMessage: TextBuilder;
        ExcelHeaders: Text;
        TableFields: Text;
    begin
        UserMessage.AppendLine('Excel headers provided by the user:');
        UserMessage.AppendLine('---');
        ExcelHeaders := GetExcelHeaders(ExcelBuffer);
        UserMessage.AppendLine(ExcelHeaders);
        UserMessage.AppendLine('---');
        UserMessage.AppendLine('Session table fields:');
        UserMessage.AppendLine('---');
        TableFields := GetTableFields();
        UserMessage.AppendLine(TableFields);
        UserMessage.AppendLine('---');
        exit(UserMessage.ToText());
    end;

    local procedure GetExcelHeaders(var ExcelBuffer: Record "Excel Buffer"): Text
    var
        ExcelHeaders: TextBuilder;
        ExcelHeader: Text;
    begin
        ExcelBuffer.SetRange("Row No.", 1);
        ExcelBuffer.FindSet();
        repeat
            ExcelHeader := ExcelBuffer."Cell Value as Text";
            ExcelHeaders.AppendLine(ExcelHeader);
            ExcelHeaders.AppendLine(',')
        until ExcelBuffer.Next() = 0;
        exit(ExcelHeaders.ToText());
    end;

    local procedure GetTableFields() TableFields: Text
    var
        Field: Record Field;
        FieldJsonObj: JsonObject;
        FiedldsArray: JsonArray;
    begin
        Field.SetRange(TableNo, Database::"GPT Session");
        Field.SetRange(Class, Field.Class::Normal);
        Field.SetRange(ObsoleteState, Field.ObsoleteState::"No");
        if Field.FindSet() then
            repeat
                Clear(FieldJsonObj);
                FieldJsonObj.Add('TargetFieldNo', Field."No.");
                FieldJsonObj.Add('TargetFieldName', Field.FieldName);
                FiedldsArray.Add(FieldJsonObj);
            until Field.Next() = 0;

        FiedldsArray.WriteTo(TableFields);
    end;

Great! Now you have a system message that guides the AI and a user message that dynamically generates based on the uploaded Excel file's headers and Business Central table fields.

6. Clean AI Response

Sometimes, the AI's response may contain unwanted characters or formatting that needs to be cleaned before further processing. This step ensures that the response is structured and ready for mapping to Business Central fields.

Clean Response: Remove unwanted characters or formatting that might be included in the AI's response.

local procedure CleanResponseText(ResponseText: Text): Text
begin
    ResponseText := ResponseText.Replace('```json', '');
    ResponseText := ResponseText.Replace('```', '');
    exit(ResponseText);
end;

7. Finalize Field Mappings

Now, as we have cleaned the response, we need to convert the JSON response into actual field mappings in Business Central. This involves parsing the JSON and inserting the mappings into the GPT Field Mapping Proposal table.

  • Json Management: Utilize the Json Management codeunit to parse the JSON and loop through each mapping proposition, inserting them into the Field Mapping Proposal table.
local procedure ConvertTextResponseToFieldMappingProposalRecords(ResponseText: Text; var FieldMappingProposal: Record "GPT Field Mapping Proposal")
var
    JsonManagement: Codeunit "Json Management";
    FieldMappingObj: Text;
    i: Integer;
begin
    JsonManagement.InitializeCollection(ResponseText);

    for i := 0 to JsonManagement.GetCollectionCount() - 1 do begin
        JsonManagement.GetObjectFromCollectionByIndex(FieldMappingObj, i);
        InsertFieldMappingProposed(FieldMappingProposal, FieldMappingObj);
    end;
end;

local procedure InsertFieldMappingProposed(var FieldMappingProposal: Record "GPT Field Mapping Proposal"; FieldMappingObj: Text)
var
    JsonManagement: Codeunit "Json Management";
    RecRef: RecordRef;
    FieldRef: FieldRef;
begin
    JsonManagement.InitializeObject(FieldMappingObj);

    RecRef.GetTable(FieldMappingProposal);
    RecRef.Init();
    JsonManagement.GetValueAndSetToRecFieldNo(RecRef, 'SourceColumnNo', FieldMappingProposal.FieldNo("Source Column No."));
    ...
    RecRef.Insert(true);
end;

8. Display Field Mappings

You should have the GPT Cop. Imp. Excel Subpart part ready to display the proposed field mappings. This part is used to show the mappings suggested by the AI and allow the user to review and confirm them.

Review how the "src\3-ImportExcelCopilot\PromptDialog\CopImpExcelSubpart.Page.al" page is structured to display the field mappings, loads the data, and handles user interactions.

Conclusion

By following these steps, you will enhance the Import Any Excel Copilot with powerful AI feature. It will analyze, and suggest field mappings between Excel files and Business Central tables.

In the next step you will use these field mappings to import data from Excel into a GPT Session table.