Deep insert with Business Central APIs

5 May

Recently I got a question from a partner: is it possible to insert a sales order and lines all at once with a Business Central API? And the answer to that question is, yes, that is possible. However, little documentation is available about this feature. The only documentation I could find is in an article about API limits in Business Central. One of the recommendations is to reduce the number of calls is to do deep inserts. The body of the request can contain nested entities, e.g. a sales header and sales lines. The documentation shows a short example of inserting a Sales Quote and lines and that’s all you get.

Let’s take a close look at how to create deep insert requests. Examples are for the standard sales order endpoint.

To insert a sales order including lines, you need to send a request like below. Note: I’m using the url for my local docker instance, if you want to test against the cloud environment, then the host is https://api.businesscentral.dynamics.com and the path is /v2.0/{{tenant}}/{{environment}}/api/v1.0/companies({{companyId}})/salesOrders.

The first block shows the request url and the headers, the second block the JSON request body.

POST /api/v1.0/companies({{companyId}})/salesOrders HTTP/1.1
Host: https://bcsandbox.docker.local:7048/bc
Authorization: Basic QURNSU46U2VjcmV0UGFzc3dvcmQ=
Content-Type: application/json
{  
    "customerId": "{{customerId}}",
    "salesOrderLines": [
        {
            "itemId": "{{itemId}}",
            "quantity": 5
    	}
    ]
}

It is important to notice that the salesOrderLines key is a JSON array. It may contain any number of entities. So, if we were to insert three lines, then the request would look like this (ok, same item, but you get the idea):

{  
    "customerId": "{{customerId}}",
    "salesOrderLines": [
        {
            "itemId": "{{itemId}}",
            "quantity": 5
    	},
    	{
            "itemId": "{{itemId}}",
            "quantity": 3
    	},
        {
            "itemId": "{{itemId}}",
            "quantity": 8
    	}
    ]
}

Let’s have a look at the result of the last request, with three lines. The response that comes back looks like this (use the scrollbar to see the complete payload).

{
    "@odata.context": "https://bcsandbox.docker.local:7048/bc/api/v1.0/$metadata#companies(9c4f2ddc-9e75-ea11-bbf0-000d3a38a583)/salesOrders/$entity",
    "@odata.etag": "W/\"JzQ0OzNtN240VjJmek4rdXY1RW5hWSszcDVUM09PeDZYV0labTg4aXlHSnRKWkU9MTswMDsn\"",
    "id": "f3af2160-227c-ea11-9be0-d0e787b03942",
    "number": "S-ORD101008",
    "externalDocumentNumber": "",
    "orderDate": "2020-04-11",
    "postingDate": "2020-04-11",
    "customerId": "82fe170f-9f75-ea11-bbf0-000d3a38a583",
    "contactId": "",
    "customerNumber": "10000",
    "customerName": "Adatum Corporation",
    "billToName": "Adatum Corporation",
    "billToCustomerId": "82fe170f-9f75-ea11-bbf0-000d3a38a583",
    "billToCustomerNumber": "10000",
    "shipToName": "Adatum Corporation",
    "shipToContact": "Robert Townes",
    "currencyId": "00000000-0000-0000-0000-000000000000",
    "currencyCode": "USD",
    "pricesIncludeTax": false,
    "paymentTermsId": "6cc81e09-9f75-ea11-bbf0-000d3a38a583",
    "shipmentMethodId": "00000000-0000-0000-0000-000000000000",
    "salesperson": "PS",
    "partialShipping": true,
    "requestedDeliveryDate": "0001-01-01",
    "discountAmount": 0,
    "discountAppliedBeforeTax": true,
    "totalAmountExcludingTax": 16012.8,
    "totalTaxAmount": 960.77,
    "totalAmountIncludingTax": 16973.57,
    "fullyShipped": true,
    "status": "Draft",
    "lastModifiedDateTime": "2020-04-11T18:29:28.41Z",
    "phoneNumber": "",
    "email": "robert.townes@contoso.com",
    "sellingPostalAddress": {
        "street": "192 Market Square",
        "city": "Atlanta",
        "state": "GA",
        "countryLetterCode": "US",
        "postalCode": "31772"
    },
    "billingPostalAddress": {
        "street": "192 Market Square",
        "city": "Atlanta",
        "state": "GA",
        "countryLetterCode": "US",
        "postalCode": "31772"
    },
    "shippingPostalAddress": {
        "street": "192 Market Square",
        "city": "Atlanta",
        "state": "GA",
        "countryLetterCode": "US",
        "postalCode": "31772"
    }
}

