Deep insert with Business Central APIs

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!

   Loading...

   Reply

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

     Loading...

     Reply

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.

   Loading...

   Reply

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

     Loading...

     Reply

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

   Loading...

   Reply

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

     Loading...

     Reply

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

   Loading...

   Reply

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

     Loading...

     Reply

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

   Loading...

   Reply

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

     Loading...

     Reply

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”
   }​​​​​
   ]
   }​​​​​

   Loading...

   Reply

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?

    Loading...

    Reply

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;
    }

    Loading...

    Reply

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

      Loading...

      Reply

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?

    Loading...

    Reply

    • 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…

      Loading...

      Reply

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);
    }
    }
    }
    }

    Loading...

    Reply

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
    }
    ]
    }

    Loading...

    Reply

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

      Loading...

      Reply

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.

    Loading...

    Reply

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.

    Loading...

    Reply

    • No, there is no bound action to release a sales document.

      Loading...

      Reply

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

    Loading...

    Reply

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

      Loading...

      Reply

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