Forum Discussion
Rob Nunley
Feb 25, 2021Brass Contributor
Create timestamp in Sharepoint
I have a SharePoint Online List. One of the columns in the list is a Yes/No titled "Completed" and another text column titled "Time Completed". When an item is changed from No to Yes I would like a flow to run and place a timestamp in "Time Completed" column. I've tried finding the correct steps in Power Automate but cannot get it to work. Is this a possibility with PA?
Thanks!
Rob Nunley Yes this is possible. First make sure that versioning is turned on in List Settings as the flow compares the current version with the previous one to see if the Completed column has changed.
1. The trigger is a SharePoint When an item or a file is modified:
Once you have selected your site and list you need to add trigger conditions so that you don't end up in an infinite loop. So click the 3 dots at the top right of the action and select Settings. At the bottom of the panel that appears you add the following 2 trigger conditions:
@equals(triggerBody()?['Completed'],true)
@empty(triggerBody()?['TimeCompleted'])What this does is to trigger the flow only if the Completed column has a Yes in it and if the TimeCompleted column is empty:
Next, we want to get the changes that have happened, so add a Get Changes for an item or a file (properties only) action. In the Since field select Trigger Window Start Token and in the Until field select Trigger Window End Token:
Next, I want the flow to add the data into the TimeCompleted column in a specific format, so I've added a Compose control and in the inputs added the following expression:
formatDateTime(utcNow(),'dd/MM/yyyy HH:mm')(the capital Hs are to use the 24-hour time).
Next we want to check if specifically the Completed column has changed. Add a Condition and select Has Column Changed: Completed from the dynamic content, in the middle field select is equal to and in the right field type true
As this is a Condition you'll have the normal green If yes channel and the Red If no channel. You don't want anything to happen in the red channel so leave it empty. But in the green channel add a SharePoint Update item action. Select the site and list from the dropdowns and the ID and Title from the dynamic content box. In the TimeCompleted field select the outputs from the Compose control.
When a user changes the Completed column in the SharePoint list to yes the flow will run and add today's date and time into the TimeCompleted column. It might take a minute or 2 to do it but you can see it happening in the attached video.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
- RobElliottSilver Contributor
Rob Nunley Yes this is possible. First make sure that versioning is turned on in List Settings as the flow compares the current version with the previous one to see if the Completed column has changed.
1. The trigger is a SharePoint When an item or a file is modified:
Once you have selected your site and list you need to add trigger conditions so that you don't end up in an infinite loop. So click the 3 dots at the top right of the action and select Settings. At the bottom of the panel that appears you add the following 2 trigger conditions:
@equals(triggerBody()?['Completed'],true)
@empty(triggerBody()?['TimeCompleted'])What this does is to trigger the flow only if the Completed column has a Yes in it and if the TimeCompleted column is empty:
Next, we want to get the changes that have happened, so add a Get Changes for an item or a file (properties only) action. In the Since field select Trigger Window Start Token and in the Until field select Trigger Window End Token:
Next, I want the flow to add the data into the TimeCompleted column in a specific format, so I've added a Compose control and in the inputs added the following expression:
formatDateTime(utcNow(),'dd/MM/yyyy HH:mm')(the capital Hs are to use the 24-hour time).
Next we want to check if specifically the Completed column has changed. Add a Condition and select Has Column Changed: Completed from the dynamic content, in the middle field select is equal to and in the right field type true
As this is a Condition you'll have the normal green If yes channel and the Red If no channel. You don't want anything to happen in the red channel so leave it empty. But in the green channel add a SharePoint Update item action. Select the site and list from the dropdowns and the ID and Title from the dynamic content box. In the TimeCompleted field select the outputs from the Compose control.
When a user changes the Completed column in the SharePoint list to yes the flow will run and add today's date and time into the TimeCompleted column. It might take a minute or 2 to do it but you can see it happening in the attached video.
Rob
Los Gallardos
Microsoft Power Automate Community Super User- AB21805Bronze ContributorHi ! Thanks for this, if I had a column called status and wanted the trigger to happen when I change the status to "Completed"
What would the following formula look like?
@equals(triggerBody()?['Completed'],true)
Would it be:
@equals(triggerBody()?['Status'],Completed) - vheerCopper ContributorHi! Im following the instructions, but for some reason the right column doesnt show as a "Has Column Changed" option. Any idea what might cause this?
- RobElliottSilver Contributor
vheer sorry I don't understand the problem: the left field of the Condition must have the Has Column Changed: yourcolumn selected from the dynamic content box. In the right field you type true.
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
- robrobrobrobCopper ContributorWow ... how can i set the trigger condition to trigger when the column Completed has the value " closed " instead of true or false ?