Wait… where are the lines? It doesn’t return the lines! That’s because we didn’t ask… Another feature that is mentioned in the documentation about API limits is to use Expand to fetch related entries. It reduces the number of calls. With expand the result will contain both header and lines.

So we change the POST request to include the sales lines. Notice the extra ?$expand=salesOrderLines at the end of the url. The entity to expand is the same as the entity in the request body to specify the lines. That’s not by coincidence of course… 😊 On a side note, the $expand parameter also works on GET commands.

POST /api/v1.0/companies({{companyId}})/salesOrders?$expand=salesOrderLines HTTP/1.1
Host: https://bcsandbox.docker.local:7048/bc
Authorization: Basic QURNSU46U2VjcmV0UGFzc3dvcmQ=
Content-Type: application/json

And this is the result. Scroll down to see that the sales lines are now included.

{
    "@odata.context": "https://bcsandbox.docker.local:7048/bc/api/v1.0/$metadata#companies(9c4f2ddc-9e75-ea11-bbf0-000d3a38a583)/salesOrders/$entity",
    "@odata.etag": "W/\"JzQ0O2NXMUpaWEtsdk45bnN6dG0rOFJlSTExTVNjZUtHbVg0ZGNjTm5IU0g4TTA9MTswMDsn\"",
    "id": "8ac93e25-237c-ea11-9be0-d0e787b03942",
    "number": "S-ORD101009",
    "externalDocumentNumber": "",
    "orderDate": "2020-04-11",
    "postingDate": "2020-04-11",
    "customerId": "82fe170f-9f75-ea11-bbf0-000d3a38a583",
    "contactId": "",
    "customerNumber": "10000",
    "customerName": "Adatum Corporation",
    "billToName": "Adatum Corporation",
    "billToCustomerId": "82fe170f-9f75-ea11-bbf0-000d3a38a583",
    "billToCustomerNumber": "10000",
    "shipToName": "Adatum Corporation",
    "shipToContact": "Robert Townes",
    "currencyId": "00000000-0000-0000-0000-000000000000",
    "currencyCode": "USD",
    "pricesIncludeTax": false,
    "paymentTermsId": "6cc81e09-9f75-ea11-bbf0-000d3a38a583",
    "shipmentMethodId": "00000000-0000-0000-0000-000000000000",
    "salesperson": "PS",
    "partialShipping": true,
    "requestedDeliveryDate": "0001-01-01",
    "discountAmount": 0,
    "discountAppliedBeforeTax": true,
    "totalAmountExcludingTax": 16012.8,
    "totalTaxAmount": 960.77,
    "totalAmountIncludingTax": 16973.57,
    "fullyShipped": true,
    "status": "Draft",
    "lastModifiedDateTime": "2020-04-11T18:34:59.11Z",
    "phoneNumber": "",
    "email": "robert.townes@contoso.com",
    "sellingPostalAddress": {
        "street": "192 Market Square",
        "city": "Atlanta",
        "state": "GA",
        "countryLetterCode": "US",
        "postalCode": "31772"
    },
    "billingPostalAddress": {
        "street": "192 Market Square",
        "city": "Atlanta",
        "state": "GA",
        "countryLetterCode": "US",
        "postalCode": "31772"
    },
    "shippingPostalAddress": {
        "street": "192 Market Square",
        "city": "Atlanta",
        "state": "GA",
        "countryLetterCode": "US",
        "postalCode": "31772"
    },
    "salesOrderLines": [
        {
            "@odata.etag": "W/\"JzQ0O0laTE8xb0NZU2hERlZMclNWaFg2djBCM0J2ZnRJeEVackU1VDZOMTJGYzQ9MTswMDsn\"",
            "id": "8ac93e25-237c-ea11-9be0-d0e787b03942-10000",
            "documentId": "8ac93e25-237c-ea11-9be0-d0e787b03942",
            "sequence": 10000,
            "itemId": "96fe170f-9f75-ea11-bbf0-000d3a38a583",
            "accountId": "00000000-0000-0000-0000-000000000000",
            "lineType": "Item",
            "description": "ATHENS Desk",
            "unitOfMeasureId": "17ff170f-9f75-ea11-bbf0-000d3a38a583",
            "quantity": 5,
            "unitPrice": 1000.8,
            "discountAmount": 0,
            "discountPercent": 0,
            "discountAppliedBeforeTax": false,
            "amountExcludingTax": 5004,
            "taxCode": "FURNITURE",
            "taxPercent": 6,
            "totalTaxAmount": 300.24,
            "amountIncludingTax": 5304.24,
            "invoiceDiscountAllocation": 0,
            "netAmount": 5004,
            "netTaxAmount": 300.24,
            "netAmountIncludingTax": 5304.24,
            "shipmentDate": "2020-04-11",
            "shippedQuantity": 0,
            "invoicedQuantity": 0,
            "invoiceQuantity": 5,
            "shipQuantity": 5,
            "lineDetails": {
                "number": "1896-S",
                "displayName": "ATHENS Desk"
            },
            "unitOfMeasure": {
                "code": "PCS",
                "displayName": "Piece",
                "symbol": null,
                "unitConversion": null
            }
        },
        {
            "@odata.etag": "W/\"JzQ0O3dzRk50S2xONTkyMkM2enRpQkVTTjN6bmNHa3gyczJuWnBWWlRCeUxrY0E9MTswMDsn\"",
            "id": "8ac93e25-237c-ea11-9be0-d0e787b03942-20000",
            "documentId": "8ac93e25-237c-ea11-9be0-d0e787b03942",
            "sequence": 20000,
            "itemId": "96fe170f-9f75-ea11-bbf0-000d3a38a583",
            "accountId": "00000000-0000-0000-0000-000000000000",
            "lineType": "Item",
            "description": "ATHENS Desk",
            "unitOfMeasureId": "17ff170f-9f75-ea11-bbf0-000d3a38a583",
            "quantity": 3,
            "unitPrice": 1000.8,
            "discountAmount": 0,
            "discountPercent": 0,
            "discountAppliedBeforeTax": false,
            "amountExcludingTax": 3002.4,
            "taxCode": "FURNITURE",
            "taxPercent": 5.99987,
            "totalTaxAmount": 180.14,
            "amountIncludingTax": 3182.54,
            "invoiceDiscountAllocation": 0,
            "netAmount": 3002.4,
            "netTaxAmount": 180.14,
            "netAmountIncludingTax": 3182.54,
            "shipmentDate": "2020-04-11",
            "shippedQuantity": 0,
            "invoicedQuantity": 0,
            "invoiceQuantity": 3,
            "shipQuantity": 3,
            "lineDetails": {
                "number": "1896-S",
                "displayName": "ATHENS Desk"
            },
            "unitOfMeasure": {
                "code": "PCS",
                "displayName": "Piece",
                "symbol": null,
                "unitConversion": null
            }
        },
        {
            "@odata.etag": "W/\"JzQ0O0hCNlc4R1pSZXJzSjgzQ2t6U25CeDZqbjl2R1NadVdVUWxjVGJVeE5yOEE9MTswMDsn\"",
            "id": "8ac93e25-237c-ea11-9be0-d0e787b03942-30000",
            "documentId": "8ac93e25-237c-ea11-9be0-d0e787b03942",
            "sequence": 30000,
            "itemId": "96fe170f-9f75-ea11-bbf0-000d3a38a583",
            "accountId": "00000000-0000-0000-0000-000000000000",
            "lineType": "Item",
            "description": "ATHENS Desk",
            "unitOfMeasureId": "17ff170f-9f75-ea11-bbf0-000d3a38a583",
            "quantity": 8,
            "unitPrice": 1000.8,
            "discountAmount": 0,
            "discountPercent": 0,
            "discountAppliedBeforeTax": false,
            "amountExcludingTax": 8006.4,
            "taxCode": "FURNITURE",
            "taxPercent": 6.00007,
            "totalTaxAmount": 480.39,
            "amountIncludingTax": 8486.79,
            "invoiceDiscountAllocation": 0,
            "netAmount": 8006.4,
            "netTaxAmount": 480.39,
            "netAmountIncludingTax": 8486.79,
            "shipmentDate": "2020-04-11",
            "shippedQuantity": 0,
            "invoicedQuantity": 0,
            "invoiceQuantity": 8,
            "shipQuantity": 8,
            "lineDetails": {
                "number": "1896-S",
                "displayName": "ATHENS Desk"
            },
            "unitOfMeasure": {
                "code": "PCS",
                "displayName": "Piece",
                "symbol": null,
                "unitConversion": null
            }
        }
    ]
}

