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
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
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;
}
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
Final Thoughts
- 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.
