Extending standard APIs (2)

In the previous blog post, I’ve demonstrated how to extend standard APIs by using them as a template for a new custom API that contains the customizations you want to do. While that approach works for most APIs, there are some APIs that are more complicated. I’m talking about APIs for sales and purchase documents. These APIs are special because they are not based on the standard tables Sales Header, Sales Line, etc.

I take the sales documents as examples here, but as you can imagine this also applies to purchase documents.

API salesOrders

The API page for Sales Orders is based on the table “Sales Order Entity Buffer”. This table holds a copy of all records in the table “Sales Header” of type Order. This is managed from codeunit “Graph Mgt – Sales Order Buffer”. I’d recommend to take a look at this codeunit to read how this is managed. Similarly, the API page for Sales Order Lines is based on table “Sales Invoice Line Aggregate”. The word Invoice in this name is not a typo, this is really the name. The same table is also used for the Sales Invoice Line API. But unlike the “Sales Order Buffer” table, the lines are not copied over. Instead, the API pages use them as temporary source tables and fill them on demand. See below for a full list of all buffer tables used by the sales and purchase document APIs.

Here is a high-level dataflow diagram for the salesOrders API.

As you can see, the salesOrders API reads data from the table “Sales Order Entity Buffer”. It does not insert or update data in that table. At least not directly. The API page is based on that table, so when you insert a new order or update an existing one, the API page uses the same table to store the values. But right before it inserts or updates it in the database, it calls the codeunit “Graph Mgt – Sales Order Buffer” to propagate the values to the real “Sales Header” table. This happens in the OnInsert / OnModify / OnDelete triggers in the API page. See below the code of these triggers and notice the exit(false) which cancels the operation to the source table of the page.

    trigger OnDeleteRecord(): Boolean
    begin
        GraphMgtSalesOrderBuffer.PropagateOnDelete(Rec);
        exit(false);
    end;
    trigger OnInsertRecord(BelowxRec: Boolean): Boolean
    begin
        CheckSellToCustomerSpecified();
        GraphMgtSalesOrderBuffer.PropagateOnInsert(Rec, TempFieldBuffer);
        SetDates();
        UpdateDiscount();
        SetCalculatedFields();
        exit(false);
    end;
    trigger OnModifyRecord(): Boolean
    begin
        if xRec.Id <> Id then
            Error(CannotChangeIDErr);
        GraphMgtSalesOrderBuffer.PropagateOnModify(Rec, TempFieldBuffer);
        UpdateDiscount();
        SetCalculatedFields();
        exit(false);
    end;

The codeunit “Graph Mgt – Sales Order Buffer” transfers the values from “Sales Order Entity Buffer” to the “Sales Header” table. Below is the code of the PropagateOnInsert. The Modify works similar, but it gets the record first before updating it or inserts if it doesn’t exist.

    procedure PropagateOnInsert(var SalesOrderEntityBuffer: Record "Sales Order Entity Buffer"; var TempFieldBuffer: Record "Field Buffer" temporary)
    var
        SalesHeader: Record "Sales Header";
        TypeHelper: Codeunit "Type Helper";
        TargetRecordRef: RecordRef;
        DocTypeFieldRef: FieldRef;
    begin
        if SalesOrderEntityBuffer.IsTemporary or (not GraphMgtGeneralTools.IsApiEnabled) then
            exit;
        TargetRecordRef.Open(DATABASE::"Sales Header");
        DocTypeFieldRef := TargetRecordRef.Field(SalesHeader.FieldNo("Document Type"));
        DocTypeFieldRef.Value(SalesHeader."Document Type"::Order);
        TypeHelper.TransferFieldsWithValidate(TempFieldBuffer, SalesOrderEntityBuffer, TargetRecordRef);
        // SetTable does not transfer globals, which will affect the logic in OnInsert trigger. We have to insert here and modify latter.
        TargetRecordRef.Insert(true);
        SalesHeader.Get(TargetRecordRef.RecordId());
        SalesHeader.CopySellToAddressToBillToAddress;
        SalesHeader.SetDefaultPaymentServices;
        SalesHeader.Modify(true);
        SalesOrderEntityBuffer."No." := SalesHeader."No.";
        SalesOrderEntityBuffer.Get(SalesOrderEntityBuffer."No.");
    end;

