Using Dataverse to store workflow status
In this 3rd episode in the series, I look at using Dataverse, rather than a SharePoint list, to store the workflow information regarding stage and status.
First, I created a new table and decided to use a primary key made up of the stage and GUID of the project. Seemed like a good idea at the time, but with Dataverse being driven by Row IDs I’m not sure if it makes sense. I do also capture quite a few additional pieces of information compared to the earlier example in the SharePoint List – such as the Flow ID, Instance Id and the Flow Name, which I don’t use in this example, but for scenarios where we might need to resubmit a flow, for a long running workflow that had been disabled, might come in handy.
These are the fields – excluding the usual ones that Dataverse adds for me:
The Flow follows the same structure as in the first article, initializing variables for fields I’ll use later, including the Row Id for the 3 stage rows I’ll be adding and updating. I check that the EPT is the one this workflow will process and jump into the grabbing of workflow information and creating my rows for the stages.
Getting the workflow information gets the workflow() input, then can be parsed using the output as a sample for the schema, then the name, flowDisplayName and id can be put into my variables.
When I add a new row for Propose, which is my first stage, I can populate some of the flow information from the above variables into my Workflow_Statuses table, as well as putting in my primary key and project name. I also set the Stage to Propose and Status to Not Started.
I do a similar setup for the Review and Finished stages.
My Switch and Do Until are very similar to those from Part 1, with some different options there to get my Row ID and get it into a suitable form to use as the Row ID in my Update Dataverse action.
I’m sure I could have done this much more efficiently than the way I ended up – but wanted to get this posted rather than spend time re-working it. The steps use the OData Id from the initial row creation action, which I then trim the GUID out with a compose:
First( Split( Last( Split(variables('ProposeRowId'),'(')),')'))
And the output from that trim is put back into my variable. Once I have the GUID I can use that as the Row ID to make a row update, and for all the fields I leave blank they retain the values set earlier:
That is pretty much it. Rinse and repeat, for the various approvals and stage changes to update the rows as things progress. In terms of displaying the status, it really depends on where you want to keep your users. You could expose in a web part in SharePoint (I found a few examples online) or you could perhaps use Adaptive Cards similar to those used in Part 2 - or a Power App?
One conversation we had internally about a possible “Part X” in this series related to “migration” of workflows on projects running in SharePoint 2013 workflows to a new workflow built with Power Automate. Although I can see this would have some value, particularly when very many projects are involved, I felt it may also inhibit you from getting the best possible experience in Power Automate as just matching what you have today, may not be what you really want for the future. I’d love to hear your thoughts. Would you plan to continue current workflows until the projects are finished (assuming they end before April 2nd 2026) and just start new plans with the new workflows? Or would you need to migrate in-flight projects? Anyone looking at having their workflows driven by a 'proxy' project in Project for the web, with the execution happening in Project Online?
