cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisS81
Helper II
Helper II

Conditional time stamping

Good morning,

 

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 

10 REPLIES 10
abm
Super User
Super User

Hi @ChrisS81 

 

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. 

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

@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.

Hi @ChrisS81 

 

Thanks for your reply.

 

Yes understood now.  Is all the status changes goes from one to another in a particular order?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
ChrisS81
Helper II
Helper II

@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.

abm
Super User
Super User

Hi @ChrisS81 

 

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.

 

Thanks

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
VictorIvanidze
Community Champion
Community Champion

 >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?

--------------------------------------------------------------------------------------
Contact me if you are interested in custom Power Automate development.

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".

ChrisS81_0-1648999558814.png

 

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".

ChrisS81_1-1649000159515.png

 

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.

Hi @ChrisS81 

 

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.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
ChrisS81
Helper II
Helper II

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!

ChrisS81
Helper II
Helper II

Hey @abm,

 

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:

ChrisS81_0-1649072509101.png

 

I'm not sure if I'm creating this correctly, but my flow looks like this:

ChrisS81_1-1649072594716.pngChrisS81_2-1649072726655.png

 

Can you please take a look and let me know where I'm making my mistake? Thank you.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (1,703)