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!

36 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.,

  4. Thanks for the informative post, I created a custom API for ‘Transfer Header’ table (no exist API for it by BC) and I try to insert a header into the table, but I was getting:


    You may not enter numbers manually. If you want to enter numbers manually, please activate Manual Nos. in No. Series T-ORD.

    Then I stopped post the ‘No’ inside the request and used your way for calculating it, But now I am getting different error:

    {
    “error”: {
    “code”: “Internal_RecordNotFound”,
    “message”: “The Transfer Header does not exist. Identification fields and values: No.=” CorrelationId: .”
    }
    }

    I try to post required fields:

    {

    “directTransfer”: true,

    “noSeries”: “T-ORD”,

    “status”: “Open”,

    “transferfromCode”: “LocCode1”,

    “transfertoCode”: “LocCode2”

    }

    or send more data, but keep getting same error, Could you please help me find a way to insert the transfer order(header) by use the custom API? (by the way, I created another API for insert lines, and it works fine if the header exist)

    • Wow, BC not accept insert “directTransfer” equal to true, it should be false to be inserted for first time! However, when In-Transit Code is inserted in the post body, then it will be accepted to insert directTransfer as equal true, this is strange, should be the opposite!

    • In general, when you assign a value to a number field, then the number series that is set up for that field needs to allow for manual numbers. That’s standard behavior.
      If you don’t assign a number at all, not even calculate it, then the number should be assigned automatically.

      Did you link header and lines with the systemid, and not the header no.?

  5. Hi Arend-Jan, great thanks for your clarifying blog. Short question. Could you please confirm that after api v 2.0 was announced this realization is still working and still optimal way for doing deep inserts?

    • Yes, this works with API v2.0 as well, no changes in this area. So it is still the optimal way for deep inserts.

      • Thank you for quick answer. Could you please help with next issue? I tried to play with your example and created get request to see tree structure but got next:

        {
        “error”: {
        “code”: “BadRequest”,
        “message”: “Could not find a property named ‘lines’ on type ‘NAV.headers’. CorrelationId: 2439bdd3-2a59-4a58-8076-e468d116a90c.”
        }
        }

        My request:

        api.businesscentral.dynamics.com/v2.0/b78d2413-1f07-458f-ab80-4afda55509ca/Sandbox3/ODataV4/Company(’52b76c08-3375-eb11-b853-000d3a1de618′)/headers?$expand=lines

        request type – GET
        Authorization – OAuth 2.0
        i took this URL from Web Services list where i added page “My Header API” manually

        Also i couldn’t run post request with the same URL and body from you example(with deep insert, first line, second line) as received same error

        Is there any quick fix here or these details are not enough? thanks in advance

        • The answer is that OData V4 endpoint doesn’t support deep inserts. I changed url to the same structure as in your example and now it works. thank you again for this example.

  6. Hi Arend-Jan,

    Your blog is very instructive, Thanks a lot 🙂

    I need “Description 2” in line, so, I follow your recommendation “to take a look at the standard APIs, copy it and adjust to your needs”.
    I’ve copied the 2 pages “Sales order Entity” and “Sales Order line Entity”, changed just a few lines (“Description 2″ already exists in ” Sales Invoice Line Aggregate”) and all is fine, except I got 2 warnings on the copy of “Sales Order Entity ” API page :

    Codeunit ‘Graph Int. – Contact’ is marked for removal. Reason: This functionality will be removed. The API that it was integrating to was discontinued.. Tag: 17.0.AL(AL0432)

    ‘ODataEDMType’ is being deprecated in the versions: ‘6.0’ or greater. This warning will become an error in a future release.AL(AL0667)

    And 4 distinct warnings on the copy of “Sales Order Line Entity ” API page :

    Table ‘Sales Invoice Line Aggregate’ is marked for removal. Reason: Table will be marked as TableType=Temporary. Make sure you are not using this table to store records. Tag: 17.0.AL(AL0432)

    ‘ODataEDMType’ is being deprecated in the versions: ‘6.0’ or greater. This warning will become an error in a future release.AL(AL0667)

    Table ‘Integration Record’ is marked for removal. Reason: This functionality will be replaced by the systemID field. Tag: 15.0.AL(AL0432)

    Codeunit ‘Integration Management’ is marked for removal. Reason: Integration Management will be removed. Refactor the code to use systemID and other system fields such as systemLastModifiedDateTime.. Tag: 17.0.AL(AL0432)

    Codeunit ‘Integration Management’ is marked for removal. Reason: Integration Management will be removed. Refactor the code to use systemID and other system fields such as systemLastModifiedDateTim

    Do you know if these warnings will become errors in the next month release?

    • Thanks for the kind words Christophe. 😊

      Microsoft need to comply their own APIs with these warnings. My recommendation would be to just do the modification and wait for the next version of the standard APIs. Because the standard APIs are depending on codeunits in the base app, they need to follow any change in the base app. So do you when you copy the standard APIs, then you are also depending on codeunits in the base app.

  7. One more advice…
    The new orders, from a e-shop, are pre-paid by credit card and should-be release immediatly.
    How can I trigger the “Sales Order Release Management” codeunit from the same API call (after insert header and lines)?

    • That is not possible by default. With a custom API the best option would be to add a bound action to release the order and call it separately

  8. One more advice…
    The new orders, from an e-shop, are prepaid by creditcard, and should be release immediatly.
    How can I trigger “Sales Order Release Management” codeunit from the same API post ?

    • I have a custom API page for the sales header and add this code to it:
      [ServiceEnabled]
      procedure releaseOrder(var ActionContext: WebServiceActionContext)
      var
      ReleaseSalesDocument: Codeunit “Release Sales Document”;
      begin
      ReleaseSalesDocument.ReleaseSalesHeader(Rec, false);
      SetActionResponse(ActionContext, page::ICPSalesOrdersAPI, rec.SystemId);
      end;

      • URL to access:
        POST {{baseurl}}/{{tenantId}}/{{environment}}/api/{{APIPublisher}}/{{APIGroup}}/{{APIVersion}}/companies({{cid}})/salesOrderHeaders(‘order’,’101030′)/Microsoft.NAV.releaseOrder
        Authorization: Basic {{username}} {{password}}

        • Hi Dave,
          Thank you for your contribution, but what if I want to enter the order (without number at this time) and release it from the same single API call?

          • Unfortunately, that is hard to achieve. If Microsoft would have implemented batch calls in the right way, then it would be an option. But that’s a no go as it is today.

            The only (dirty) workaround would be to add a custom field on the sales lines API with the name ‘release’. On the last sales line (deep insert I guess), you set the field release to true.

            The field can be a global variable on the page. After the insert is completely ready, you can then call release if the global variable is true.

  9. Is it possible to do a deep insert of additional lines against an already existing header? for example, adding more sales to an existing header?

      • so I’ve created a buffer table to store the lotNumber and qtyToHandle that a 3PL used on a given inventory Pick Line. And I can do a POST (to insert a record to the buffer table) with this URI:

        {{jtbaseurl}}/companies({{Corp}})/inventoryPickLines({{inventoryPickLineId}})/whseActivityTrackingLines

        and with this body:

        {
        “qtyToHandle”: 0.1,
        “lotNumber”: “21-2522”
        }

        All works well.

        but when I try with $batch:

        {{jtbaseurl}}/companies({{Corp}})/inventoryPickLines({{inventoryPickLineId}})/whseActivityTrackingLines/$batch

        and with this body:

        {
        “requests”: [
        {
        “qtyToHandle”: 0.1,
        “lotNumber”: “21-2522”
        },
        {
        “qtyToHandle”: 0.2,
        “lotNumber”: “21-2523”
        }
        ]
        }

        I get the following error:

        “message”: “The request URI is not valid. Since the segment ‘whseActivityTrackingLines’ refers to a collection, this must be the last segment in the request URI or it must be followed by an function or action that can be bound to it otherwise all intermediate segments must refer to a single resource. CorrelationId: 80c95ebe-e775-40b7-baaf-1fdf142458b7.”

        something with my API definition that I need to fix?

        I can just repeat the call without $batch for each lotNumber/Qty combination. (Most will be a single lot# anyway). More just a learning exercise at this point what I am doing wrong. Thx.

  10. Regarding the possibility of arbitrary deep inserts, what speaks against the following “DataReceiver”-construct:
    -> Create a API Page suited to receive merely to things, i.e., consists merely of two fields, a “Process to be applied on Data” field and a “Data” field. The latter is a text field which is to be feeded with a json message containing all the data to be processed. The former is to be feeded, e.g., with a code which defines the BC process to be carried out “with the data”.
    -> via an enum / interface mechanism, you provide the system with all possible processes to be potentially carried out on the data and at the same time, you create extensibility …
    -> the API Page “lives” on a table “Data Receiver Log” (“Entry No.” Integer, “Process to be applied on Data” Code[20], “Data” Blob.
    -> OnInsert, you create the Log Entry and perform elementary checks like if there is a interface reakization corresponding to the “Process to be applied”, if the “Data” is well formatted and so on and
    -> perform the arbitrary deep insert by running the “Process” on the data. All in one single transaction, if you want.
    -> you implement proper error reporting and, perhaps, create the possibility to manually work on erroneous data in BC …
    -> on the side, you get a protocol of all “incoming API activity” which minimizes the need of discussions “who provided / received which data” … and you have a single, “simple access point” …

    From a business central point of view, I can’t see any disadvantage in using this “Data Receiver”
    From a “Consumer point of view”, the Data Receiver might be somewhot harder to address?

    I would be really interested in your opinion regarding this DataReceiver construct where a deep insert problem does not exist at all …

    • In general, nothing wrong with a design like this. It’s basically a free format, the data part can be any structure.

      A few comments:
      * I would rather use unbound actions for this design. The return of the API page would ‘echo’ what you post to it, while a codeunit can return any type of result.
      * The data part needs to be text, so it needs to be serialized JSON. But it could also simply be text or XML.
      * This design reminds me of GraphQL, which is basically one endpoint accepting multiple request types

  11. Thank you for sharing all your discoveries 🙂

    Using v17.5 (Platform 17.0.22463.22994 + Application 17.5.22499.23024):
    I replicated your header/line design including the ListPart page for lines – and added the line SystemId as field(id; Format(Rec.SystemId, 0, 4).ToLower())

    What ?… Compiler warning : AL0615
    “Field ‘SystemId’ is not specified as a source expression on page [my page name]. All fields specified in ODataKeyFields must be used as the source expression in a page control. This warning will become an error in a future release.”

    Something must have changed since you initially published this article…

    I then removed the Format() and replaced with: field(id; Rec.SystemId)
    Happy days 😉
    It turns out that Rec.SystemId now can be shown on any page type.

    Is this really correct (or am I dreaming) – and does this change how you would approach the deep insert scenario ?

Leave a Reply

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