Let’s have a look at the sales order API page. Below you see part of the source code of the Sales Order API v1.0. I’ve left out all code that is not interesting right now.

page 20028 "APIV1 - Sales Orders"
{
    APIVersion = 'v1.0';
    Caption = 'salesOrders', Locked = true;
    ChangeTrackingAllowed = true;
    DelayedInsert = true;
    EntityName = 'salesOrder';
    EntitySetName = 'salesOrders';
    ODataKeyFields = Id;
    PageType = API;
    SourceTable = "Sales Order Entity Buffer";
    Extensible = false;

    layout
    {
        area(content)
        {
            repeater(Group)
            {
                field(id; Id)
                {
                    ApplicationArea = All;
                    Caption = 'id', Locked = true;
                    Editable = false;

                    trigger OnValidate()
                    begin
                        RegisterFieldSet(FIELDNO(Id));
                    end;
                }
                
                part(salesOrderLines; 20044)
                {
                    ApplicationArea = All;
                    Caption = 'Lines', Locked = true;
                    EntityName = 'salesOrderLine';
                    EntitySetName = 'salesOrderLines';
                    SubPageLink = "Document Id" = FIELD(Id);
                }
            }
        }
    }
}

As you can see, salesOrderLines is a pagepart. On a side note, it is inside a repeater, ever seen that before? Anyway, what is important here is the EntitySetName. That’s the name that is exposed in the API endpoint, and that’s what you need to add to the $expand= parameter. It is also the name that is used in the request body for the deep insert. This is a case-sensitive value!

