Dynamics 365 Business Central - Safety Stock Report with Power Automate

Recently on the D365BC WhatsApp community BC chat there was a question about getting alerts for items if their inventory was below the safety stock quantity. Dynamics 365 Business Central collected RSS feeds - Blogs, YouTube, LinkedIn...

Naturally, running the planning worksheet can pick this up About Planning Functionality - Business Central | Microsoft Learn, but maybe the users are not looking at this every day so an email reminder could be handy.

So, this is a good opportunity for Power Automate; here are the steps:

  1. First I created an API page for Items and Stock Keeping Units, the items page in the standard API didn't have Safety Stock Quantity and Stock Keeping Units was not present at all. You can grab the source code and compiled app here. GitHub - andywingate/Wingate356-API-pages
  2. Now I used the Stock Keeping Units API in the following flow: 


Here are the details on each step, adjust these to suite you needs! To make things easy I've just pasted the code, so you can paste this code into a step of the same type and you should have the exact same setup as I had. 

Recurrence
Set how often you want the safety stock check to be run, in my example its once per day
 
{
  "type": "Recurrence",
  "recurrence": {
    "frequency": "Day",
    "interval": "1",
    "startTime": "2024-10-21T09:00:00.000Z"
  }
}

Find records
Get the records from the sku API page. Seems you can't use an ODATA filter to compare two numbers so instead getting the rows where the Inventory is less that the Safety Stock I do in the next action.

 {

  "type": "OpenApiConnection",
  "inputs": {
    "parameters": {
      "bcenvironment": "SANDBOX",
      "company": "330d8698-e486-ef11-ac21-7c1e5203581f",
      "dataset": "wingate365/allfieldapipages/v1.0",
      "table": "skus",
      "$filter": "locationCode eq 'SILVER'"
    },
    "host": {
      "apiId": "/providers/Microsoft.PowerApps/apis/shared_dynamicssmbsaas",
      "connection": "shared_dynamicssmbsaas",
      "operationId": "GetItemsV3"
    }
  },
  "runAfter": {}
}  

Filter Array
This filter was put in to set reduce the data down to only those SKUs where the inventory was lower than the Safety Stock Quantity. 

{
  "type": "Query",
  "inputs": {
    "from": "@outputs('Find_records_(V3)')?['body/value']",
    "where": "@less(item()?['inventory'],item()?['safetyStockQuantity'])"
  },
  "runAfter": {
    "Find_records_(V3)": [
      "Succeeded"
    ]
  }
}

Select
Used to select and rename the columns I wanted in the table

{
  "type": "Select",
  "inputs": {
    "from": "@body('Filter_array')",
    "select": {
      "Item": "@item()?['itemNo']",
      "Location": "@item()?['locationCode']",
      "Inv.": "@item()?['inventory']",
      "Safety Stock": "@item()?['safetyStockQuantity']"
    }
  },
  "runAfter": {
    "Filter_array": [
      "Succeeded"
    ]
  }
}

Create HTML Table
Used to convert the JSON array to an HTML table, as that's a nicer way to send the data!

{
  "type": "Table",
  "inputs": {
    "from": "@body('Select')",
    "format": "HTML"
  },
  "runAfter": {
    "Select": [
      "Succeeded"
    ]
  }
}

Compose
The plain HTML table can be improved with a bit of CSS, the idea is from Power Automate HTML Table Styling – Ryan Maclean

{
  "type": "Compose",
  "inputs": "<style>\ntable {\n  border: 1px solid #1C6EA4;\n  
            background-color: #EEEEEE;\n  width: 100%;\n  text-align: left;\n  
            border-collapse: collapse;\n}\ntable td, table th {\n  border: 1px solid #AAAAAA;\n  
            padding: 3px 2px;\n}\ntable tbody td {\n  font-size: 13px;\n}\ntable thead {\n
             background: #1C6EA4;\n  border-bottom: 2px solid #444444;\n}\ntable thead th {\n  
            font-size: 15px;\n  font-weight: bold;\n  color: #FFFFFF;\n  border-left: 2px
            solid #D0E4F5;\n}\ntable thead th:first-child {\n  border-left: none;\n}\n</style>
            \n@{body('Create_HTML_table')}",
  "runAfter": {
    "Create_HTML_table": [
      "Succeeded"
    ]
  }
}

Condition
This seems the best way to determine if there are any rows or not, why 30 you ask? Because the output from Create HTML table with no rows is 30 characters: <table><tbody></tbody></table>

{
  "type": "If",
  "expression": {
    "and": [
      {
        "greater": [
          "@length(body('Create_HTML_table'))",
          30
        ]
      }
    ]
  },
  "actions": {
    "Send_an_email_(V2)": {
      "type": "OpenApiConnection",
      "inputs": {
        "parameters": {
          "emailMessage/To": "admin@CRMbc589530.onmicrosoft.com",
          "emailMessage/Subject": "Safety Stock Alert",
          "emailMessage/Body": "<p class=\"editor-paragraph\">The following
                            items need to be re-ordered</p><br><p class=\"editor-paragraph\">
                            @{outputs('Compose')}</p>",
          "emailMessage/Importance": "Normal"
        },
        "host": {
          "apiId": "/providers/Microsoft.PowerApps/apis/shared_office365",
          "connection": "shared_office365",
          "operationId": "SendEmailV2"
        }
      }
    }
  },
  "else": {
    "actions": {}
  },
  "runAfter": {
    "Compose": [
      "Succeeded"
    ]
  }
}

What do you think?

Please let me know your thoughts in the comments

Connect or follow me on LinkedIn to get all my updates Andrew Wingate | LinkedIn