As you can see, the field values are actually transferred with the generic function TransferFieldsWithValidate in codeunit TypeHelper. This function uses the TempFieldBuffer as the list of field values that needs to be transferred. It is important to understand that this table has a field Order which is automatically filled by the function RegisterFieldSet in the API page. In short, without copying a lot of source code here, the fields are transferred in the order in which they appear in the API page. Furthermore, the fields are mapped on their field ID only, not by name.

When a record is inserted or updated in the “Sales Header” table, then the very same codeunit synchronizes the data to the table “Sales Order Entity Buffer”. By the way, the same pattern exists for the Sales Quote.

API salesInvoices

The API page for Sales Invoices on the other hand, is based on the table “Sales Invoice Entity Aggregate”. From the API point of view, the data flow is exactly the same as with sales orders. However, there is a difference from a functional perspective. The table “Sales Invoice Entity Aggregate” does not only contain unposted sales invoices from table “Sales Header”, it also contains posted invoices from table “Sales Invoice Header”. And that makes a huge difference of course. The idea behind this is to have one endpoint that returns both unposted and posted sales invoices. On top of that, the posted sales invoice will keep the same id. As a result, the sales invoice will keep the same resource URL throughout the whole lifecycle from unposted to posted document.

Here is the high-level data flow diagram for the salesInvoices API:

Status field

There is one thing about the salesOrders and salesInvoices APIs that can be confusing. And that is the status field. We all know the status field in the table “Sales Header”. But the value Open in the sales APIs has a different meaning compared to the same value on the sales document in the Business Central UI. In the API it actually means that the sales document is Released. In other words, status Open in the API means that the sales document is not open in terms of Business Central UI. 🥴 Instead, the status Draft in the APIs is equivalent to Open in the sales document. My educated guess for this difference is that Microsoft tried to align the APIs with common status values as used by other solutions.

Sales Document status

Sales orders and invoices have a status based on the enum “Sales Document Status” which has these values:

salesOrder API status

The salesOrders API has a status field, which is based on the enum “Sales Order Entity Buffer Status”. This enum has these values:

salesInvoices API status

The salesInvoices API has yet another status field, based on the enum “Invoice Entity Aggregate Status” with these values:

For the canceled and corrected invoice, see https://docs.microsoft.com/en-us/dynamics365/business-central/sales-how-correct-cancel-sales-invoice

Sales Lines

Now we have that out of our way, let’s look at the sales lines before we dive into modifying the API. While the sales header values are actually duplicated in the buffer tables, the sales lines are not. Instead, all APIs for sales lines are using the same table: “Sales Invoice Line Aggregate” as a temporary source table. The lines are loaded into the temporary table by calling the function LoadLines in codeunit “Sales Invoice Aggregator”. It is basically a TransferFields, followed by some extra calls to update line amounts.

Inserting or updating sales lines is done in a similar way as the sales headers. The codeunit “Sales Invoice Aggregator” has functions to propagate the field values from the temporary record to the table “Sales Line”. Again, the function TransferFieldsWithValidate from codeunit “Type Helper” is used. No magic stuff in here, just the same story again.

Adding a custom field to the table and API

Let’s now look at how we can customize those sales APIs. We start with the salesInvoices API as an example. The scenario is to add a custom field Webshop Reference Id to the table “Sales Header” and to the API.

The tableextension for the “Sales Header” table looks like:

tableextension 50100 SalesHeaderAJK extends "Sales Header"
{
    fields
    {
        field(50100; WebshopReferenceIdAJK; Text[30])
        {
            Caption = 'Webshop Reference Id';
        }
    }
}

And of course we want to have this field on the posted sales invoice as well:

tableextension 50101 SalesInvoiceHeaderAJK extends "Sales Invoice Header"
{
    fields
    {
        field(50100; WebshopReferenceIdAJK; Text[30])
        {
            Caption = 'Webshop Reference Id';
        }
    }
}

We start with copying the standard API as explained in the previous blog post. In order to add the field to the copied API page, it needs to be added to the table “Sales Invoice Entity Aggregate” as well. So you can guess the other tableextension:

