Dynamics 365 Business Central: How to do “if Record.Find then” in Power Automate (Check if the record already exists or not)

Hi, Readers.
We discussed Automatically create a new customer when receiving an email via Power Automate (No customization) the other day. I got a question yesterday, “What if i want to add a step to check if the customer already exists or not?” Yes, it can be done in Power Automate, so today I would like to briefly talk about how to do “if Record.Find then” in Power Automate (Check if the record already exists or not).

First, let’s look at a very simple requirement. When a customer whose name is ‘Microsoft’ exists, add ‘zy’ after the name. If it does not exist, create a new customer named ‘Microsoft’.

In AL, we can do it with the following. It’s not difficult.

What about in Power Automate? We can use Find records (V3) action and Condition control.

Find records (V3): Retrieves Dynamics 365 Business Central records of a certain type (for example, customers or items)

Returns: The outputs of this operation are dynamic.

Condition: Identifies which block of actions to execute based on the evaluation of condition input. Use a condition to specify that a cloud flow performs one or more tasks only if a condition is true or false. For example, you can use a condition that indicates that you’ll get an email only if a tweet that contains a keyword is retweeted at least 10 times. More details: Add a condition to a cloud flow

Let’s look at a specific example.

Sign in to Power Automate, and create a new Automated cloud flow.

Choose Skip. (You can also set the flow name and flow’s trigger here)

Give the flow a name.
For example, Check if the Customer already exists or not

This time we start the flow manually, so click “Manually trigger a flow“

Click New step.

Search for Business Central, and click on it.

Then select the Find records (V3) action.

Select the environment information that needs to be connected. We’re using the standard customers api this time, so select v2.0 in API category and customers in Table name. Then click Show advanced options.

Then enter filter query.
For example,

displayName eq 'Microsoft'

“$filter” is already included in parameters, so there is no need to add it manually. More details: filter in API

PS:
1. The field name of the query must be the same as defined in the API, otherwise the following error will be prompted.

{"statusCode":400,"headers":{"Cache-Control":"no-cache","Pragma":"no-cache","Set-Cookie":"ARRAffinity=41d3c965c9e2315c0c1a022a787866a0158b3dd7de79015a8b3a0517db60e823;Path=/;HttpOnly;Secure;Domain=dynamicssmbsaas-cus.azconn-cus-002.p.azurewebsites.net,ARRAffinitySameSite=41d3c965c9e2315c0c1a022a787866a0158b3dd7de79015a8b3a0517db60e823;Path=/;HttpOnly;SameSite=None;Secure;Domain=dynamicssmbsaas-cus.azconn-cus-002.p.azurewebsites.net","x-ms-request-id":"b2720829-7cfa-4515-8170-34eddbd5601b","X-AspNet-Version":"4.0.30319","X-Powered-By":"ASP.NET","Strict-Transport-Security":"max-age=31536000","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"true","x-ms-apihub-obo":"false","Date":"Wed, 09 Aug 2023 23:24:07 GMT","Content-Length":"521","Content-Type":"application/json","Expires":"-1"},"body":{"status":400,"message":"ErrorCode: BadRequest  Could not find a property named 'customerNumber' on type 'Microsoft.NAV.customer'.  CorrelationId:  04098d57-3fb2-4707-83d5-1d40e508b865.\r\nclientRequestId: b2720829-7cfa-4515-8170-34eddbd5601b","error":{"message":"ErrorCode: BadRequest  Could not find a property named 'customerNumber' on type 'Microsoft.NAV.customer'.  CorrelationId:  04098d57-3fb2-4707-83d5-1d40e508b865."},"source":"api.businesscentral.dynamics.com","errors":[]}}

2. The OUTPUTS of this step:
Value can be found.

No value can be found.

Click New step.

Then select the Condition action.

In Choose a value -> Expression, enter the following formula. To determine whether the value in the OUTPUTS of step Find records (V3) has a value.  More details: Reference guide to workflow expression functions in Azure Logic Apps and Power Automate

empty(outputs('Find_records_(V3)')?['body/value'])

PS: Please note that in this step, the fields of the Find records (V3) step cannot be used, because there may be more than one query result, so the Power Automate will automatically add an Apply to each loop.

If the Find records (V3) step value is empty, this Condition control will never be executed.

Finally, add actions to create the record if it is empty (Create record (V3)) and update the record if it is not empty (Update record (V3)).

That’s all.

Let’s test it out.
If the customer does not exist, add the customer.

If the customer exists, update the customer name. (Because of the Apply to each loop, all records found will be updated)

Test video:

Very simple, give it a try!!!😁

END

Hope this will help.

Thanks for reading.

ZHU