I have a SharePoint list that has a choice column called "Status". The column has 7 statuses to choose from. I'd like to be able to time stamp when a status has changed from one status to another. I don't have a problem creating a conditions where it times stamps the end of the previous status and time stamps the beginning of another if the statues are in sequential order. For instance, "Request Initiated" to "Under Review" to "Feedback from Customer" to "In Progress" then to "Closed". Each status has a hidden column for logging the start date and end date. For example, "UnderReviewOpenDate" and "UnderReviewClosedDate".
My problem is what to do if it isn't in sequential order? Do I have a create a condition for all the many possibilities that a case status may jump to? For instance, if I'm "Under Review" and jump to "In Progress", I have to time stamp the closed date for "Under Review" and then time stamp the start of "In Progress". I'm hoping there is a way to avoid creating many condition actions to handle all the different scenarios for when the user jumps from one status to another. Hopefully I'm not being too confusing with what I'm trying to do. Thanks in advance
I am not sure about the full intention of different datetime stamp for each status changes. Why not create a multi-line text and append the text with status - datetime. Next time status gets changed then append the multi-line text with new status - datetime. This way historically to can see all in one text under a single column. Not sure this works or not for your requirement.
@abm thanks for the response. The intention of the time stamps is to track the cycle times of each status. I want to know how long each item is in a particular status.
I'm not sure that the method you've provided will solve my problem. If all the date are in a multi line field I'm not sure that I can export all the times and all the dates into an excel spreadsheet and create charts etc.
@abm ideally it should go in a particular order. The item will always start in "Request Initiated" but after that I can jump straight to "Closed", it can jump straight to "In progress". So I need to be able to change the status and the power automate flow knows the previous status and place a date in that previous statuses closed date field.
Thinking about it and the earlier suggested multi-line text column may be a workaround. Please let me know what you think.
See the below steps.
1. When the status gets changed store the status name and datetime
eg: Request Initiated - 01/04/2022 13:40
2. All next status changes store the next status in similar format. Here append the new text in next line (use a carriage return when you append the text)
eg: Under Review - 02/04/2022 09:30
Finally you will be having something like this
Request Initiated - 01/04/2022 13:40
Under Review - 02/04/2022 09:30
Next create another flow to read the multi-line text values
Use split() expression to extract each lines using the enter key as delimiter. This will give you each line with status and datetime. To extract the datetime use another split using the '-' as delimiter. This way you can get all status and datetimes separately.
Hope it make sense.
>I'm hoping there is a way to avoid creating many condition actions to handle all the different scenarios for when the user jumps from one status to another.
Please forgive me if I misunderstood you, but what's the problem to create a single switch action with 7 options?
Hey @VictorIvanidze ,
I thought about using a switch for the statuses but I'm not sure that will solve the problem. I'm still fairly new to Power Automate so I'll try to explain.
So below is the first switch "Under Review". I know that most likely after it leaves the "Request Initiated" status it's going to go for a review. So when the status goes for review, a "utcNow()" will be added to "Request Initiated Close Date" and a utcNow() for "UnderReviewOpenDate".
But how would I handle it, if it jumps from "UnderReview" to "Escalated"? I would need to to put a utcNow() in the "EscalatedOpenDate" field and the MS Flow would have to know to put a utcNow() in the closed date field for whatever the previous status was.
The screenshot below is what I'm trying to say. Assuming that the user jumps from "UnderReview" to "Escalated".
Hopefully this makes it more clear. I'm just trying to figure out how can I let Power Automate know that the last status before the change and a time stamp, utcNow(), is placed in that closed date column. And a utcNow() placed in the open date column of the new status.
It's probably an easy solution and I'm just thinking too much on what needs to be done. I appreciate all the responses so far.
Did you had a think about the suggested steps I described. That avoids all the switch or if conditions. For utcNow() you need to have an IF condition or switch condition to check what's the current status and add Update Item action step for updating todays datetime.
Hey @abm ,
Sorry for the late response. I understand what you're trying to say and I believe something like this may work. I will try this now and contact you with any questions. Thank you!
I'm running into an issue trying to get the append to string to work. Can you check my flow blow?
Here is the error I'm receiving:
I'm not sure if I'm creating this correctly, but my flow looks like this:
Can you please take a look and let me know where I'm making my mistake? Thank you.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Announcing a new way to share your feedback with the Power Automate Team.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.
The Super User program for 2022- Season 2 has kicked off!