tableextension 50102 SalesInvoiceEntityAggregateAJK extends "Sales Invoice Entity Aggregate"
{
    fields
    {
        field(50100; WebshopReferenceIdAJK; Text[30])
        {
            Caption = 'Webshop Reference Id';
        }
    }
}

Now we can add the field to the API page. Don’t forget to include the call to RegisterFieldSet! And if the order is important, then make sure to place it in the correct position as well. Otherwise you can just add it to the end, or before the first part.

                field(webshopReferenceId; Rec.WebshopReferenceIdAJK)
                {
                    Caption = 'Webshop Reference Id';
                    trigger OnValidate()
                    begin
                        RegisterFieldSet(Rec.FieldNo(WebshopReferenceIdAJK));
                    end;
                }

Now this field can be used to create a new sales invoice.

So far so good. This works for both headers and lines in the same way. To summarize: in order to add a new field to the API:

• Add the field with a tableextension to Sales Header or Sales Line
• Optionally add the field with a tableextension to the posted document tables
• Create a custom API page based on the standard API
• Add the field with a tableextension to buffer table used by the API page (see table below)
• Add the field to the custom API page and call RegisterFieldSet from the OnValidate

The buffer tables for all sales and purchase document APIs:

Adding an existing field to the API

The second scenario is less obvious. Not all fields in the sales document tables are available in the API. How can we add these fields to the API? Let’s say we want to add the fields “Blanket Order No.” and “Blanket Order Line No.” to the salesOrderLines API. I didn’t take these fields randomly, I’ve got this question a couple of times and it was actually the reason for writing this blog post. 😊

Let’s start by creating the custom salesOrderLines API. That’s just the same procedure as described in the previous blog post. But the salesOrderLines API is linked to the salesOrders API (the header). We need to create a custom API page for the header as well. Follow the same procedure to create the custom salesOrders API and then change the salesOrderLines part to point to the custom salesOrderLines API page. The only required change is the name of the subpage.

                part(salesOrderLines; SalesOrderLinesAPIAJK)
                {
                    Caption = 'Lines';
                    EntityName = 'salesOrderLine';
                    EntitySetName = 'salesOrderLines';
                    SubPageLink = "Document Id" = Field(Id);
                }

Now we are ready to add the fields to the custom salesOrderLines API. Since these fields are already available in the “Sales Line” table we don’t have to create a tableextension for the “Sales Line” table. But unfortunately, they are not available in the table “Sales Invoice Line Aggregate”. And that is a BIG miss from Microsoft. Apparently, only the fields that are used in the standard APIs have been added. The same counts for the “Sales Header” table and the corresponding buffer tables. I’m not sure why. In an attempt to save some space because the data is being duplicated? That doesn’t make a big difference I would say, and for the sales line it does not make a difference at all because that table is only used as a temporary table. I think that the scenario of extending the standard API with fields from the base app just wasn’t considered. So, if Microsoft is reading this, can you please make sure that the API buffer tables contain all fields from the document tables? That would be very helpful!

The only option we have now is to add the fields to the buffer table in our own number range and then somehow copy the values to the target table. So let’s start with the tableextension:

tableextension 50103 SalesInvoiceLineAggregateAJK extends "Sales Invoice Line Aggregate"
{
    fields
    {
        field(50100; BlanketOrderNoAJK; Code[20])
        {
            AccessByPermission = TableData "Sales Shipment Header" = R;
            Caption = 'Blanket Order No.';
            TableRelation = "Sales Header"."No." WHERE("Document Type" = CONST("Blanket Order"));
        }
        field(50101; BlanketOrderLineNoAJK; Integer)
        {
            AccessByPermission = TableData "Sales Shipment Header" = R;
            Caption = 'Blanket Order Line No.';
            TableRelation = "Sales Line"."Line No." WHERE("Document Type" = CONST("Blanket Order"),
                                                           "Document No." = FIELD(BlanketOrderNoAJK));
        }
    }
}

And these fields need to be added to the custom salesOrderLines API page as well. The position is not really important because we will be validating those fields at the end.

