Microsoft Business Central API (Beta) – Create a sales order and sales order line through PowerApps/Flow

At work I was experimenting with Microsoft PowerApps, Flow and the Business Central Beta API, trying to create a PowerApp that lets me create a sales order and the according sales order lines through Microsoft Flow. I ran into issues when I wanted to create a sales order line through Flow and got the following error message:

{
“status”: 400,
“message”: “ErrorCode: Application_DialogException You must specify a document id to get the lines.\r\nclientRequestId: bf31d2f6-3d8a-46d9-b0e6-0e8048352c7e”,
“source”: “api.businesscentral.dynamics.com”,
“errors”: []
}

So yeah, Microsoft Flow asks for a Document ID, but wait, there’s no field to specify the Document ID?

Luckily, Microsoft Flow supports HTTP requests which means that we can use the HTTP methods of the REST API’s to get our data. And that’s exactly what we are going to do. Follow through!

Side note: I assume you’re familiair with the basics of REST API’s, Microsoft Flow and PowerApps.

Prerequesites

The following is required to properly use the REST API of Microsoft Business Central:

  • Access to a Dynamics 365 Business Central environment.
  • Credentials necessary to connect and authenticate against the API. In this example I’m using basic authentication that works with a Web Service Access key. I’ll explain in this guide on how to get your key.
  • Your tenant ID. Your tenant ID can be found in the URL of Business Central, like shown below. You can also find your tenant ID in Microsoft Azure AD.

  • Access to Microsoft Flow.
  • Optional: a PowerApp that uses the Flow. Showing how to create a PowerApp is out of scope for this guide, but I’ll use my PowerApp to execute my Flow. There are plenty of guides on PowerApps on Google and YouTube :-).

Acquiring a Web Service Access key

You need a Web Service Access key to connect and authenticate against the Business Central API. In Business Central, do the following:

  1. Use the search function in BC. Located in the upper right corner or use Alt + Q. Search for Users and click on it.
  2. Locate your user name and click on it.
  3. On the user card page, your user name that will be used for authentication is on the General tab. Write down your user name.
  4. Generate a new Web Service Access Key using the AssistEdit button or just copy the currently shown web key. If this field is blank, generate a new key. Write down the key, we need this in Microsoft Flow.
  5. We’re done in Business Central for now. Next step is to configure Microsoft Flow.

Configuring Microsoft Flow

Allright, we have our Web Service Access Key. Great! Next step is to build a flow in Microsoft Flow that executes a POST request to the Business Central API so that the sales order lines will get created. I’ll show my complete Flow and guide you through step-by-step. You probably won’t need all the steps; suit the Flow to your needs.

  1. Step 1: make sure you have a connection with Microsoft Business Central in Microsoft Flow. I assume you are reading this guide because you can’t create sales order lines. Anyway, check your connections tab and make sure you have a connection with BC. Screenshot below is in Dutch, I’m lazy and Dutch ;-).
  2. Create a new Flow in Microsoft Flow. Easy peasy, not going to explain this.
  3. Open your newly created Flow.
  4. Time to explain the Flow I created before. Follow along and at the end you’ll have a Flow that creates sales order lines.

Creating the sales order lines Flow

The screenshot below shows the Flow that I used to create sales order lines. I’ll explain every step along the way; you don’t need all of them for it to work. The steps required are numbered from 1 to 4.

PowerApps:

  • This trigger step is required to run a Flow from a PowerApp. If you want to run your Flow through a PowerApp, use this step. If you don’t, you don’t need it.

Get My Profile (V2):

  • Gets your Office 365 profile information for use in Flow. Not required, but nice if you want to make your mails/messages more personal. I usually use this step in combination with the O365 Outlook connector.

Variable step:

  • This step is not mandatory, but I used a variable because I use this Flow in PowerApps. This step stores the item amount the user gave up in the PowerApp and saves it in the variable for later use. If you use this step, give it a logical name like any variable, set the Type to Integer and the Value to: Ask in PowerApps.

Create a empty Sales Order (MANDATORY):

Here come the mandatory steps. Step 1 is to create a empty sales order action. Click New Step in Flow -> Search for Business Central -> Actions -> Create Item.

Next up, select the Company that the step applies to and select the Table. For this example I’m using the Cronus demo company and the salesOrders table. Then, fill the following fields:

  • orderDate: use a function to automatically fill the date. Here’s the expression I used: formatDateTime(utcNow(),’yyyy-MM-dd’).
  • customerNumber: I selected customer 10000 for this example. You can also ask this in PowerApps if you want. For now, just use a dummy.

Get the items that we need to add to the sales order (MANDATORY):

