Update Dynamics 365 Records with FetchXML and Power Automate

Microsoft Dynamics 365 and Dataverse offer robust capabilities for retrieving and updating records. One of the most powerful and flexible methods is to combine the advanced querying power of FetchXML with the automation prowess of Power Automate. This approach allows you to process and update records automatically without the need for custom code.

In this article, we will walk through the process of retrieving specific records using FetchXML and then updating them within Dynamics 365 using Power Automate.

Why Use FetchXML in Power Automate?

FetchXML is a proprietary query language for Microsoft Dataverse that offers significant advantages when integrated with Power Automate:

• Advanced Filtering Capabilities: Build complex queries to pinpoint exact records.
• Supports Linked Entities: Query data across related tables.
• Better Performance: Optimized for retrieving specific data sets, especially for complex queries.
• Easy Integration: Seamlessly works with Dataverse actions in Power Automate.
• No Custom Development: Leverages built-in tools, reducing development time and cost.

Common scenarios where this combination shines include:

• Bulk record updates based on specific criteria.
• Automated status changes for records.
• Data synchronization tasks.
• Scheduled record maintenance.
• Automating complex business processes.

Prerequisites

Before you begin, ensure you have the following:

• A Microsoft Dynamics 365 environment.
• Access to Power Automate.
• Access to the relevant Dataverse tables.
• Appropriate security permissions to read and update records in Dynamics 365.

Step-by-Step Guide

Step 1: Create a Cloud Flow

1. Navigate to the Power Automate portal.
2. Click on Create.
3. Select either a Scheduled Cloud Flow (for automated, time-based triggers) or an Instant Cloud Flow (for manual triggering).
4. Provide a descriptive Flow Name.
5. Click Create to initialize your flow.

Step 2: Add the 'List Rows' Action

The first step within your flow is to retrieve the records you intend to update. We'll use the Dataverse connector for this.

1. Add a new step and search for the Dataverse connector.
2. Select the List rows action.
3. In the Table name field, select the Dynamics 365 table you want to query (e.g., 'Accounts').
4. Leave Return all rows unchecked for now, as we will use FetchXML for precise filtering.

Step 3: Create and Input Your FetchXML Query

This is where you define the specific records you want to target. You can construct your FetchXML query using tools like the FetchXML Builder in XrmToolBox or by writing it manually.

Here's an example of a FetchXML query to retrieve all active accounts:

<fetch>
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

Paste this FetchXML query into the Fetch XML query field within the List rows action. This query specifically selects the accountid and name attributes for all records where the statecode is 0 (Active).

Step 4: Process Retrieved Records with 'Apply to each'

Once the List rows action executes, Power Automate automatically wraps the subsequent actions in an Apply to each control. This loop iterates through every record returned by your FetchXML query.

You can access the dynamic content from the List rows action within this loop. For example, you can use the accountid of each record to perform updates.

Step 5: Add the 'Update a row' Action

Inside the Apply to each loop, add another Dataverse action to modify the records.

1. Add the Update a row action from the Dataverse connector.
2. Select the same Table name as used in the List rows action.
3. For the Row ID field, select the unique identifier of the current record from the Apply to each loop. This is typically the accountid output from the List rows action.
4. Update the fields you wish to modify. For example, you might set a custom field like 'Status' to 'Processed' or add a description like 'Updated through Power Automate'.

Step 6: Save and Test Your Flow

1. Save your Power Automate flow.
2. Click Test and choose to run it manually.
3. Initiate the test. Once the flow has run, navigate back to your Dynamics 365 environment.
4. Verify that the records matching your FetchXML query have been updated according to the logic defined in the Update a row action.

Example Scenario: Updating Old Accounts

Imagine your organization needs to automatically identify and flag active accounts that were created more than 30 days ago for a potential review.

Your FetchXML query would look like this:

<fetch>
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <filter>
      <condition attribute="createdon" operator="olderthan-x-days" value="30" />
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

In your Power Automate flow:

1. Use the List rows action with the above FetchXML.
2. Inside the Apply to each loop, use the Update a row action.
3. Update a custom field (e.g., 'Review Status') to a value like 'Needs Review'.

This setup automatically retrieves all active accounts older than 30 days and updates a specific field, flagging them for review without manual intervention.

Best Practices

• Retrieve Only Necessary Columns: Specify only the attributes you need in your FetchXML to improve performance.
• Use Filters Effectively: Make your filters as specific as possible to minimize the number of records processed.
• Enable Pagination: For very large datasets, consider enabling pagination in the List rows action to handle results in batches.
• Test in Sandbox: Always test your flows in a sandbox or development environment before deploying to production.
• Monitor Flow Runs: Regularly check the flow run history for any errors or performance issues.
• Avoid Unnecessary Updates: Ensure your logic only updates records when a change is actually needed.

Benefits of This Approach

• Reduces Manual Effort: Automates repetitive data management tasks.
• Improves Data Accuracy: Ensures consistent updates across your system.
• Supports Bulk Operations: Efficiently handles updates for large volumes of records.
• Increases Operational Efficiency: Frees up users from mundane tasks to focus on strategic activities.
• Simplifies CRM Administration: Streamlines routine maintenance and data hygiene.

Common Use Cases

• Lead Management: Automatically update lead statuses based on engagement or time.
• Account Maintenance: Update account fields (e.g., industry, rating) based on external data or business rules.
• Contact Synchronization: Keep customer information current by updating contact details based on defined criteria.
• Opportunity Tracking: Automatically update opportunity stages or close dates based on related activity.
• Scheduled Data Cleanup: Regularly identify and update or archive outdated records to maintain data quality.

Conclusion

Leveraging FetchXML within Power Automate provides an exceptionally powerful and efficient method for retrieving and updating records in Dynamics 365. By combining FetchXML's advanced querying capabilities with Power Automate's robust automation features, organizations can effectively manage large datasets, automate complex business processes, and improve overall data quality and operational efficiency – all without writing custom code.