Capturing a reassigned approval flow from the Common Data Service (CDS): Part 1

There have been instances where I like to keep logs of approval workflows in a SharePoint list so the business team can see who has approved each approval flow. As we know, only flows that are assigned to you, are visible to you in the Approvals area in Power Automate.

When you create an approval in Power Automate, you can track that approval by an Approval ID in the Common Data Service (CDS). Tied to that Approval ID is a Request ID/s that is created for the user, or users, who are assigned the approval flow.

For example, when you create an approval, a record gets created in the Flow Approval entity (msdyn_flow_flowapproval). The Approval ID is the msdyn_flow_flowapprovalid field. A record, or records (depending if multiple approvers have been assigned) also gets created in the Approval Request (msdyn_flow_approvalrequests) entity. The unique identifier for the Request ID is the msdyn_flow_approvalrequestid field. To match up the requests to the approval, the msdyn_flow_approvalrequestidx_approvalid field is used from the Approval Request entity.

You can track who the original approvers are as it is a required field in the Create Approval action in flow. However, when the approval gets reassigned, the business have no way of knowing that it has happened, unless we create a flow that searches the CDS for new approval requests.

To create this flow, first we need to scan for when a new request gets created in the Approval Requests entity.

Right now, we are triggering for all the requests from every flow that’s in the current environment. In order to get the flow the request came from, we need to get the records from the Flow Approvals entity. The item identifier will be the msdyn_flow_approvalrequestidx_approvalid (which I mentioned above was the field that tied the two entities together). The friendly name is Approval Id Index.

Now that we have tied our entities together, we now need to filter our results to return the flow where our Create Approval action is. For most flows, the Reassigned From field (msdyn_flow_approvalrequestidx_reassignedfromid) in the Approval Request entity will have a <none> value. However, reassigned flows will have a GUID that contains the value of the Request ID of the previous approval request. Remember, there can be multiple unique Request IDs for an approval. So a user that was assigned to that Request ID is the one that reassigned the flow to another user. Therefore, the GUID is the Reassigned From field points to the Request ID field (msdyn_flow_approvalrequestid).

Additionally, we want to filter by the Flow Name (msdyn_flow_flowapproval_flowname) from the Flow Approval entity. To find the name of the flow that contains your Create Approval action, go to your flow and look at the URL. You will see the GUID for your flow after the word flows/.

/flows/a17c5cfe-0cc1-4375-b4c5-9338a95a3d7c

In my example, I have multiple flows with approvals that I am looking for reassignments on.

Once you have established that your approval has been reassigned, you can update your log, or notify the required users. This does not capture who the request has been reassigned to, but I will detail that in another blog post.