Power Automate has Business Central connectors, however, they are very limited. What Power Automate does not have are connectors to Business Central for the Web Services. You can locate these web services in Business Central under Web Services.
So how can you get access to the Web Services in Power Automate? Not that easy. But I figured it out with some sleuthing and piecing things together. This is a multi-step process and if you do not have admin rights to Azure/Business Central, you may need to contact your IT department to get them.
So the steps (read difficulties) I ran into getting this to work are the following:
- You need to authenticate against Business Central (Microsoft) using oAuth2.0 and get a bearer token
- You use the bearer token to call the web service that you need
- You get a JSON response
- You need to fiddle with the response and the Parse JSON did not work properly for me so I did need to MacGyver some of it
- You get variables and you can use them anywhere, in my case, I just wanted to populate an Excel table
Here is what I did:
- oAuth2.0 and Power Automate
- I got this information from reading this post: https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/How-to-use-OAuth2-0-in-Power-Automate-Custom-Connector/ba-p/1260216
- It is a very detailed account on how to set this up in Azure, but I will summarize it here.
- Go to Azure Portal
- Select Azure Active Directory
- Select App Registrations
- Click on New Registration – name it
- Leave it as a single tenant (at least this was my environment)
- I used this as the WEB redirect URL: https://businesscentral.dynamics.com/OAuthLanding.htm
- Click on Register
- Select Certificates & Secrets and then click on New Client Secret
- Remember, you MUST keep the secret (password) as it is shown only this once. But it’s not a big deal, as you can always create another one
- Now click on API permissions and make sure these permissions are added. So click on Add a permission and on the next screen select Microsoft APIs and look for Business Central. Select Business Central and then select DELEGATED PERMISSIONS. I selected app_access/Automation.ReadWrite.All/user_impersonation. I do not believe that you need to Financials.ReadWrite.All.
- After you are done that, you need to add another permission but this time, you need to choose Microsoft Graph. The select Delegated Permission and then add USER.READ.
- After you have done all that Don’t forget to GRANT ADMIN CONSENT after you add it!
- Click on the Overview tab, and make a note of the Client ID and Tenant ID. We will need those in a bit.
Now, go to Power Automate where we will create the create the steps required to access Business Central
- This is a fairly simple process and here is the Power Automate steps that we will require:
- Initialize a variable and assign it a name Username or UN. It is a STRING variable and paste the CLIENT ID that you got from Azure into the value field.
- Initialize another variable and assign it a name Password or PW. It is also a STRING variable and paste the SECRET (password) that you got from Azure into the value field
- Add a new step using COMPOSE. Add this line into the Expression (with all the quotes, but replace XXXX with the Client ID from above and YYYY with the Secret (password) from above:
- concat(‘grant_type=’,encodeUriComponent(‘client_credentials’),’&scope=’,encodeUriComponent(‘https://api.businesscentral.dynamics.com/.default’),’&client_id=’,encodeUriComponent(‘XXXX’),’&client_secret=’,encodeUriComponent(‘YYYY’))
- Now add an HTTP step
- The method is POST
- URI is (replace the zzzz with your TENANT ID from above https://login.microsoftonline.com/zzzz/oauth2/v2.0/token
- Under HEADERS add Content-Type as a key and assign this to the value: application/x-www-form-urlencoded
- Under QUERIES add client_id and client_secret as keys, and assign the values we got from above into them
- Under BODY put in the output from the COMPOSE step
- Now click on ADVANCED OPTIONS and use Authentication: BASIC and put in the username and password variables into username and password
- Add a new step and choose PARSE JSON. Use the Body from the previous step and use this as the schema:
{
"type": "object",
"properties": {
"token_type": {
"type": "string"
},
"expires_in": {
"type": "integer"
},
"ext_expires_in": {
"type": "integer"
},
"access_token": {
"type": "string"
}
}
}
- Add a new step HTTP again
- This time use GET as the Method
- the URI comes from the Business Central Web Services APIv2.0. Generally, the calls look like this (where xxxx is your tenant ID / cccc is your company name from business central / dddd is the webservice name that you gave: https://api.businesscentral.dynamics.com/v2.0/xxxx/Production/ODataV4/Company(‘cccc’)/dddd
- Under HEADERS add 3 keys and assign these values:
- Content-Type value to assign application/json
- Authorization value to assign Bearer and choose the variable “access token” from our previous step
- Accept: value to assign odata=nometadata
- Under ADVANCED OPTIONS, choose None for authentication
- The step above will return the information to you in a JSON format. In my case, I got the CUSTOMER CARD information from Business Central (this is Page 21)
- Add another step and use the Parse JSON function. Use the Body from the HTTP request above under CONTENT. Use this as the schema (note you may have additional fields for your customization):
{
"body": {
"type": "object",
"properties": {
"@@odata.context": {
"type": "string"
},
"value": {
"type": "array",
"items": {
"type": "object",
"properties": {
"@@odata.etag": {
"type": [
"string",
"null"
]
},
"No": {
"type": [
"string",
"null"
]
},
"Name": {
"type": [
"string",
"null"
]
},
"Name_2": {
"type": [
"string",
"null"
]
},
"Search_Name": {
"type": [
"string",
"null"
]
},
"IC_Partner_Code": {
"type": [
"string",
"null"
]
},
"Balance_LCY": {
"type": "integer"
},
"Balance_Due_LCY": {
"type": "integer"
},
"Credit_Limit_LCY": {
"type": "integer"
},
"Blocked": {
"type": [
"string",
"null"
]
},
"Privacy_Blocked": {
"type": "boolean"
},
"Salesperson_Code": {
"type": [
"string",
"null"
]
},
"Responsibility_Center": {
"type": [
"string",
"null"
]
},
"Service_Zone_Code": {
"type": [
"string",
"null"
]
},
"Document_Sending_Profile": {
"type": [
"string",
"null"
]
},
"TotalSales2": {
"type": "integer"
},
"CustSalesLCY_CustProfit_AdjmtCostLCY": {
"type": "integer"
},
"AdjCustProfit": {
"type": "integer"
},
"AdjProfitPct": {
"type": "integer"
},
"CFDI_Purpose": {
"type": [
"string",
"null"
]
},
"CFDI_Relation": {
"type": [
"string",
"null"
]
},
"CFDI_Export_Code": {
"type": [
"string",
"null"
]
},
"SAT_Tax_Regime_Classification": {
"type": [
"string",
"null"
]
},
"Last_Date_Modified": {
"type": [
"string",
"null"
]
},
"Disable_Search_by_Name": {
"type": "boolean"
},
"Address": {
"type": [
"string",
"null"
]
},
"Address_2": {
"type": [
"string",
"null"
]
},
"Country_Region_Code": {
"type": [
"string",
"null"
]
},
"City": {
"type": [
"string",
"null"
]
},
"County": {
"type": [
"string",
"null"
]
},
"Post_Code": {
"type": [
"string",
"null"
]
},
"ShowMap": {
"type": [
"string",
"null"
]
},
"Phone_No": {
"type": [
"string",
"null"
]
},
"MobilePhoneNo": {
"type": [
"string",
"null"
]
},
"E_Mail": {
"type": [
"string",
"null"
]
},
"Fax_No": {
"type": [
"string",
"null"
]
},
"Home_Page": {
"type": [
"string",
"null"
]
},
"Language_Code": {
"type": [
"string",
"null"
]
},
"Primary_Contact_No": {
"type": [
"string",
"null"
]
},
"ContactName": {
"type": [
"string",
"null"
]
},
"Bill_to_Customer_No": {
"type": [
"string",
"null"
]
},
"VAT_Registration_No": {
"type": [
"string",
"null"
]
},
"EORI_Number": {
"type": [
"string",
"null"
]
},
"GLN": {
"type": [
"string",
"null"
]
},
"Use_GLN_in_Electronic_Document": {
"type": "boolean"
},
"Copy_Sell_to_Addr_to_Qte_From": {
"type": [
"string",
"null"
]
},
"Tax_Liable": {
"type": "boolean"
},
"Tax_Area_Code": {
"type": [
"string",
"null"
]
},
"Tax_Identification_Type": {
"type": [
"string",
"null"
]
},
"Tax_Exemption_No": {
"type": [
"string",
"null"
]
},
"RFC_No": {
"type": [
"string",
"null"
]
},
"CURP_No": {
"type": [
"string",
"null"
]
},
"State_Inscription": {
"type": [
"string",
"null"
]
},
"Gen_Bus_Posting_Group": {
"type": [
"string",
"null"
]
},
"VAT_Bus_Posting_Group": {
"type": [
"string",
"null"
]
},
"Customer_Posting_Group": {
"type": [
"string",
"null"
]
},
"Currency_Code": {
"type": [
"string",
"null"
]
},
"Price_Calculation_Method": {
"type": [
"string",
"null"
]
},
"Customer_Price_Group": {
"type": [
"string",
"null"
]
},
"Customer_Disc_Group": {
"type": [
"string",
"null"
]
},
"Allow_Line_Disc": {
"type": "boolean"
},
"Invoice_Disc_Code": {
"type": [
"string",
"null"
]
},
"Prices_Including_VAT": {
"type": "boolean"
},
"Prepayment_Percent": {
"type": "integer"
},
"Application_Method": {
"type": [
"string",
"null"
]
},
"Partner_Type": {
"type": [
"string",
"null"
]
},
"Payment_Terms_Code": {
"type": [
"string",
"null"
]
},
"Payment_Method_Code": {
"type": [
"string",
"null"
]
},
"Reminder_Terms_Code": {
"type": [
"string",
"null"
]
},
"Fin_Charge_Terms_Code": {
"type": [
"string",
"null"
]
},
"Cash_Flow_Payment_Terms_Code": {
"type": [
"string",
"null"
]
},
"Print_Statements": {
"type": "boolean"
},
"Last_Statement_No": {
"type": "integer"
},
"Block_Payment_Tolerance": {
"type": "boolean"
},
"Preferred_Bank_Account_Code": {
"type": [
"string",
"null"
]
},
"Bank_Communication": {
"type": [
"string",
"null"
]
},
"Check_Date_Format": {
"type": [
"string",
"null"
]
},
"Check_Date_Separator": {
"type": [
"string",
"null"
]
},
"Ship_to_Code": {
"type": [
"string",
"null"
]
},
"Location_Code": {
"type": [
"string",
"null"
]
},
"Combine_Shipments": {
"type": "boolean"
},
"Reserve": {
"type": [
"string",
"null"
]
},
"Shipping_Advice": {
"type": [
"string",
"null"
]
},
"Shipment_Method_Code": {
"type": [
"string",
"null"
]
},
"Shipping_Agent_Code": {
"type": [
"string",
"null"
]
},
"Shipping_Agent_Service_Code": {
"type": [
"string",
"null"
]
},
"Shipping_Time": {
"type": [
"string",
"null"
]
},
"Base_Calendar_Code": {
"type": [
"string",
"null"
]
},
"Customized_Calendar": {
"type": [
"string",
"null"
]
},
"ExpectedCustMoneyOwed": {
"type": "integer"
},
"TotalMoneyOwed": {
"type": "integer"
},
"CalcCreditLimitLCYExpendedPct": {
"type": "integer"
},
"Balance_Due": {
"type": "integer"
},
"Payments_LCY": {
"type": "integer"
},
"CustomerMgt_AvgDaysToPay_No": {
"type": "integer"
},
"DaysPaidPastDueDate": {
"type": "integer"
},
"AmountOnPostedInvoices": {
"type": "integer"
},
"AmountOnCrMemo": {
"type": "integer"
},
"AmountOnOutstandingInvoices": {
"type": "integer"
},
"AmountOnOutstandingCrMemos": {
"type": "integer"
},
"CustInvDiscAmountLCY": {
"type": "integer"
},
"Global_Dimension_1_Filter": {
"type": [
"string",
"null"
]
},
"Global_Dimension_2_Filter": {
"type": [
"string",
"null"
]
},
"Currency_Filter": {
"type": [
"string",
"null"
]
},
"Date_Filter": {
"type": [
"string",
"null"
]
}
},
"required": []
}
}
}
}
}- Add a new step and initialize 1 variable for each field that you will require data for. In this case, we will just obtain the Name of the client. So initialize 1 variable and call it Name, type is STRING, and assign it any value for now
- Add an Apply to Each step under SELECT AN OUTPUT FROM PREVIOUS STEPS I added this expression: outputs(‘Parse_JSON_DetailCustomer’)?[‘body’]?[‘value’]
- Note that I called my Parse JSON step Parse JSON DetailCustomer so remember to add an underscore for all the spaces. You might have named that step something else, so replace the title of that step with yours. If you are at a loss, you can get the title just by looking at the Parse JSON step
- Now assign the value to the variable by using the set variable step and using this expression: items(‘Apply_to_each’)?[‘Name’] Note that the Apply_to_each is the title for this step
- Once you have assigned the variable, you can use it anywhere!
This entire process was frustrating on many levels. Microsoft documentation is about as good if it were written in an alien language (why is that?). There is great information about oAuth2.0 authentication (but only after spending hours looking) and not a great deal on Parse JSON. I ran into days of frustration when I kept on getting object not array errors, or null errors. In any event, this worked for me, and I hope it works for you.
DIY Accountant 