We just created the step to create an empty sales order in Flow. Next step is to get the item info of the items that need to be added to the sales order. Perform the following steps:

  • Insert a new step in Flow -> Select Business Central connector -> Get item. Fill in the company name and the table name. Company name in this example is Cronus, table is items.
  • Next up we need the Row ID of the item that needs to be added to the sales order line. I use PowerApps to dynamically determine the Row ID of an item. You can also use the Table Viewer in Business Central to determine ID of the item you want to add to your sales order: https://businesscentral.dynamics.com/?table=27. Scroll to the right to see a list of ID’s that you can fill in.

Fill in the selected Row ID in Microsoft Flow.

Final step – use a POST call to create a sales order line (MANDATORY):

  • Add a new step in PowerApps -> Search for HTTP -> select the basic HTTP connector. Don’t choose the Swagger or Webhook connector.
  • Fill in the following information:
    • Method: POST.
    • URI: https://api.businesscentral.dynamics.com/v1.0/<yourTenantID>/api/beta/companies(<company ID>)/salesOrders(<Sales order number>)/salesOrderLines
    • Use the following JSON body:
      • {
        “itemId”: “<item id retrieved in previous step>“,
        “lineType”: “Item”,
        “quantity”: <fill in number or use variable in Flow>
        }
    • Click Show advanced options.
    • Verification Type: Basic.
    • Fill in the username and password you’ve retreived in the step “Acquiring a Web Acess Key”.

See the screenshot below of the configuration I used. I’ve hidden my Tenant ID.

Executing the Flow

We just created a Flow that creates an empty sales order, retrieves an item based on an item ID and creates sales order lines. Next step is to test the Flow and ensures it works.

  • While your Flow is in Edit mode, right click on Test in the upper right corner.
  • Select “I will execute the trigger activity”. You can also use the previous values if you like.
  • Save and test.

If executed succesfully, all steps will have a green check. I’ll show you the most important inputs below.

Input of the HTTP POST we executed

And the output in Business Central:

 

