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!

27 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.

  6. Pingback: Custom API order – My Business Central Diary

  7. Great post.

    I would like to ask you about the API in BC17. Now we have salesDocuments and salesDocumentsworkflowSalesDocumentLines instead salesOrders and salesLines.

    When I GET with expand (salesDocuments?$expand=salesDocumentsworkflowSalesDocumentLines) It is working fine.

    But when I try to insert a line with POST, I only can insert the header but not the lines.

    {
    “documentType”: “Order”,
    “sellToCustomerNumber”: “CLI0001”,
    “salesDocumentsworkflowSalesDocumentLines”: [
    {
    “type”: “Item”,
    “number”: “959308”,
    “quantity”: 100
    }
    ]
    }

    Any idea about the problem. As you say, it am getting crazy trying with different ways.

    Thanks in advance.

    Roberto Corella

    • It seems you are using UI page web services, and not APIs. I’ve done some testing with those and it looks like they are not supporting deep inserts. I would suggest to switch to APIs instead of UI page web services.

  8. Great post. I read this blog many times now. There are a lot of client asking for interfacing with API now.
    Just to share, we have a problem with “DiscountAmount” (Invoice Discount Amount) on API Sales Order.
    Basically system will automatically split this to be inserted on Sales Order Lines.
    What happened is that, when u send json Insert with DiscountAmount, it will raise error. Because lines hasn’t been inserted.
    So we need to insert than update which is not good.
    Is there any way to do it in one go? any advise. Thanks

    • I’m afraid this is not possible. You are right, the lines have not been inserted, so the header can’t distribute the discount amongst the lines.

      The only option I see here is that you include the distributed discount amount on the lines. Or do insert and then update. There is no other way as far as I can see.

  9. Hello,

    We are experimenting with deep insert by Odata against BC18 but, even though the main and sub page are both API pages, the OnInsert trigger of the sub page is not hit. In our metadata, the entity name for the lines is generated as WebserviceEntityname instead of just Entityname

    What can be wrong?

    Thanks

    Main page:

    page 50210 “2C Merk Import”
    {
    Caption = ‘MerkImport’;
    PageType = API;
    EntityName = ‘MerkImport’;
    EntitySetName = ‘MerkImport’;
    SourceTable = “2C Merk”;
    DelayedInsert = true;
    ODataKeyFields = “2C Merknaam”;
    APIPublisher = ‘EHTEST’;
    APIGroup = ‘Autos’;
    UsageCategory = Tasks;
    ApplicationArea = All;
    SourceTableTemporary = true;

    layout
    {
    area(content)
    {
    repeater(Group)
    {
    field(Merk; Rec.”2C Merknaam”)
    {
    ApplicationArea = All;
    }
    part(Modellen; “2C Modellen”)
    {
    SubPageLink = “2C Merk” = FIELD(“2C Merknaam”);
    EntityName = ‘Model’;
    EntitySetName = ‘Modellen’;
    }
    }
    }
    }

    trigger OnInsertRecord(BelowxRec: Boolean): Boolean –> this trigger runs correctly
    var
    Merk: Record “2C Merk”;
    begin
    CurrPage.Modellen.Page.SetMerk(Rec.”2C Merknaam”);

    if not Merk.get(Rec.”2C Merknaam”) then begin
    Merk.init;
    Merk.”2C Merknaam” := Rec.”2C Merknaam”;
    Merk.Insert();
    end;

    exit(true);
    end;
    }

    Sub page:

    page 50211 “2C Modellen”
    {
    Caption = ‘AB Modellen’;
    SourceTable = “2C Model”;
    DelayedInsert = true;
    SourceTableTemporary = true;
    PageType = API;
    EntityName = ‘Model’;
    EntitySetName = ‘Modellen’;
    APIPublisher = ‘EHTEST’;
    APIGroup = ‘Autos’;
    UsageCategory = None;

    layout
    {
    area(content)
    {
    repeater(Group)
    {
    field(Merknaam; Rec.”2C Merk”)
    {
    ApplicationArea = All;
    }
    field(Modelnaam; Rec.”2C Modelnaam”)
    {
    ApplicationArea = All;
    }
    }
    }
    }

    procedure SetMerk(MerkIN: Text) –> The value is set, so this proves that the OnInsert on the main page triggerrs
    begin
    MerkNaam := MerkIN;
    end;

    trigger OnInsertRecord(BelowxRec: Boolean): Boolean –> this trigger is not hit
    var
    ABModel: Record “2C Model”;
    begin
    Error(‘regel oninsert met record %1’, Rec);
    Rec.”2C Merk” := MerkNaam;

    if not (ABModel.Get(Rec.”2C Merk”, Rec.”2C Modelnaam”)) then begin
    ABModel.Init();
    ABModel.TransferFields(Rec);
    ABModel.Insert(true);
    end;
    exit(true);
    end;

    var
    MerkNaam: Text;
    }

    And the Odata Post:

    {​​​​​
    “Merk”: “Renault”,
    “MerkImportModellen”: [
    {​​​​​
    “Modelnaam”: “C4”
    }​​​​​,
    {​​​​​
    “Modelnaam”: “C3”
    }​​​​​,
    {​​​​​
    “Modelnaam”: “C4 Cactus”
    }​​​​​
    ]
    }​​​​​

  10. Having the same issue. I’ve read a couple of people say that deep inserts don’t work with OData endpoints but haven’t found anything official. Ever get this working?

  11. Hi

    I have written a sample codeunit 50100 function ReadJson2 to browse a JSON file, and this populates the customer masters into a staging table in BC and is working as expected.
    Kindly find below object

    But when I try to expose the same codeunit as Web Service and try to test my function using Postman, I am getting the below error. However the JSON example above is requested in the postman body and I am expecting the 3rd party API call to populate the staging data.

    API Request:

    {“Customers”:[{“customerNo”:”16933″,”customerName”:”ABC Private Limited”,”phone”:{“phoneCountryCode”:”+91″,”phoneCountryCode”:”9849098490″}},{“customerNo”:”17001″,”customerName”:”DEF Private Limited”,”phone”:{“phoneCountryCode”:”+91″,”phoneCountryCode”:”9849098491″}}]}

    API Response:

    {“error”:{“code”:”BadRequest”,”message”:”One or more errors occurred. CorrelationId: ab3dce35-f7b9-45ed-8ea7-7151bef5353e.”}}

    As per the understanding from Microsoft documentation (https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-creating-and-interacting-with-odatav4-unbound-action), example Data ({“inputJson”: “{\”str\”:\”Hello world!\”,\”confirm\”:true}”}) .and has been advised to follow the request body in Postman which is working fine for batch request. But 3rd party s not agreeing to send with slash separator and they require the input to be sent as like JSON array request through Text parameter.

    Kindly help or advise how do I get solution to meet the requirement in BC. Appreciate if you can share any sample working example which meets my criteria. Attaching the objects for references and also the postman collection.

    codeunit 50100 CustomJSONMgt
    {
    trigger OnRun()
    begin
    //ReadJSONData();
    ReadJSONData2();
    end;

    procedure ReadJSONData2()
    var
    LastEntryNo: Integer;
    InputFileName: Text;
    InStreamObj: InStream;
    InputToken: JsonToken;
    CustomersObject: JsonObject;
    CustomerTokenArray: JsonToken;
    CustObject: JsonObject;
    CustToken: JsonToken;
    PhoneObject: JsonObject;
    PhoneToken: JsonToken;
    CustNumber: Text;
    CustName: Text;
    PhoneCountryCode: Text;
    PhoneNumber: Text;
    PhoneData: Text;
    APITable: record 90001;
    begin
    // -input file
    if UploadIntoStream(‘Select File to Import’, ”, ‘*.*|*.json’, InputFileName, InStreamObj) then
    InputToken.ReadFrom(InStreamObj);
    // InputToken.ReadFrom(InputText);
    // +input file

    if not InputToken.IsObject then
    exit;

    LastEntryNo := 1;
    if APITable.FindLast() then
    LastEntryNo := APITable.”Entry No.” + 1;
    CustomersObject := InputToken.AsObject();
    if CustomersObject.Contains(‘Customers’) then begin
    if CustomersObject.Get(‘Customers’, CustomerTokenArray) then begin
    foreach CustToken in CustomerTokenArray.AsArray() do begin
    CustObject := CustToken.AsObject();
    CustObject.Get(‘customerNo’, CustToken);
    IF CustToken.IsValue THEN
    CustNumber := CustToken.AsValue().AsText();
    CustObject.Get(‘customerName’, CustToken);
    IF CustToken.IsValue THEN
    CustName := CustToken.AsValue().AsText();
    CustObject.Get(‘phone’, PhoneToken);
    if PhoneToken.IsObject then begin
    PhoneToken.WriteTo(PhoneData);
    PhoneObject.ReadFrom(PhoneData);
    PhoneObject.Get(‘phoneCountryCode’, PhoneToken);
    if PhoneToken.IsValue then
    PhoneCountryCode := PhoneToken.AsValue().AsText();
    PhoneObject.Get(‘phoneNumber’, PhoneToken);
    if PhoneToken.IsValue then
    PhoneNumber := PhoneToken.AsValue().AsText();
    end;
    if CustNumber ” then begin
    APITable.init;
    APITable.”Entry No.” := LastEntryNo;
    APITable.Insert(true);
    APITable.”Customer No.” := CustNumber;
    APITable.”Customer Name” := CustName;
    APITable.Phone_Country := PhoneCountryCode;
    APITable.Phone_No := PhoneNumber;
    APITable.Modify(true);
    LastEntryNo := LastEntryNo + 1;
    end;
    end;
    end;
    end;
    end;
    }

    • Unbound actions only support single-level JSON objects. Each property in the JSON needs to be a parameter in the function in the codeunit that is exposed as web service. For sending a more complex JSON it is required to use an escaped JSON value. Which is pretty simple to do in other languages.

      Only API pages support multi-level JSON with deep inserts.

  12. Thank you for your reply. Is there anyway to access codeUnit function without Body?

    Or Can we send any attached file in Body section? If yes, Pls share an sample?

    • The unbound action does not have to have a parameter. That means you can send an empty body. A body is required because unbound actions are called with the POST method. The body can simply be {}

      Unbound actions don’t support binary data. But you can send base64 encoded text values. So, instead of escaping the JSON you could write it to text and then base64 encode it. Which is in my opinion equally bad…

  13. Hello, I created the API but when I get data in postman so facing issues.
    API := https://api.businesscentral.dynamics.com/v2.0/tenant/environment/ODataV4/Company('CRONUS%20IN‘)/APIGLBudgets?$expand=GLBudgetsLine
    error := “error”: {
    “code”: “BadRequest”,
    “message”: “Could not find a property named ‘GLBudgetsLine’ on type ‘NAV.APIGLBudgets’. CorrelationId: 37d1f6d3-1b4f-474f-a1b7-7838dc0c02b9.”
    }

    code:
    page 50140 “API GL Budgets”
    {
    PageType = API;
    Caption = ‘APIGLBudgets’;
    APIVersion = ‘v2.0’;
    APIPublisher = ‘bctech’;
    APIGroup = ‘demo’;
    EntityName = ‘APIGLBudgets’;
    EntitySetName = ‘APIGLBudgets’;
    ChangeTrackingAllowed = true;
    ODataKeyFields = SystemId;
    SourceTable = “G/L Budget Name”;
    Extensible = false;
    DelayedInsert = true;

    layout
    {
    area(content)
    {
    repeater(Group)
    {
    field(“BudgetName”; Rec.”Budget Name”)
    {
    ApplicationArea = All;
    }
    field(Description; Rec.Description)
    {
    ApplicationArea = All;
    }
    field(BudgetDimension1Code; Rec.”Budget Dimension 1 Code”)
    {
    ApplicationArea = All;
    }
    field(BudgetDimension2Code; Rec.”Budget Dimension 2 Code”)
    {
    ApplicationArea = All;
    }
    }

    part(GLBudgetsLine; “Cus G/L Budget Entries”)
    {
    Caption = ‘GLBudgetsLine’;
    EntityName = ‘GLBudgetsLine’;
    EntitySetName = ‘GLBudgetsLine’;
    SubPageLink = “Budget Name” = field(Name);
    }
    }
    }
    }

  14. Hi,

    Can we actually do the opposite, is it possible to export purchase order and purchase line all at once using API page in json format as you shown above where header shows only once but lines are separated with curly braces as below. I have created using Query but it’s not coming in the below tree structure all the lines are combined with header. What’s need to be done In order to achieve below structure?
    {
    “customerId”: “{{customerId}}”,
    “salesOrderLines”: [
    {
    “itemId”: “{{itemId}}”,
    “quantity”: 5
    },
    {
    “itemId”: “{{itemId}}”,
    “quantity”: 3
    },
    {
    “itemId”: “{{itemId}}”,
    “quantity”: 8
    }
    ]
    }

    • A query API results in a table format without any nested objects. Just like a SQL query always returns data in a table format.

      What you are looking for is the standard API for the header and expand the lines.
      /purchaseOrders?$expand=purchaseOrderLines

      and if you want to reduce the fields, like your example:
      {{url}}/api/v2.0/companies({{companyId}})/purchaseOrders?$select=id,vendorId&$expand=purchaseOrderLines($select=itemId,quantity)

      Don’t forget to include the id in the $select because that is the link between header and lines. When you leave it out, the output will not be complete.

  15. I am writing to inquire about a technical issue. While testing a scenario using the method /salesOrders in an on-prem environment, I am receiving an error: BadRequest_NotFound. Specifically, I have noticed that the method /customers is only available in the beta version and not in versions 1.0 or 2.0. Could you please advise on what I can enable to make these methods available?
    Thank you for your assistance.

  16. Is it possible to release a Sales Order or A Sales Quote through API? How?
    I would expect it to be a bound action, but I cannot find anything in the documentation.

  17. The API wont let me create a sales order – it says: “You cannot create Sales Orders Directly, Create a Quote & Convert to an Order”. Why is that?

    I can create a quote but I cannot convert it to an order. When I use the makeOrder bound action it says: “Sold-From Location Code must have a value in Sales Header: Document Type=Quote”
    I cannot find how/where to set that field. I have set Location on the Customer and Sales Header via “Configuration template” but still doesn’t work.

    And how would I instantly mark a quote as Accepted? The customer has already paid for the item on the website, so all I really want to do is create the order.

    Thanks

    • This smells as a customization preventing you of creating sales orders directly. And apparently it has added a field “Sold-From Location Code” to the Sales Header. Which is not part of the standard APIs.

      The only solution would be to create a custom API that works with the customizations.

Leave a Reply

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