Deep insert with Business Central APIs (part 2)

1 Jul

In my previous post about deep inserts with Business Central APIs, I mentioned creating custom APIs that support deep inserts. I tried to create an example with Sales Header / Sales Line, but I gave up because I ran into too many issues. In this post I want to explain what you need for creating custom APIs that support deep inserts. I will do so with a simple Header & Line table example.

The header table has just two fields and some code to simulate a number series.

table 60000 "My Header"
{
    fields
    {
        field(1; "No."; Code[10]) { }
        field(2; Description; Text[50]) { }
    }

    keys
    {
        key(PK; "No.") { }
    }

    trigger OnInsert()
    var
        MyHeader: Record "My Header";
    begin
        if "No." = '' then
            if MyHeader.FindLast() then
                "No." := IncStr(MyHeader."No.")
            else
                "No." := 'HEADER001';
    end;
}

The line table is linked to the header table in the usual way, similar to Sales Line.

table 60001 "My Line"
{
    fields
    {
        field(1; "Header No."; Code[20])
        {
            TableRelation = "My Header"."No.";
        }
        field(2; "Line No."; Integer) { }
        field(3; "Header Id"; Guid)
        {
            TableRelation = "My Header".SystemId;
        }
        field(4; Description; Text[50]) { }
    }

    keys
    {
        key(PK; "Header No.", "Line No.") { }
    }
}

But it also has a field “Header Id” which is linked to the SystemId field of the header table. And that is a key part in this story.

Linking by SystemId field

One of the recommendations when creating custom APIs is to make use of the SystemId field. Before we dive into the code of the custom API, we need to understand the role of the SystemId field. It is used in the URL like this:

{{baseurl}}/api/ajk/demo/v1.0/companies({{companyid}})/headers({{headerid}})

The companyid and headerid in the URL are the SystemId fields of these tables. In the API page you need to set the property ODataKeyFields to the field that you want to filter with this value. Usually the ODataKeyFields property is set to be SystemId.

page 60000 "My Header API"
{
    PageType = API;
    SourceTable = "My Header";
    APIPublisher = 'ajk';
    APIGroup = 'demo';
    APIVersion = 'v1.0';
    EntitySetName = 'headers';
    EntityName = 'header';
    DelayedInsert = true;
    ODataKeyFields = SystemId;

    layout
    {
        area(Content)
        {
            field(id; SystemId) { }
            field(headerNo; "No.") { }
            field(description; Description) { }
            part(lines; MyAPILines)
            {
                EntitySetName = 'lines';
                EntityName = 'line';
                SubPageLink = "Header Id" = field(SystemId);
            }
        }
    }
}

This is the code of the custom API for the header table. Simple and straightforward, isn’t it? The most important detail is the part that points to the page with the lines. As you can see, the link between the header and the lines is not based on the “No.” field of the header table, but on the SystemId field. Why don’t we just use the “No.” field as in normal pages? The reason is that API pages don’t behave like normal UI pages. They behave differently and you should treat them as a totally different beast.

This header API contains a page part for the lines, so we can expand the URL with the entity set name of that page part. We call that a navigational property of the API, a relation from one entity to another entity.

{{baseurl}}/api/ajk/demo/v1.0/companies({{companyid}})/headers({{headerid}})/lines

This URL points directly to the lines. When you use this URL to insert a new line, then it will use the header id from the URL as a filter for the lines. But it will not first read the header record in the header API page! Because of this, you can’t use any other value to link the header and line part than the value provided with the URL. The header API page does not even execute the triggers OnAfterGetRecord and OnAfterGetCurrRecord.

And that’s why you need the SystemId field of the header as a field in the line table.

The lines part

Let’s have a look at the lines part. We’ve just seen that the lines are filtered based on the SystemId of the header. With that value, we should be able to get the header record and populate the “Header No.” of the line table. But we need another property to get the value into the “Header Id” field: PopulateAllFields. Without this property, things still don’t work. It makes sure that non-key fields of new records are automatically populated based on a single filter value.

The lines part is just a regular ListPart page. It’s not an API page, because then it becomes an API entity on itself and could be called without the header. Let’s have a look at the lines API.