18 Comments

  1. great stuff Ruud.

    quick question: I am connecting CE (Accounts) with BC (Customers) using Flow/LogicApps. Upon the integration using the Microsoft Dynamics 365 connector (Out of the box), I see NavIntegrationId gets created in CE which connects both the tables.

    I am trying to get this integration id from Accounts and then update the relevant customer in BC based on some logic. The problem is I cannot find any filter based on which I can fetch the relevant customer from BC. There is no common field between both tables (Accounts and customers).

    Did you come across any such scenario?

  2. What if you want to insert multiple lines on the order? Do you have to make multiple API calls (one per line), or is there a way to add multiple lines for the order in one call?

  3. Hi Ruud,

    I have trying to replicate the mandatory parts of your flow. However, i am using “purchasedocuments” and “purchasedocumentlines”

    I am yet to succeed as the HTTP POST will not work.

    For input i have:
    method = POST
    URI= https://api.businesscentral.dynamics.com/v2.0/%5BTENANT
    ID]/Production/ODataV4/Company(‘CRONUS%20Danmark%20A%2FS’)/purchaseDocuments(615271de-e314-ea11-a813-000d3a6507ad)/purchaseDocumentLines

    JSON:
    {
    “lineType”: “Item”,
    “quantity”: “10”,
    “rowId”: “d580ec69-2f00-4275-8390-03a4eac3f0be”
    }

    Verification: Basic.

    The output i recieve is just an error-message:
    {
    “error”: {
    “code”: “”,
    “message”: “No HTTP resource was found that matches the request URI
    }
    }

    Do you have any suggestion for this?

    1. Hi Jacob! PurchasedocumentLines is not a valid endpoint in the standard Business Central API; therefore there’s no resource that you can call. You can find a list of valid endpoints here: https://docs.microsoft.com/en-us/dynamics-nav/api-reference/v1.0/. Valid purchase related endpoints are:
      – Purchase invoice / purchase invoice lines.
      – Vendor purchases.

      If you want to connect to different data, you’ll need a webservice/custom connector. That’s a different topic.

      1. Hi Ruud,

        Yes, i figured that out yesterday afternoon talking to a colleague. Thanks anyway – When not using invald endpoints, it works perfectly

  4. Hi Ruud,

    Great post 🙂 We’re trying to do something similar but rather than a sales order/lines, we would like to import data via PowerApps/Flows to a custom table in our SaaS Business Central. In one of your previous replies you recommended a webservice/custom connector. Would you be able to provide us more insight into this, please? Thank you in advance! 🙂

    1. Hi Yanika, thanks for the comment! 🙂 If I understand your question correctly, you’d like to write data to your custom table? Is there a page connected to this table? Your best bet would be to publish the page as a webservice, so you can use the full capabilities of OData webservices in your Power App/Power Automate. That includes reading, writing, updating and deleting data in your custom table. You can consume the OData webservice using the HTTP connector. Link to MS Docs: https://docs.microsoft.com/en-us/dynamics365/business-central/across-how-publish-web-service.

      If you need to load data from a custom table to Power Apps, use CDS: https://www.cloudfronts.com/how-to-fetch-a-custom-table-created-in-business-central-into-powerapps-using-cds-and-odata/.

      Good luck!

  5. Hi Ruud,

    Thanks for sharing this post. I’m using the Business Central Connector (Preview) and can successfully create a Sales order with a salesorder line, but only with a hardcoded itemId.

    Do you know how to retrieve the ItemId programmatically so I can use that itemId in my salesOrderLine call?
    Thanks

  6. Hi Ruud,

    Great post you have there. 🙂

    I am playing around with the Business Central Connector (Preview) in LogicApp, and there is just one thing I cant understand.
    When you say you have a dynamic way of getting the “Row ID” of an item, how exactly do you do that?

    im creating a new SalesOrder with SalesOrderLines, but I need to be able to “search for an item by name, GTIN, or whatever” and then get the “Row ID” of the item, to actually make it valuable .

    Can you please share how you do that?

    Thanks

    1. Hey Haakon, thanks for your reply! 🙂 You can use the Business Central Connector (preview) to get all the item master data from your Business Central company, including the unique Item ID’s (this is what I meant with Row ID). You can then add the Item ID’s to a Power Apps Collection, then pass the collection as JSON (there’s a JSON function in Power Apps) to Power Automate. Then in Power Automate, use a HTTP call to the Business Central API to insert one or multiple sales lines in your sales order. Use the JSON as request body.

      Are you familair with the concepts described above? If some more clarification is necessary I’d be happy to hear.

      1. Thanks Ruud.

        Im not sure however how to get all items (inc master data ) rom BC using the BC-Connector in LogicApps.
        When selecting “Get Record” using table name “Items”, one of the required fields are Row ID which is what im actually looking for in the first place So it seams to me there is no list functionality available ..

        1. Hey Haakon,

          A little bit of a late reply, but I can dedicate a post to this in the near future (within 1 or 2 weeks) describing the concepts that are necessary to make this work. Just let me know if you are still in need of this :-).

  7. Hello Rudd,

    Thanks much for this post. This saves my frustration in getting powerapp and Dynamics connected. I followed your directions, but I am stuck in the HTTP Section. It says “Enter a valid json”. I am not sure why but I just copied and pasted what you were instructed.

    Thank you.

    1. Hi Julleus,

      If you copied and pasted the JSON shown below, that should work. Can you show a screenshot of the error you are getting?
      This blog post is a little bit outdated, since it’s over a year old. I’ll try and write a new post in the near future on how to dynamically retrieve items from a Power App and insert them into Business Central. Will also provide a sample app so that you can play around with that. Stay tuned.

      {
      “lineType”: “Item”,
      “quantity”: “10”,
      “rowId”: “d580ec69-2f00-4275-8390-03a4eac3f0be”
      }

  8. Rudd.,

    I noticed you mentioned it is possible to create multiple orderlines using API /salesOrderLines

    The following is what my request looks like :
    {
    “value”: [
    {
    “itemId”: “ItemGUIID-1”,
    “lineType”: “Item”,
    “quantity”: 1
    },
    {
    “itemId”: “ItemGUIID-2”,
    “lineType”: “Item”,
    “quantity”: 3
    }
    ]
    }

    But., I received the following response

    {
    “error”: {
    “code”: “BadRequest”,
    “message”: “Does not support untyped value in non-open type. CorrelationId: CorrelationID.”
    }
    }

    Can you please help me understand what is incorrect or what exactly am I missing here ?

    Thanks
    Bhavana

    1. Hi Bhavan,

      Just to check: did you create a sales header yet before inserting the sales lines? One thing I notice in your call is that you’re using “value” instead of salesOrderLines. The API can be picky about that.
      Try to use the example below :-). Also, Jan Kauffman has written an excellent post about deep inserts: https://www.kauffmann.nl/2020/05/05/deep-insert-with-business-central-apis/. Let me know if it works out for you.

      “salesOrderLines”: [
      {
      “itemId”: “{{itemId}}”,
      “quantity”: 5
      },
      {
      “itemId”: “{{itemId}}”,
      “quantity”: 3
      },
      {
      “itemId”: “{{itemId}}”,
      “quantity”: 8
      }
      ]

Leave a Comment