Dynamics Community Forum Thread Details

Hello JA-11021511-0,

You've encountered a common challenge: retroactively updating existing, closed Opportunity records with a new field value derived from their existing data (in your case, the Opportunity ID). Processes (workflows and Power Automate flows) are excellent for ongoing updates, but they don't automatically apply to historical data.

Here are the best approaches to retroactively update your closed Opportunities:

1. Bulk Update using Advanced Find and Export/Import (Recommended for Simplicity):

Concept:
- This is the simplest method for one-time updates.
- You'll use Advanced Find to select the closed Opportunities, export them to Excel, modify the field values, and then re-import them.

Steps:
1. Advanced Find:
  - Go to Advanced Find.
  - Select "Opportunities" as the entity.
  - Add filters:
    - "Status" equals "Closed" (or any other relevant closed status).
  - Add any other filters needed to narrow down the records.
  - Add the "Opportunity ID" field and the field you want to update to the view.
  - Click "Results".
2. Export to Excel:
  - Click "Export Opportunities".
  - Choose "Static worksheet" or "Static worksheet (page only)".
  - Download the Excel file.
3. Modify in Excel:
  - Open the downloaded Excel file.
  - In the column for the field you want to update, use Excel formulas or manual entry to populate the field with the Opportunity ID.
    - For example, if the opportunity ID is in column A, you can use the formula =A2 in the next column, and drag it down.
  - Save the Excel file.
4. Import to Dynamics 365:
  - Go to Settings > Data Management > Imports.
  - Click "Import Data".
  - Select your modified Excel file.
  - Follow the import wizard, mapping the columns correctly.
  - Ensure that the "Update existing records" option is selected.
  - Submit the import job.

Advantages:
- Simple and straightforward.
- No code required.
- Works well for one-time updates.

Disadvantages:
- Manual process.
- Can be time-consuming for very large datasets.

2. Bulk Update using Power Automate (Recommended for Automation/Recurring Updates):

Concept:
- Create a Power Automate flow to retrieve and update the closed Opportunities.
- This is more useful if you have to repeat this operation often.

Steps:
1. Trigger:
  - Use a "Schedule" trigger or a "Manual" trigger.
2. List Rows (Dataverse):
  - Use the "List rows" action from the Dataverse connector.
  - Select the "Opportunities" table.
  - Add a filter query to retrieve only closed Opportunities (e.g., statuscode eq 3).
  - Add the "Opportunity ID" and the field you want to update to the selected columns.
3. Apply to Each:
  - Add an "Apply to each" loop to iterate through the retrieved Opportunities.
4. Update a Row (Dataverse):
  - Inside the loop, use the "Update a row" action from the Dataverse connector.
  - Select the "Opportunities" table.
  - Use the "Opportunity ID" dynamic content from the "List rows" action to identify the record to update.
  - Set the field you want to update to the "Opportunity ID" dynamic content.
5. Save and test the flow.

Advantages:
- Automated process.
- Can be scheduled for recurring updates.
- Good for larger datasets.

Disadvantages:
- Requires Power Automate knowledge.

3. Custom Code (Plugin or Console Application) (Advanced):

Concept:
- Write a custom plugin or console application to retrieve and update the closed Opportunities.
- This provides the most control and flexibility but requires development effort.

Steps:
1. Develop the Code:
  - Use the Dynamics 365 SDK to connect to your environment.
  - Write code to:
    - Retrieve the closed Opportunities using a query.
    - Update the field with the Opportunity ID.
  - If using a plugin, register the plugin to run on the update of the opportunity, and then create a console app to update the records.
2. Deploy and Run:
  - Deploy the plugin to your Dynamics 365 environment or run the console application.

Advantages:
- Maximum control and flexibility.
- Can handle complex logic.

Disadvantages:
- Requires development expertise.
- More complex to set up and maintain.

Recommendation:

For a simple, one-time update, the Advanced Find and Export/Import method is the easiest and most efficient.
If you need to perform this update regularly or have a very large dataset, Power Automate is a good choice.
Custom code should only be used for very complex scenarios where the other methods are not suitable.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,

Daivat Vartak