Next question is how to get the values from these fields to the target fields in the base table. First thing to look at would be events. But unfortunately, there are no events at all in the codeunits “Sales Invoice Aggregator” or the other codeunits used by the API pages. Bummer…. Now we need to write custom code for something that could have been solved out-of-the-box. Let me give you two options, so you have something to choose.

Option 1: With table extensions

This option is added after the post was published. 😀 But it is a more elegant way and gives you full control over the order in which the fields are validated. But… there is a disadvantage as you will see.

The idea is to create a table extension for the “Sales Line” table with the same fields we added to the aggregate table used by the API. When the custom fields are changed, then we change the target field. And when the target field is changed, the API fields are kept in sync. With that, we don’t require any further coding in the API page. The tableextension will look like this:

tableextension 50104 SalesLineAJK extends "Sales Line"
{
    fields
    {
        modify("Blanket Order No.")
        {
            trigger OnAfterValidate()
            begin
                BlanketOrderNoAJK := "Blanket Order No.";
            end;
        }
        modify("Blanket Order Line No.")
        {
            trigger OnAfterValidate()
            begin
                BlanketOrderLineNoAJK := "Blanket Order Line No.";
            end;
        }
        field(50100; BlanketOrderNoAJK; Code[20])
        {
            AccessByPermission = TableData "Sales Shipment Header" = R;
            Caption = 'Blanket Order No.';
            TableRelation = "Sales Header"."No." WHERE("Document Type" = CONST("Blanket Order"));
            trigger OnValidate()
            begin
                Validate("Blanket Order No.", BlanketOrderNoAJK);
            end;
        }
        field(50101; BlanketOrderLineNoAJK; Integer)
        {
            AccessByPermission = TableData "Sales Shipment Header" = R;
            Caption = 'Blanket Order Line No.';
            TableRelation = "Sales Line"."Line No." WHERE("Document Type" = CONST("Blanket Order"),
                                                           "Document No." = FIELD(BlanketOrderNoAJK));
            trigger OnValidate()
            begin
                Validate("Blanket Order Line No.", BlanketOrderLineNoAJK);
            end;
        }
    }
}

On the API page we just add the custom fields like we did in the previous example with a new custom field:

                field(blanketOrderNo; Rec.BlanketOrderNoAJK)
                {
                    Caption = 'Blanket Order No.';
                    trigger OnValidate()
                    begin
                        RegisterFieldSet(Rec.FieldNo(BlanketOrderNoAJK));
                    end;
                }
                field(blanketOrderLineNo; Rec.BlanketOrderLineNoAJK)
                {
                    Caption = 'Blanket Order Line No.';
                    trigger OnValidate()
                    begin
                        RegisterFieldSet(Rec.FieldNo(BlanketOrderLineNoAJK));
                    end;
                }

Because the fields are registered in the field buffer through the function RegisterFieldSet, we have full control over the order in which they are validated.

The only disadvantage of this approach is that the custom fields need to be in sync with the target fields. That means that you need to write upgrade code for the initial synchronization, because the custom fields will be empty after installation. And if the original fields could be changed by means of a direct value assignment instead of a validate, then the values are out of sync as well. But it would be easy to solve that with an event subscriber codeunit of course.

If the order of the fields are important, then this approach is the best option. If the order of the fields are not important and you are not sure about keeping the values in sync, then the next option might be the better choice.

Option 2: With a manual event subscriber

The second way I could think of is by using event subscribers to the events of the “Sales Line” table. This should be a manual subscriber codeunit to avoid unnecessary event hits. The advantage of this method is that you don’t have to keep values in sync between the original field and the API field. And you don’t have to write any upgrade code for the initial synchronization. The disadvantage is that you don’t have control over the exact order. As you can see, the fields don’t call the function RegisterFieldSet because they will be synced at the end of the process, right before the insert or modify.

                field(blanketOrderNo; Rec.BlanketOrderNoAJK)
                {
                    Caption = 'Blanket Order No.';
                }
                field(blanketOrderLineNo; Rec.BlanketOrderLineNoAJK)
                {
                    Caption = 'Blanket Order Line No.';
                }

Here is an example of the code for this manual event subscriber codeunit:

codeunit 50100 SalesLineAPISubscriberAJK
{
    EventSubscriberInstance = Manual;
    var
        SalesInvoiceLineAggregate: Record "Sales Invoice Line Aggregate";
    procedure SetSalesLineInvoiceLineAggregate(Rec: Record "Sales Invoice Line Aggregate")
    begin
        SalesInvoiceLineAggregate := Rec;
    end;
    [EventSubscriber(ObjectType::Table, Database::"Sales Line", 'OnBeforeInsertEvent', '', false, false)]
    local procedure OnBeforeInsertSalesLine(var Rec: Record "Sales Line")
    begin
        if Rec.IsTemporary then
            exit;
        UpdateSalesLine(Rec);
    end;
    [EventSubscriber(ObjectType::Table, Database::"Sales Line", 'OnBeforeModifyEvent', '', false, false)]
    local procedure OnBeforeModifySalesLine(var Rec: Record "Sales Line")
    begin
        if Rec.IsTemporary then
            exit;
        UpdateSalesLine(Rec);
    end;
    local procedure UpdateSalesLine(var SalesLine: Record "Sales Line")
    begin
        SalesLine.Validate("Blanket Order No.", SalesInvoiceLineAggregate.BlanketOrderNoAJK);
        SalesLine.Validate("Blanket Order Line No.", SalesInvoiceLineAggregate.BlanketOrderLineNoAJK);
    end;
}

Don’t forget to test for Rec.IsTemporary! During the validation of fields, temporary lines are being created, and they will hit this event subscriber as well, resulting in a potential recursive overflow.

As you can see, the UpdateSalesLine function just validates the fields that I need. This could be more generic by using a similar field buffer table, but I decided to keep the code readable.

No we need to bind this codeunit to the right procedure. That’s done in the custom salesOrderLines API page:

    var
        SalesLineAPISubscriber: Codeunit SalesLineAPISubscriberAJK;
    trigger OnInsertRecord(BelowxRec: Boolean): Boolean
    var
        GraphMgtSalesOrderBuffer: Codeunit "Graph Mgt - Sales Order Buffer";
    begin
        SalesLineAPISubscriber.SetSalesLineInvoiceLineAggregate(Rec);
        BindSubscription(SalesLineAPISubscriber);
        GraphMgtSalesOrderBuffer.PropagateInsertLine(Rec, TempFieldBuffer);
        UnbindSubscription(SalesLineAPISubscriber);
        SetCustomFields();
    end;
    trigger OnModifyRecord(): Boolean
    var
        GraphMgtSalesOrderBuffer: Codeunit "Graph Mgt - Sales Order Buffer";
    begin
        SalesLineAPISubscriber.SetSalesLineInvoiceLineAggregate(Rec);
        BindSubscription(SalesLineAPISubscriber);
        GraphMgtSalesOrderBuffer.PropagateModifyLine(Rec, TempFieldBuffer);
        UnbindSubscription(SalesLineAPISubscriber);
        SetCustomFields();
    end;

There is one thing left that we can’t solve with these event subscribers. The propagate functions return the buffer record which has been updated with all values from the sales line. It does that by first clearing all values and use TransferFields. As a result, our custom fields have been cleared, so we need to put them back. For that I have created the function SetCustomFieldValues. The call is already in the code above. The function looks like this:

    local procedure SetCustomFields()
    var
        SalesLine: Record "Sales Line";
	begin
        SalesLine.GetBySystemId(Rec.SystemId);
        Rec.BlanketOrderNoAJK := SalesLine."Blanket Order No.";
        Rec.BlanketOrderLineNoAJK := SalesLine."Blanket Order Line No.";
	end;

The final step that we need to do is to get the values into the buffer table when the record is read with a HTTP GET. For that, we can use the same function SetCustomFields. Just call it from the OnAfterGetRecord trigger.

    trigger OnAfterGetRecord()
    begin
        SetCustomFields();
    end;

This works the same for the salesOrder and salesInvoice APIs (the headers). Of course you could also create event subscribers to keep the field values synchronized. Below is the test result that proves all of this is actually working. 😊

I hope that this long post helps you in creating custom APIs for the sales documents. Maybe I should do a video about this as well, just to demonstrate all the steps above. Let me know in the comments! And if you found another way to do it, then please feel free to share.

Happy coding!