page 60001 MyAPILines
{
    PageType = ListPart;
    SourceTable = "My Line";
    DelayedInsert = true;
    AutoSplitKey = true;
    PopulateAllFields = true;
    ODataKeyFields = SystemId;

    layout
    {
        area(Content)
        {
            repeater(lines)
            {
                field(id; Format(SystemId, 0, 4).ToLower()) { }
                field(headerNo; "Header No.") { }
                field(headerId; "Header Id") { }
                field(lineNo; "Line No.") { }
                field(description; Description) { }
            }
        }
    }

    var
        IsDeepInsert: Boolean;

    trigger OnInsertRecord(BelowxRec: Boolean): Boolean
    var
        MyHeader: Record "My Header";
        MyLine: Record "My Line";
    begin
        if IsDeepInsert then begin
            MyHeader.GetBySystemId("Header Id");
            "Header No." := MyHeader."No.";
            MyLine.SetRange("Header No.", "Header No.");
            if MyLine.FindLast() then
                "Line No." := MyLine."Line No." + 10000
            else
                "Line No." := 10000;
        end;
    end;

    trigger OnNewRecord(BelowxRec: Boolean)
    var
        MyHeader: Record "My Header";
    begin
        IsDeepInsert := IsNullGuid("Header Id");
        if not IsDeepInsert then begin
            MyHeader.GetBySystemId("Header Id");
            "Header No." := MyHeader."No.";
        end;
    end;
}

First of all, because this page is a normal page, and not an API page, it is not possible to put the SystemId field on it. That’s been solved with Format(SystemId, 0, 4).ToLower(). This returns the value in exactly the same way as the SystemId field on an API page.

As you can see, the PopulateAllFields property on the page is set to true. And the ODataKeyFields property is also set, because this page is exposed as part of an API.

The real stuff, which includes a little bit unexpected behavior, is in the triggers OnNewRecord and OnInsertRecord.

Let’s first look at the OnNewRecord. Consider a normal direct insert of a line record:

post {{baseurl}}/api/ajk/demo/v1.0/companies({{companyid}})/headers({{headerid}})/lines
Authorization: Basic {{username}} {{password}}
Content-Type: application/json

{
    "description": "Some line"
}

When you insert a new record directly, not as a deep insert, then the header id value is part of the URL. The header page can take that value and pass it to the line page by means of the SubPageLink property. In the lines page, the PopulateAllFields feature kicks in, and it sets the value of the “Header Id” field before the OnNewRecord trigger is executed. As a result, we can just read the header record with the GetBySystemId function in the OnNewRecord trigger and fill the “Header No.” field. Finally, that will enable the AutoSplitKey feature to correctly calculate the next line number.

But the deep insert behaves a little bit different. Consider a deep insert of a header and some lines:

post {{baseurl}}/api/ajk/demo/v1.0/companies({{companyid}})/headers?$expand=lines
Authorization: Basic {{username}} {{password}}
Content-Type: application/json

{
    "description": "Deep insert",
    "lines": [
        {
           "description": "First line"
        },
        {
            "description": "Second line"
        }
    ]
}

In this situation, the “Header Id” field is not populated in the OnNewRecord trigger. It’s just empty, nada, nope, null. If that’s the case, then we can assume it’s a deep insert. But we need the “Header No.” field to the header record (which is being created in the same call). After many tests, I figured out that when the record is inserted from the OnInsert trigger, then the “Header Id” field does contain the value. Unfortunately, the AutoSplitKey function to automatically calculate line numbers doesn’t work properly in this situation. You need to write your own code in the OnInsertTrigger to calculate the next line number.

Field validations

Because API pages require delayed insert, all fields field will be validated before the record is actually inserted. For deep inserts that means the “Header No.” field does not yet contain a value. And if you have field validation code that needs the header record, then you run into the situation that the header can’t be read. How to fix that?

Well, it turns out to be quite easy. During a deep insert, you don’t have the Header Id in the OnNewRecord trigger on the page. But when the OnValidate triggers of the fields are executed (which happens after the OnNewRecord trigger), then the Header Id field does have a value. The only thing you need to do is to read the Header record based on the “Header Id” field instead of the “Header No.” field. Makes sense, right?

Here is the line table again with an example of how to read the header table. Works both with deep inserts and direct inserts.

