.png)
There are often ad hoc needs that go beyond what is possible with a human clicking on their computer, or should be possible for productivity’s sake. For example, a need we ran into was capturing a datetime in a SharePoint list for when an item was approved. Done manually, this meant looking through the history version. That would be fine if it were for a few items.
The Concept
We needed to detect when a SharePoint item was approved. At first you may think that could be found with the earliest version where the Status column was equal to ‘Approved’. However, what happens when something is then set back to ‘In Review’ before being set to ‘Denied’ or ‘Approved’ again. That means we need to find the most recent time the Status column was changed to ‘Approved’.
Building the Utility Flow
At its most basic level, this utility flow only consists of three actions, which I’ll outline below.
Before that, I’d like to show how we made the flow a true utility with the 5 required parameters:
SharePoint Site URL: the base URL for the specific SharePoint site that holds the List
SharePoint List Name: the name of the List to be queried
Field Schema Name: the schema or logical name of the column, which may be different than the display name
Target Value: the value of the field you are interested in determining when it became that value
ID: the ID of the SharePoint item
These are all critical to composing the SharePoint http requests that respond with version history information. So now let’s get to the more exciting part of building the actions. As described above, we need to find the most recent version where the specified column does not equal the target value.
Following that step, you then find the most recent version where the specified column is equal to the target value but is also a later version than the version returned in the first http call.
Code snippet for the VersionId gt comparison: first(outputs('Get_first_non_target_version')?['body/value'])?['VersionId']
With that, you have all the information you need! The final step is responding back with the datetime of the change. This makes it possible to call this flow as a child flow and run it for numerous items!
Suggestion: make a Utilities Solution that holds these types of Flows and share them with your team.
Response snippet: first(outputs('Get_last_target_version')?['body/value'])?['Modified']
This isn’t a complicated build, but it is incredibly useful when backfilling data. It can also be tweaked to restore information while not being able to restore an actual version.
If you’d like to use the Flow yourself, you’ll find the file below. You’ll also find a more thoroughly built out flow that takes into consideration many of the potential failure points. For example, what happens when the column isn’t found or when the item has never been set to the target value?