Hi smodkins,
I have all set :)
I had to work with some assumptions for this tutorial, so if something doesn't match exactly to your use case, let me know and I will be happy in provide you with further support.
Knowledge base
Before we get started, I will share the link for some blog posts that I wrote about the topics discussed here. It can be useful for you or for any other user that finds this thread in the future.
List overview
For this step-by-step, I'm working with the following SP List:
Similarly to yours, it has a Office column (choice data type) and a ConfirmedStartDate column (date only).
Starting the flow: trigger and conditions
Here I'm working with a When an item or a file is modified trigger, which capture only the updates in your SP List (and not the insertion of new items). Based on your previous posts in this thread, the flow must be triggered only when a date is assigned to a List Item, and its previous value is blank, so we don't need to capture the creation of new items:
As a trigger condition, I'm using @and(or(equals(triggerOutputs()?['body/Office']?['Value'], 'London'), equals(triggerOutputs()?['body/Office']?['Value'], 'Leeds'), equals(triggerOutputs()?['body/Office']?['Value'], 'Manchester')), contains(triggerOutputs()?['body'], 'ConfirmedStartDate')), which is the same recommended in my first message, but without the not. This condition will capture only changes in items where Office is set as Leeds, London or Manchester, and the ConfirmedStartDate is not blank (after the modification).
If you are copying the condition from this step-by-step, make sure to change the "Office" column name.
Get all item versions
For this solution, we will need to access all previous versions from the List Item, so we can compare the new one with the existing before the change. For that, we will use a HTTP request like below:
About the request:
- Make sure to set the Site Address as the same where your List is located.
- Set the method to GET.
- Use the following URI: _api/web/lists/getbytitle('Employee onboarding')/items([ID_dynamic_content])/versions/, replacing the text in red for your List name and the text in blue for the trigger's ID dynamic content.
- Add the header accept with the value as application/json.
This HTTP request will return a history of all values that your List Item ever had in its previous versions. For example, when running the flow for a modification in the "Spiderr man" record from the List, the HTTP outputs will include all values from its previous versions, including the 2.0, when the ConfirmedStartDate was blank (null):
Getting the ConfirmedStartDate from previous version
A good thing from this HTTP request is that it retrieves all versions as an array of objects in descending order by modification date, so you will have the current version (the just changed one) as first element, the previous version (the one from where we want to capture the ConfirmedStartDate to test if it's empty) as second element, and so on.
Having said that, we are sure that the previous version will always be allocated at the second position in the array, so we don't need to waste time comparing the different versions to identify which one is the correct.
So let's extract the form the second array element and store it into a variable. You can use the expression
outputs('Send_an_HTTP_request_to_SharePoint_-_All_versions')?['body']['value'][1]['ConfirmedStartDate'] for that:
When copying/pasting the expression, make sure to replace outputs('Send_an_HTTP_request_to_SharePoint_-_All_versions')?['body'] for the dynamic content of the body property of your HTTP request action.
Test if ConfirmedStartDate in previous version is blank
Finally, let's test if the ConfirmedStartDate before the Item modification is blank. For that, you will need to add a condition, and set the left input as an empty expression, such as empty(variables('previous_date_value')) (make sure to use your actual variable dynamic content within the empty parentheses), keep the dropdown as is equal to and set the right input as true:
The rest of your flow must now be positioned within the If yes block, since it will be executed only if the previous date was blank.
Testing the flow
I just added a new record to the List, allocated to one of the Offices that must be captured by the flow. This event, however, won't trigger the flow, since we are monitoring only modifications and not item creations:
Now let's make a small change in the name (but not in the date), which will update the Item version to 2.0, but still won't trigger the flow, since the ConfirmedStartDate is still empty:
Then we will make another change (going to version 3.0), and at this time setting the date:
At this time, the flow finally ran, since we match both trigger conditions (Office is Leeds/London/Manchester and ConfirmedStartDate is not empty). As expected, the Condition returned as true, since the ConfirmedStartDate for version 2.0 was empty:
If we now make any other change to the same Item, the flow will still run, but the condition will return as false, as the previous version doesn't have the ConfirmedStartDate as empty:
Let me know if it works for you or if you need any additional help!
-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.