Maximizing Business Central with chatGPT

Yes, this blog will be about the idea of using the new chatGPT to extend possibilities of Business Central. 

So, what’s chatGPT?

How chatGPT can be valuable for AL developers? 

As I see from description  – chatGPT use Codex model to generate code. And Codex was trained on GitHub open repos. AL repos, compared to other languages, especially Python, are relatively small. At least 400x smaller

I did a separate webinar GitHub Copilot Write Extensions for Business Central with AL where showed how Copilot (based on Codex) can generate AL code. Feel free to watch and leave comments. 

Copilot definitely can increase the productivity of AL developers by completing the code blocks. 

But, can Codex generate code that will fully resolve one particular task in AL?  

As you see, it gives you a direction where to follow, but you should refactor everything by yourself. And of course you should know how to do that.

So should we put chatGPT on hold and wait until Codex will be better trained for AL?

Let’s see if we can make use of what it was best trained for. And I’m talking about

Python code generated by chatGPT

The thing is that there are about 4 million public Python repos in Github, compared to less that 100 for AL (according Github search engine). The result from that – Codex is quite good in writing complex python end-to-end code. I believe we can benefit from that.

Quite some time ago i wrote a blog How to get Insights from your Business Central App Data, using Python Azure Functions.

The idea was to use Python in cases when AL don’t perform well or is not capable of solving at all (due to lack of libraries, methods etc.), then put the Python code into an Azure function and call it from AL.

I got many positive feedback, with one BUT: in most cases AL developers don’t know Python. I guess you now have a feeling where I’m going to 😉 With the latest chatGPT you, actually, should not. 

Let’s try an example. 

When I enter a product (an item) on a sales order, then I want the system (Business Central) to also propose similar items that other customers also bought when they ordered the same item

This is quote from the Steven Renders blog Using the power of queries to bring some smartness into business central, written about 2 years ago. He proposed to use query object to gather all sales lines to other customers, related to the item i’m selling right now. 

This is good. But maybe chatGPT can propose us another way? Just ask

Generate an Azure Function code

Great, now we have a Python function. Let’s put it into an Azure function. ChatGPT can help with that as well.

Now let’s do create Azure Function in VS Code, copy and paste the generated code and publish.

				
					import azure.functions as func
import json
import pandas as pd

# Define an Azure Function that accepts an HTTP request
# and returns a response in JSON format
def main(req: func.HttpRequest) -> func.HttpResponse:
    # Parse the request body to get the item and sales lines data
    body = req.get_json()
    item = body['item']
    sales_lines = body['sales_lines']

    # Read the sales line data from the request body
    sales_lines = pd.DataFrame(sales_lines)

    # Get a list of customers who purchased the given item
    customers = list(sales_lines[sales_lines['item'] == item]['customer'].unique())

    # Get a list of items purchased by the customers who also
    # purchased the given item
    recommended_items = list(sales_lines[sales_lines['customer'].isin(customers)]['item'].unique())

    # Return the list of recommended items as JSON
    return func.HttpResponse(json.dumps(recommended_items))

				
			

Prepare Business Central Extension

Now we have a published Azure Function with the code generated by chatGPT. Let’s call it from Business central. 

 

We’ll need to prepare a http request in a json format. But how it should look like? Let’s ask chatGPT.

Nice, let’s create this in AL extension.

I will create the same UI as Steven did in his blog: ListPart with recommendations embedded in a Sales Order.

 

				
					pageextension 50100 "SalesOrderExt" extends "Sales Order"
{
    layout
    {
        // Add changes to page layout here
        addfirst(factboxes)
        {
            part(ItemCrossSaleFactbox; "Item Cross Sale Factbox")
            {
                ApplicationArea = All;
                Provider = SalesLines;
                SubPageLink = "Document Type" = field("Document Type")
                                , "Document No." = field("Document No.")
                                , "Line No." = field("Line No.");
            }
        }
    }
}