table 60001 "My Line"
{
    fields
    {
        field(1; "Header No."; Code[20])
        {
            TableRelation = "My Header"."No.";
        }
        field(2; "Line No."; Integer) { }
        field(3; "Header Id"; Guid)
        {
            TableRelation = "My Header".SystemId;
        }
        field(4; Description; Text[50])
        {
            trigger OnValidate()
            begin
                CheckHeaderStatus();
            end;
        }
    }

    keys
    {
        key(PK; "Header No.", "Line No.") { }
     }

    var
        MyHeader: Record "My Header";
        
     local procedure CheckHeaderStatus()
     begin
         GetHeader();
         // Do status check
     end;

     local procedure GetHeader()
     begin
         if MyHeader.SystemId <> "Header Id" then
            MyHeader.GetBySystemId("Header Id");
     end;
}

Now you can probably also see why it is hard to create a custom API for Sales Header and Sales Line that supports deep inserts. There is no “Header Id” field on the Sales Line, and the header is just read with the “Document No.” field. If you find yourself in that situation, then I recommend to take a look at the standard APIs, copy it and adjust to your needs. These standard APIs are not based on the Sales Header and Sales Line tables but use buffer tables instead.

Hope this all makes sense. To be honest, it was not easy to explain this stuff. And I don’t blame you if it’s not clear to you. Please let me know in the comments and I’ll try to clarify!

9 thoughts on “Deep insert with Business Central APIs (part 2)

  1. Any suggestions on adding fields to the sales order V1.0 API?

    I have copied required objects from V1.0 and created custom api.

    Have successfully added default dimensions to customer import but cannot added Shipping Agent, Shipping Agent Service and Sales Order Comment Lines to importing and exporting.

    Can’t even get the shipping fields!

    Problems probably due to intermediate sales order table and the complex nature of API logic.

    • Yes, that requires some coding. You need to add the fields to the buffer table that is used by the API page. And then, in the API page, copy the logic from the other fields to add the field to the TempFieldBuffer. That should do the trick.

      • Thinking about it… The fields Shipping Agent Code and Shipping Agent Service Code are not in the Sales Order Entity buffer table, and adding those fields with a table extension does not work. Because the fields are copied from Sales Header to Sales Order Entity Buffer table with a TransferFields you will get nothing.
        Your only option would be to subscribe to the Sales Order Entity Buffer OnInsert / OnModify triggers and update the fields you have added with a table extension.

        • ajk,
          Yes that’s exactly what I encountered.

          Never got the triggers working.

          I think that we could add custom fields by adding them to the buffer and main table as we could make the field numbers match.

          Decided to just make a simple sales header custom api page. Less hassle.

          Looks like we’re on the bleeding edge here!

          Thanks!

  2. Thank you for bringing clarity to something that’s been bothering and giving me quite the headache for far too long!

    How deep can you go? Would this approach work just as well on deeper structures? Like if you would have Header – Lines – Sublines – Sub sublines. I’ve not found any information about limitations in how deep of a structure you’re able to build.

    • First of all, it depends on how you define your pages. If the Lines page is a normal ListPart, then you can’t add another ListPart for sublines to it. Only API pages can have page parts. This means that in your example, Lines and Sublines must be a real API page. That also means they can be called directly, without the header. It’s not very difficult to block that, but you need to be aware of this.

      As far as I know, it is possible to do a multi-level insert. If not in the current version, then it should be available in the next version (v17).

  3. Hello,
    Thanks for all this informartions.
    I tried to reproduced, step by step, this api creation on BC 16 and when i try to insert (with Postman) header/line i have all time this error :
    ” “error”: {
    “code”: “BadRequest”,
    “message”: “Does not support untyped value in non-open type. CorrelationId: 1d89b167-005a-4d9b-8eda-dadaca95ac45.”
    When i search on the web, I see some people with the same problem but don’t find a solution 🙁
    Microsoft himself seems to create separated API when you look the standard web services they published. I’m lost…
    DO you have some information about this error ?
    Thx for your help and sorry for my english.
    Regards.
    Pol.

    • Yes, I have seen this error before. This happens when the JSON you are posting does not match the specification of the API page.

      • Hello Ajk,
        Thx a lot for your anwser,! Yes, my maneer to use Postman was wrong.
        Have a good day and thx again for all your shares.
        Regards.
        Pol.,

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.