Fixing the Sales Pipeline funnel chart’s Pipeline Phases with Power Automate

Every now and then customers comment that their Sales Pipeline funnel chart displays business process stages that they think shouldn’t be on their funnel. The first thing I investigate is what their current Lead To Opportunity and Opportunity Sales Process are and whether any changes have been made to them. If the processes are altered after D365 has been implemented, the funnel chart can easily display process stages from both the OOTB processes and from their edited versions.

The Pipeline Phase field

A reason the funnel chart displays extra information can be the Pipeline Phase field. If you make that field visible on the Opportunity main form, check what value the field has and compare the value to the stage of you business process. When a Business Process Flow’s stages are edited, the value in the Pipeline Phase field doesn’t automatically update. When you move forward or backward in your process, the value in the field changes.

The image below shows a BPF in D365 PSA. The process is in the Develop stage but the Pipeline Phase field has a value of 1-Qualify. This will cause the funnel chart to display incorrectly. While the example in question is related to the BPF spanning across entities, it conveys the idea around how a BPF and the Pipeline Phase field work together.

Fixing the Pipeline Phase field with Power Automate

Let’s dig into a Flow that fixes the problem. I’ve created an on-demand Flow so that it can be fired off manually, whenever a change to a BPF has been made. If you have a BPF that spans across entities, you will notice that the Pipeline Phase field won’t get updated when a BPF’s stage jumps to another entity. A Flow or a real-time workflow can update the Pipeline Phase field when a BPF’s stage changes. Below is an example of a real-time workflow that sets a correct value to the Pipeline Phase field when a BPF’s Active Stage changes.

The initial compose actions in the Flow are there to point you to some helpful information. To list your Opportunity Sales Process’ stages, use the request found below on your web browser of choice. A compose action can be used to store the results so that you can easily access information about your process’ stages.

https://EnvironmentAddressHere/api/data/v9.0/processstages?$select=stagename&$filter=processid/workflowid eq BpfGuidHere

I have also included a compose for a FetchXML query that helps us get our hands on all open opportunities. After all, those are the ones that we want to edit to align the funnel chart. By far the easiest way to create a FetchXML query is to use MVP Jonas Rapp’s FetchXML Builder. If you’re not using it, you’re working too hard. Kudos to Jonas for making our D365 life less complicated. Below is a query that returns open opportunities, when using a List records action for the Opportunity Sales Process entity. More on this in a few paragraphs.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
  <entity name="opportunitysalesprocess" >
    <attribute name="businessprocessflowinstanceid" />
    <attribute name="name" />
    <attribute name="createdon" />
    <attribute name="opportunityid" />
    <attribute name="activestageid" />
    <attribute name="statecode" />
    <attribute name="statuscode" />
    <attribute name="processid" />
    <order attribute="opportunityid" descending="false" />
    <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="inner" alias="aa" >
      <filter type="and" >
        <condition attribute="statecode" operator="eq" value="0" />
      </filter>
    </link-entity>
  </entity>
</fetch>

Additional useful information is the official docs.microsoft.com page for working with BPFs using code. It can be found here. Also check out MVP Elaiza Benitez’s blog post on automatically updating the stage of a BPF. That post helped me create the Flow I’m describing on this post so kudos to Elaiza.

The final compose actions in the image below are for storing the GUID of a BPF’s stage. They help me remember the stages I have on my BPF – lose the composes if they bother you. They’re not mission critical for the Flow to work.

After the compose actions, it’s time to use a list records action for the Opportunity Sales Process entity. The FetchXML query is now used so that we can get our hands on all open opportunities. A switch is then used and based on the BPF’s active stage, the related Opportunity’s Pipeline Phase field is updated.

I hope this simple Flow helps you align you funnel chart. Remember that if your BPF spans across entities, the Pipeline Phase field doesn’t automatically update. As always, this Flow and the real-time workflow described earlier can be found in the TDG Power Platform Bank here.

Disclaimer:
All my blog posts reflect my personal opinions and findings unless otherwise stated.