page 50100 "Item Cross Sale Factbox"
{
    PageType = ListPart;
    SourceTable = "Sales Line";
    Caption = 'Items bought by others';
    SourceTableTemporary = true;
    Editable = false;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field("No."; Rec."No.")
                {
                    ApplicationArea = Basic, Suite;
                    Caption = 'Item No.';
                    ToolTip = 'Specifies the item number.';
                }
                field(Description; Rec.Description)
                {
                    ApplicationArea = Basic, Suite;
                    Caption = 'Description';
                    ToolTip = 'Specifies the description of the item.';
                }
            }
        }
    }
    trigger OnFindRecord(Which: Text): Boolean
    begin
        FillTempTable();
        EXIT(Rec.Find(Which));
    end;

    procedure FillTempTable()
    var
        LineNo: Integer;
        SalesLine: Record "Sales Line";
        RecommendationsImpl: Codeunit "Recommendations Impl.";
    begin
        //Set SalesLine3 based upon Minimum values of SalesLine:
        IF NOT
          SalesLine.GET(
            Rec.GETRANGEMIN("Document Type"),
            Rec.GETRANGEMIN("Document No."),
            Rec.GETRANGEMIN("Line No."))
        THEN
            EXIT;

        RecommendationsImpl.RequestItemRecommendationsFromAzureFunctionAndSaveResult(SalesLine, Rec);
    end;

}
				
			
The implementation will be in RequestItemRecommendationsFromAzureFunctionAndSaveResult() function. I will utilise the new AzureFunction module in BC
				
					    procedure RequestItemRecommendationsFromAzureFunctionAndSaveResult(SalesLine: Record "Sales Line"; var RecommenationLine: Record "Sales Line" temporary)
    var
        AzureFunctionsAuthentication: Codeunit "Azure Functions Authentication";
        AzureFunctions: Codeunit "Azure Functions";
        Auth: Interface "Azure Functions Authentication";
        QueryDict: Dictionary of [Text, Text];
        Response: Codeunit "Azure Functions Response";
        ResponseText: Text;
    begin
        Auth := AzureFunctionsAuthentication.CreateCodeAuth('https://bc-recommend-sales-items.azurewebsites.net/api/recommend', '9D0S_jkgbVbD20rCe6nBl-9hOxCX6kZvadvaftelyyy3AzFu8Lk78Q==');
        Response := AzureFunctions.SendPostRequest(Auth, GetRequestJsonBody(SalesLine."No."), 'application/json');
        if not Response.IsSuccessful() then
            Error(Response.GetError());

        Response.GetResultAsText(ResponseText);
        SaveRecommendationsToTempTable(ResponseText, SalesLine, RecommenationLine);
    end;

				
			

The request body will be in the same format as chatGPT suggested. I will convert all Sales Invoice Lines with items, to Json

				
					    local procedure GetRequestJsonBody(ItemNo: Code[20]) Body: Text
    var
        JsonBody: JsonObject;
    begin
        JsonBody.Add('item', ItemNo);
        JsonBody.Add('sales_lines', ConvertSalesInvoiceLinesToJsonArray());
        jsonBody.WriteTo(Body);
    end;

    local procedure ConvertSalesInvoiceLinesToJsonArray() Result: JsonArray
    var
        SalesInvLine: Record "Sales Invoice Line";
        JsonObj: JsonObject;
    begin
        SalesInvLine.SetRange(Type, SalesInvLine.Type::Item);
        SalesInvLine.SetFilter(Quantity, '>0');
        if SalesInvLine.FindSet then
            repeat
                Clear(JsonObj);
                JsonObj.Add('customer', SalesInvLine."Bill-to Customer No.");
                JsonObj.Add('item', SalesInvLine."No.");
                JsonObj.Add('date', SalesInvLine."Posting Date");
                JsonObj.Add('quantity', SalesInvLine.Quantity);
                JsonObj.Add('price', SalesInvLine."Unit Price");
                Result.Add(JsonObj);
            until SalesInvLine.Next() = 0;
    end;

				
			

And the response will be converted from Json Array to a temporary Sales Line, which i show to the user

				
					    local procedure SaveRecommendationsToTempTable(ResponseText: Text; SalesLine: Record "Sales Line"; var RecommenationLine: Record "Sales Line" temporary)
    var
        JArray: JsonArray;
        i: Integer;
        JToken: JsonToken;
        LineNo: integer;
    begin
        RecommenationLine.Reset;
        RecommenationLine.deleteAll();

        JArray.ReadFrom(ResponseText);
        for i := 0 to JArray.Count() - 1 do begin
            JArray.Get(i, JToken);
            RecommenationLine.Init();
            RecommenationLine."Document Type" := SalesLine."Document Type";
            RecommenationLine."Document No." := SalesLine."Document No.";
            RecommenationLine."Line No." := i + 1;
            RecommenationLine."No." := JToken.AsValue().AsText();
            RecommenationLine.Description := GetItemDescription(RecommenationLine."No.");
            RecommenationLine.Insert();
        end;
    end;

				
			

Final testing

Everything is ready. Now let test the solution proposed by chatGPT.
The Source Code can be found here

Final Thoughts

The main point from the blog, is that, even without an experience in a Python (and that’s just one of the possible technologies), we were able to implement end-to-end solution which perfectly does the job. 
  • chatGPT created a Python code for us. We just published it.
  • copilot helped with AL code

How cool is that? 

And it’s not about that “computers will steal my job, anytime soon”. It’s about extending the limits of what we can do with Business Central, starting from just today.

Share Post:

Leave a Reply

About Me

DMITRY KATSON

A Microsoft MVP, Business Central architect and a project manager, blogger and a speaker, husband and a twice a father. With more than 15 years in business, I went from developer to company owner. Having a great team, still love to put my hands on code and create AI powered Business Central Apps that just works.

Follow Me

Recent Posts

Tags