Deep insert with custom API’s

Another frequently asked question is if it is possible to extend API’s. Unfortunately, that’s not the case. Note the Extensible = false property in the source code of the standard API page above. If you want to support custom fields in API’s, then you need to create custom API’s. All features, like expand and deep insert, are also available for custom API’s. But beware! I’ve spent a lot of time creating custom API’s that support both direct insert and deep insert, and it can really drive you crazy. The behavior of subpages for direct inserts is not 100% the same as for deep inserts. Actually, this blog post originally had another part about how to support deep inserts in a custom sales order API. Unfortunately, I ran into too many issues and the blog post became way too complex with different workarounds and a lot of text to explain why the workarounds were needed. So, instead of going down that rabbit hole here, I’ve reached out to Microsoft to share my findings.

What I would recommend if you want to create a custom API that supports deep inserts and direct inserts, is to look at the source code of the standard API’s. Make a copy of it and add your own fields. You may need to do some extra plumbing then, but it will save you a lot of time and frustration.

Next blog post will be about another API performance feature: multiple calls at once, aka batch calls. Stay tuned!

8 thoughts on “Deep insert with Business Central APIs

  1. Hello,

    Thanks for the great post, really helpful.

    I was wondering what the benefits are of using the “Sales Order Entity Buffer” as sourcetable in stead of the “Sales Header” table.
    Is this to avoid validation errors? Or the order in which the fields are validated?

    Thank you in advance!

    • API pages are working different from standard pages. You are right, the table “Sales Order Entity Buffer”, in combination with the codeunit “Graph Mgt – Sales Order Buffer”, is used to control the process in which records are inserted or modified. In that way validation errors can be avoided. I believe it is a good pattern to work with more complex API pages or tables that do have complex validation processes.

      The table “Sales Order Entity Buffer” keeps copies of all sales orders headers, also those who are inserted into the Sales Header table.
      Same goes for the table “Sales Invoice Entity Aggregate”, but that one goes a step further. It contains both unposted and posted sales invoices. Including their state, like draft, open (= posted, but unpaid) and paid. With this table it is possible to read sales invoices with their original id they got in the Sales Header table, also when they are posted and moved to the Posted Sales Invoice table.

  2. Great post, when I was working on this then it took me way to much time to figure out the EntitySetName needs to be the same as the name so that’s a very good point. Also there is always the question when to use deep insert and when complex types are the way to go. Also worth mentioning that by using complex types to your subpage you can add one more level to your API.

    • Complex types are always included in the json payload. But sub pages will become navigation properties, they can be loaded on demand or used directly in the url. Complex types don’t become part of the url. Complex types are certainly a good alternative, but there are some quirks with them. It’s covers basic scenario’s and using custom EDM types is not cloud safe currently. It can be used in the cloud, but should be handled with care.

  3. Pingback: Delta Tokens and Deep Inserts: a smart way to use API/ODATA … A real case with Shopify and Business Central (part 1) – Yet Another Business Central Blog

  4. Pingback: Deep insert with Business Central APIs (part 2) - Kauffmann @ Dynamics NAV - Dynamics 365 Business Central/NAV User Group - Dynamics User Group

  5. Great sharing! Appreciate it. And is it possible to have 3 blocks of data in deep insert?

    Example:
    Block #1 Header
    Block #2 Lines
    Block #3 Sublines

    Each line in Block #2 will have child records (Block #3).

    • Not sure if it is possible today. If not, then it will become possible in the future as far as I know.

Leave a Reply

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